服务器之家：专注于服务器技术及软件下载分享
分类导航

Mysql|Sql Server|Oracle|Redis|MongoDB|PostgreSQL|Sqlite|DB2|mariadb|Access|数据库技术|

服务器之家 - 数据库 - Mysql - MySQL事务及并发下所引发的问题详解

MySQL事务及并发下所引发的问题详解

2023-06-26 01:00未知服务器之家 Mysql

环境：MySQL8.0.30 1 事务基本概念 1.1 基本概念 什么是事务： 是可以提交或回滚的原子工作单元，它是由一个或多个操作形成的一组操作单元。 事务处理的原则： 保证所有的操作都作为 一个工作单元来执行，即使出现了异常，都不

环境：MySQL8.0.30

1 事务基本概念

1.1 基本概念

什么是事务：是可以提交或回滚的原子工作单元，它是由一个或多个操作形成的一组操作单元。

事务处理的原则：保证所有的操作都作为 一个工作单元来执行，即使出现了异常，都不能改变这种执行方式。当在一个事务中执行多个操作时，要么所有的事务都被提交( commit )，这些修改永久地保存下来；要么放弃所有的修改 ，整个事务回滚( rollback )到最初状态。

1.2 事务的四个特性ACID

1.2.1 原子性

Atomicity

是指事务是一个不可分割的工作单位( 最小的工作单位 )，要么全部提交，要么全部回滚。

1.2.2 一致性

Consistency

一致性是指事务执行前后，数据从一个 合法性状态 变换到另外一个 合法性状态；而这种状态应该是与具体的业务相关。

该特性是由其它3个特性 + 开发者共同来保证的。

如：张三给李四转账100，张三的账户必须扣减100元，李四的账户必须加100元。

1.2.3 隔离性

Isolation

事务的隔离性是指一个事务的执行 不能被其他事务干扰 ，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

如不考虑事务的隔离性，将会出现如下情况：

MySQL事务及并发下所引发的问题详解

1.2.4 持久性

Durability

指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，即使系统服务器奔溃或者服务器宕机，只要数据库能够重新启动，那么一定会将其恢复为事务提交成功结束后的状态。

1.3 MySQL事务支持

MySQL事务及并发下所引发的问题详解

只有InnoDB引擎是支持事务的。

2 如何使用事务

两种方式：显式事务 和 隐式事务

2.1 显示事务

显示事务可以通过2中方式：start transaction 或 beign。

mysql> START TRANSACTION;
 Query OK, 0 rows affected (0.00 sec)
 或者
 mysql> BEGIN;
 Query OK, 0 rows affected (0.00 sec)
 
 #这里是一组DML语句
 
 #提交事务
 mysql> COMMIT;
 Query OK, 0 rows affected (0.00 sec)
 
 #回滚事务
 mysql> ROLLBACK;
 Query OK, 0 rows affected (0.00 sec)

两者的区别在于，start transaction [修饰符]

  1. READ ONLY：标识当前事务是一个只读事务 ，也就是属于该事务的数据库操作只能读取数据，而不能修改数据。
  2. READ WRITE：标识当前事务是一个读写事务 ，也就是属于该事务的数据库操作既可以读取数据，也可以修改数据。
  3. WITH CONSISTENT SNAPSHOT ：启动一致性快照读。（唯一允许一致性读的隔离级别是REPEATABLE READ，对于所有其他隔离级别，将忽略WITH CONSISTENT SNAPSHOT子句。当忽略WITH CONSISTENT SNAPSHOT子句时，将生成一个警告。）

什么是一致性快照读？如下示例：

创建如下表

mysql> create table test (id int primary key, name varchar(32));
 Query OK, 0 rows affected (0.05 sec)
 
 mysql> select * from test;
 Empty set (0.00 sec)

试验1：

MySQL事务及并发下所引发的问题详解

试验2：

MySQL事务及并发下所引发的问题详解

结论：

START TRANSACTION是在第一条select执行完后，才得到事务的一致性快照，而START TRANSACTION WITH CONSISTENT SNAPSHOT则是立马取得事务的一致性快照。

2.2 隐式事务

通过设置autocommit系统变量来控制事务，默认该值为：ON。

mysql> SHOW VARIABLES LIKE '%autocommit%';
 +---------------+-------+
 | Variable_name | Value |
 +---------------+-------+
 | autocommit    | ON    |
 +---------------+-------+
 1 row in set (0.01 sec)

默认事务自动提交。、

通过如下方式进行关闭

mysql> SET AUTOCOMMIT=OFF;
 Query OK, 0 rows affected (0.00 sec)
 
 mysql> SHOW VARIABLES LIKE '%autocommit%';
 +---------------+-------+
 | Variable_name | Value |
 +---------------+-------+
 | autocommit    | OFF   |
 +---------------+-------+
 1 row in set (0.01 sec)
 #或者
 mysql> SET AUTOCOMMIT=0;

2.3 使用事务

创建数据库及表

mysql> create database pack;
 Query OK, 1 row affected (0.01 sec)
 
 mysql> use pack;
 Database changed
 mysql> create table test (id int primary key, name varchar(32));
 Query OK, 0 rows affected (0.09 sec)

示例1：

mysql> begin;
 Query OK, 0 rows affected (0.00 sec)
 
 mysql> insert into test values (1, 'zs');
 Query OK, 1 row affected (0.01 sec)
 
 mysql> insert into test values (2, 'ls');
 Query OK, 1 row affected (0.00 sec)
 
 mysql> commit;
 Query OK, 0 rows affected (0.01 sec)
 
 mysql> select * from test;
 +----+------+
 | id | name |
 +----+------+
 |  1 | zs   |
 |  2 | ls   |
 +----+------+
 2 rows in set (0.00 sec)

示例2：

mysql> begin;
 Query OK, 0 rows affected (0.00 sec)
 
 mysql> insert into test values (3, 'ww');
 Query OK, 1 row affected (0.00 sec)
 
 mysql> insert into test values (4, 'zl');
 Query OK, 1 row affected (0.00 sec)
 
 mysql> rollback;
 Query OK, 0 rows affected (0.00 sec)
 
 mysql> select * from test;
 +----+------+
 | id | name |
 +----+------+
 |  1 | zs   |
 |  2 | ls   |
 +----+------+
 2 rows in set (0.00 sec)

2.4 事务保存点

MySQL支持SAVEPOINT、ROLLBACK TO SAVEPOINT、RELEASE SAVEPOINT。

通过设置保存点，事务回滚是回滚到指定的保存点，而不是回滚整个事务。

示例：

mysql> select * from test;
 +----+------+
 | id | name |
 +----+------+
 |  1 | zs   |
 |  2 | ls   |
 +----+------+
 2 rows in set (0.00 sec)
 
 mysql> begin;
 Query OK, 0 rows affected (0.00 sec)
 
 mysql> insert into test values (3, 'zl');
 Query OK, 1 row affected (0.00 sec)
 
 mysql> savepoint p1;
 Query OK, 0 rows affected (0.00 sec)
 
 mysql> insert into test values (4, 'ww');
 Query OK, 1 row affected (0.00 sec)
 
 mysql> rollback to p1;
 Query OK, 0 rows affected (0.00 sec)
 
 mysql> commit;
 Query OK, 0 rows affected (0.02 sec)
 
 mysql> select * from test;
 +----+------+
 | id | name |
 +----+------+
 |  1 | zs   |
 |  2 | ls   |
 |  3 | zl   |
 +----+------+
 3 rows in set (0.00 sec)

3 事务隔离级别

3.1 环境准备

创建表：

mysql> create table account (
     -> id int primary key,
     -> name varchar(32),
     -> balance int
     -> );
 Query OK, 0 rows affected (0.06 sec)

插入数据

mysql> insert into account values (1, 'zs', 1000);
 
 mysql> select * from account;
 +----+------+---------+
 | id | name | balance |
 +----+------+---------+
 |  1 | zs       |    1000  |
 +----+------+---------+
 1 row in set (0.00 sec)

3.2 并发问题

当多个事务并发执行修改相同数据时会出现如下问题：

3.2.1 脏写

一个事务修改了另外一个事务修改了但未提交的数据。

脏写非常的严重，以致所有的隔离级别都解决了脏写问题。

3.2.2 脏读

事务A读取了事务B修改了但是未提交的数据，如果此时事务B回滚了，那么事务A读取到的数据肯定是无效的。

3.2.3 不可重复读

事务A读取id为1的name为张三，紧接着事务B修改了id为1的name为李四，此时事务A再次读取id为1的数据发现此时name为李四，事务A两次读取不一样，这就是发生了不可重复读。

3.2.4 幻读

事务A读取age为20的人人员信息返回了10条，紧接着事务B插入了5条age为20的数据，此时事务A再次读取age为20的人员信息返回15条，这就是发生了幻读。

3.3 隔离级别

MySQL支持4中隔离级别，Oracle支持两种(READ COMMITTED(默认), SERIALIZABLE)

3.3.1 读未提交

READ UNCOMMITTED：读未提交，在该隔离级别，所有事务都可以看到其他未提交事务的执行结果；不能避免脏读、不可重复读、幻读。

3.3.2 读已提交

READ COMMITTED：一个事务读取到了，其它已提交的事务所修改的数据；可以避免脏读，但不可重复读、幻读问题仍然存在。

3.3.3 可重复读

REPEATABLE READ：事务A读取一条数据后，事务B修改了该数据并且提交后，事务A再次读取该条数据，读取到的内容没有发生变化；可以避免脏读、不可重复读，但幻读问题仍

然存在。MySQL默认隔离级别

3.3.4 串行化

SERIALIZABLE：一个一个的按顺序执行；能避免脏读、不可重复读和幻读。

总结，在不同隔离级别下，并发事务所引发的问题如下

不同隔离级别下所带来的性能问题

3.4 隔离级别演示

MySQL中设置隔离级别方法如下：

SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL 隔离级别;
 #其中，隔离级别格式：
 1. READ UNCOMMITTED
 2. READ COMMITTED
 3. REPEATABLE READ
 4. SERIALIZABLE

或者

SET [GLOBAL|SESSION] TRANSACTION_ISOLATION = '隔离级别'
 #其中，隔离级别格式：
 1. READ-UNCOMMITTED
 2. READ-COMMITTED
 3. REPEATABLE-READ
 4. SERIALIZABLE

示例：

mysql> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
 Query OK, 0 rows affected (0.00 sec)
 #或者
 mysql> SET SESSION TRANSACTION_ISOLATION ='REPEATABLE-READ';
 Query OK, 0 rows affected (0.00 sec)
 #查看当前会话级别的隔离级别
 mysql> SELECT @@TRANSACTION_ISOLATION;
 +-------------------------+
 | @@TRANSACTION_ISOLATION |
 +-------------------------+
 | REPEATABLE-READ         |
 +-------------------------+
 1 row in set (0.00 sec)

3.4.1 读未提交

脏读问题

mysql> set session transaction_isolatinotallow='READ-UNCOMMITTED';
 Query OK, 0 rows affected (0.00 sec)
 
 mysql> select @@transaction_isolation;
 +-------------------------+
 | @@transaction_isolation |
 +-------------------------+
 | READ-UNCOMMITTED        |
 +-------------------------+
 1 row in set (0.00 sec)

示例：

3.4.2 读已提交

不可重复读

mysql> set session transaction_isolatinotallow='read-committed';
 Query OK, 0 rows affected (0.00 sec)
 
 mysql> select @@transaction_isolation;
 +-------------------------+
 | @@transaction_isolation |
 +-------------------------+
 | READ-COMMITTED          |
 +-------------------------+
 1 row in set (0.00 sec)

示例：

MySQL事务及并发下所引发的问题详解


3.4.3 可重复读

mysql> set session transaction_isolatinotallow='REPEATABLE-READ';
 Query OK, 0 rows affected (0.00 sec)
 
 mysql> select @@transaction_isolation;
 +-------------------------+
 | @@transaction_isolation |
 +-------------------------+
 | REPEATABLE-READ         |
 +-------------------------+
 1 row in set (0.00 sec)

示例：

MySQL事务及并发下所引发的问题详解


严格意义上看，可重复读隔离级别并没有解决幻读问题

示例：

MySQL事务及并发下所引发的问题详解


3.4.4 串行化

排队执行，略

完毕！！！

延伸 · 阅读

精彩推荐
  • Mysql解决MySQl查询不区分大小写的方法讲解

    解决MySQl查询不区分大小写的方法讲解

    今天小编就为大家分享一篇关于解决MySQl查询不区分大小写的方法讲解，小编觉得内容挺不错的，现在分享给大家，具有很好的参考价值，需要的朋友一起...

    Veir_dev5592019-06-25
  • MysqlMySQL数据库varchar的限制规则说明

    MySQL数据库varchar的限制规则说明

    本文我们主要介绍了MySQL数据库中varchar的限制规则，并以一个实际的例子对限制规则进行了说明，希望能够对您有所帮助。 ...

    mysql技术网4192019-11-23
  • Mysql浅谈mysql 树形结构表设计与优化

    浅谈mysql 树形结构表设计与优化

    在诸多的管理类，办公类等系统中，树形结构展示随处可见，本文主要介绍了mysql 树形结构表设计与优化，具有一定的参考价值，感兴趣的小伙伴们可以参...

    小码农叔叔5242021-11-16
  • MysqlMySQL 数据备份与还原的示例代码

    MySQL 数据备份与还原的示例代码

    这篇文章主要介绍了MySQL 数据备份与还原的相关知识,本文通过示例代码给大家介绍的非常详细，具有一定的参考借鉴价值，需要的朋友可以参考下...

    逆心2972019-06-23
  • Mysqlmysql 不能插入中文问题

    mysql 不能插入中文问题

    当向mysql5.5插入中文时，会出现类似错误 ERROR 1366 (HY000): Incorrect string value: '\xD6\xD0\xCE\xC4' for column ...

    MYSQL教程网5722019-11-25
  • MysqlMySQL锁的知识点总结

    MySQL锁的知识点总结

    在本篇文章里小编给大家整理了关于MySQL锁的知识点总结以及实例内容，需要的朋友们学习下。...

    别人放弃我坚持吖4362020-12-14
  • MysqlERROR: Error in Log_event::read_log_event()

    ERROR: Error in Log_event::read_log_event()

    ERROR: Error in Log_event::read_log_event(): read error, data_len: 438, event_type: 2 ...

    MYSQL教程网6412020-03-13
  • Mysql详解MySQL中的分组查询与连接查询语句

    详解MySQL中的分组查询与连接查询语句

    这篇文章主要介绍了MySQL中的分组查询与连接查询语句,同时还介绍了一些统计函数的用法,需要的朋友可以参考下 ...

    GALAXY_ZMY5442020-06-03
最近更新
编辑推荐
阅读排行
热门标签
150 　 1044 　 1062 　 1449 　 1053 　 1045 　 1290 　 1819 　 2013 　 1406 　 1005 　 1862 　 1864 　 1146 　 2003 　 1175 　 1215 　 1615 　 1054 　 2006 　 1130 　 1366 　 10048 　 28000 　 1786 　 1135 　 1577 　 1665 　 1067 　 1093 　
© 2019-2024 服务器之家(www.zzvips.com) 版权所有关于我们联系我们版权申明网站地图