Mysql 数据库事物及隔离级别

mysql 专栏收录该内容
9 篇文章 0 订阅

事务的基本要素(ACID)

  • 原子性(Atomicity):事务开始后所有操作,要么全部做完,要么全部不做,不可能停滞在中间环节。事务执行过程中出错,会回滚到事务开始前的状态,所有的操作就像没有发生一样。也就是说事务是一个不可分割的整体,就像化学中学过的原子,是物质构成的基本单位。
  • 一致性(Consistency):事务开始前和结束后,数据库的完整性约束没有被破坏 。比如A向B转账,不可能A扣了钱,B却没收到。
  • 隔离性(Isolation):同一时间,只允许一个事务请求同一数据,不同的事务之间彼此没有任何干扰。比如A正在从一张银行卡中取钱,在A取钱的过程结束前,B不能向这张卡转账。
  • 持久性(Durability):事务完成后,事务对数据库的所有更新将被保存到数据库,不能回滚。

事务的隔离级别问题

  • 脏读:事务A读取了事务B更新的数据,然后B回滚操作,那么A读取到的数据是脏数据
  • 不可重复读:事务 A 多次读取同一数据,事务 B 在事务A多次读取的过程中,对数据作了更新并提交,导致事务A多次读取同一数据时,结果 不一致。
  • 幻读:系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级,但是系统管理员B就在这个时候插入了一条具体分数的记录,当系统管理员A改结束后发现还有一条记录没有改过来,就好像发生了幻觉一样,这就叫幻读。

小结:不可重复读的和幻读很容易混淆,不可重复读侧重于修改,幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行,解决幻读需要锁表

事务的隔离级别

READ UNCOMMITTED 读未提交

在READ UNCOMMITTED级别,事务中的修改,即使没有提交,对其他事务也都是可见的。事务可以读取未提交的数据,这也被称作脏读。这个级别会导致很多问题,从性能上来说,READ UNCOMMITTED不会比其他的级别好太多,但确缺乏其他级别的很多好处,除非真的有必要的理由,在实际应用中一般很少使用。

READ COMMITED 读已提交

大多数数据库系统的默认隔离级别是 READ COMMITED(但mysql不是)。READ COMMITED满足前面提到的隔离性简单定义:一个事务开始时,只能看见已经提交的事务所做的修改。换句话说,一个事务从开始直到提交之前,所做的任何修改对其他事务都是不可见的。这个级别有时候也叫做不可重复读,因为两次执行同样的查询,可能会得到不一样的结果。

REPEATABLE READ 可重复读

MySQL的默认事务隔离级别。
REPEATABLE READ解决了脏读的问题。该级别保证了在同一个事务中多次读取同样的记录的结果是一致的。但是理论上,可重复读隔离级别还是无法解决另一个幻读的问题。所谓幻读,指的是当某个事务在读取某个范围内的记录时,另外一个事务又在该范围内插入了新的记录,当之前的事务再次读取该范围的记录时,会产生幻行。InnoDB存储引擎通过多版本并发控制(MVCC)解决了幻读的问题。

SERIALIZABLE 可串行化

SERIALIZABLE 是最高的隔离级别。它通过强事务串行执行,避免了前面说的幻读的问题。简单来说,SERIALIZABLE会在曲度的每一行数据都加上锁,所以可能导致大量的超时和锁争用的问题。实际应用中也很少用到这个隔离级别,只有在非常需要确保数据一致性而且可以接受没有并发的情况下,才考虑使用该级别。

数据准备

CREATE TABLE `s_test` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(64) DEFAULT NULL,
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `status` tinyint(4) NOT NULL DEFAULT '1' COMMENT '状态。0-删除,1-可用',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

insert into s_test(`name`) values ('a'),('b'),('c'),('d');

mysql> select * from s_test;
+----+------+---------------------+---------------------+--------+
| id | name | create_time         | update_time         | status |
+----+------+---------------------+---------------------+--------+
|  1 | a    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
|  2 | b    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
|  3 | c    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
|  4 | d    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
+----+------+---------------------+---------------------+--------+
4 rows in set (0.00 sec)

READ UNCOMMITTED

分别开启2个数据库连接终端,模拟2个事物操作。
终端A 设置事物隔离级别Read UnCommited 然后读取测试表中的数据

mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.01 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from s_test;
+----+------+---------------------+---------------------+--------+
| id | name | create_time         | update_time         | status |
+----+------+---------------------+---------------------+--------+
|  1 | a    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
|  2 | b    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
|  3 | c    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
|  4 | d    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
+----+------+---------------------+---------------------+--------+
4 rows in set (0.02 sec)

终端B设置事物隔离级别Read UnCommited 然后修改测试表中的数据,并不提交事物

mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> update s_test set name = 'x' where id = 1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

此时再去终端A中读取数据库中的数据

mysql> select * from s_test;
+----+------+---------------------+---------------------+--------+
| id | name | create_time         | update_time         | status |
+----+------+---------------------+---------------------+--------+
|  1 | x    | 2019-05-07 18:42:37 | 2019-05-08 10:46:50 |      1 |
|  2 | b    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
|  3 | c    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
|  4 | d    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
+----+------+---------------------+---------------------+--------+
4 rows in set (0.00 sec)

可以发现事物A已经可以读到事物B未提交的修改,在READ UNCOMMITTED级别,事务中的修改,即使没有提交,对其他事务也都是可见的。

此时,我们将终端B的事物回滚

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

再到终端A查询数据库

mysql> select * from s_test;
+----+------+---------------------+---------------------+--------+
| id | name | create_time         | update_time         | status |
+----+------+---------------------+---------------------+--------+
|  1 | a    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
|  2 | b    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
|  3 | c    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
|  4 | d    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
+----+------+---------------------+---------------------+--------+
4 rows in set (0.00 sec)

发现数据已经回滚,则事物A中间那次读到的数据就是脏读。

READ COMMITED

同样,我们终端A 设置事物隔离级别Read Commited 然后读取测试表中的数据

mysql> set session transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from s_test;
+----+------+---------------------+---------------------+--------+
| id | name | create_time         | update_time         | status |
+----+------+---------------------+---------------------+--------+
|  1 | a    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
|  2 | b    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
|  3 | c    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
|  4 | d    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
+----+------+---------------------+---------------------+--------+
4 rows in set (0.00 sec)

然后在终端B设置事物隔离级别Read Commited 然后修改测试表中的数据,并不提交事物

mysql> set session transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update s_test set name = 'x' where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

此时再次到终端A查询数据库记录

mysql> select * from s_test;
+----+------+---------------------+---------------------+--------+
| id | name | create_time         | update_time         | status |
+----+------+---------------------+---------------------+--------+
|  1 | a    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
|  2 | b    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
|  3 | c    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
|  4 | d    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
+----+------+---------------------+---------------------+--------+
4 rows in set (0.00 sec)

此时我们会发现,我们终端B未提交的修改,我们并没有读到,即不会产生脏读。

此时,我们到终端B将刚做的修改提交

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

再次回到终端A查询数据库数据

mysql> select * from s_test;
+----+------+---------------------+---------------------+--------+
| id | name | create_time         | update_time         | status |
+----+------+---------------------+---------------------+--------+
|  1 | x    | 2019-05-07 18:42:37 | 2019-05-08 10:59:54 |      1 |
|  2 | b    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
|  3 | c    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
|  4 | d    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
+----+------+---------------------+---------------------+--------+
4 rows in set (0.00 sec)

此时我们发现,我们已经读到了终端B事物提交的事物,但是这也产生了一个新的问题,即事物A的2次提交读到的不同的数据。即产生了重复读的问题。

REPEATABLE READ

同样,我们终端A 设置事物隔离级别Repeatable Read然后读取测试表中的数据

mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from s_test;
+----+------+---------------------+---------------------+--------+
| id | name | create_time         | update_time         | status |
+----+------+---------------------+---------------------+--------+
|  1 | x    | 2019-05-07 18:42:37 | 2019-05-08 10:59:54 |      1 |
|  2 | b    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
|  3 | c    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
|  4 | d    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
+----+------+---------------------+---------------------+--------+
4 rows in set (0.00 sec)

然后 在终端B 设置事物隔离级别Repeatable Read然后然后修改测试表中的数据,并提交事物

mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update s_test set name = 'y' where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from s_test;
+----+------+---------------------+---------------------+--------+
| id | name | create_time         | update_time         | status |
+----+------+---------------------+---------------------+--------+
|  1 | y    | 2019-05-07 18:42:37 | 2019-05-08 11:08:51 |      1 |
|  2 | b    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
|  3 | c    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
|  4 | d    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
+----+------+---------------------+---------------------+--------+
4 rows in set (0.00 sec)

此时,再到终端A查看数据

mysql> select * from s_test;
+----+------+---------------------+---------------------+--------+
| id | name | create_time         | update_time         | status |
+----+------+---------------------+---------------------+--------+
|  1 | x    | 2019-05-07 18:42:37 | 2019-05-08 10:59:54 |      1 |
|  2 | b    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
|  3 | c    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
|  4 | d    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
+----+------+---------------------+---------------------+--------+
4 rows in set (0.00 sec)

发现事物B一提交的修改不回影响事物A的重复读取,即不回产生重复读的问题。

此时,再到终端B中插入一条记录

mysql> insert into s_test(`name`) values ('e');
Query OK, 1 row affected (0.02 sec)

mysql> select * from s_test;
+----+------+---------------------+---------------------+--------+
| id | name | create_time         | update_time         | status |
+----+------+---------------------+---------------------+--------+
|  1 | y    | 2019-05-07 18:42:37 | 2019-05-08 11:08:51 |      1 |
|  2 | b    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
|  3 | c    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
|  4 | d    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
|  5 | e    | 2019-05-08 11:13:54 | 2019-05-08 11:13:54 |      1 |
+----+------+---------------------+---------------------+--------+
5 rows in set (0.00 sec)

然后再回到终端A,插入一条看起来不存在的记录

mysql> select * from s_test;
+----+------+---------------------+---------------------+--------+
| id | name | create_time         | update_time         | status |
+----+------+---------------------+---------------------+--------+
|  1 | x    | 2019-05-07 18:42:37 | 2019-05-08 10:59:54 |      1 |
|  2 | b    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
|  3 | c    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
|  4 | d    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
+----+------+---------------------+---------------------+--------+
4 rows in set (0.00 sec)

mysql> insert into s_test(id,name) values(5,'e');
ERROR 1062 (23000): Duplicate entry '5' for key 'PRIMARY'

即产生了幻读,读不出来已经存在的数据。

SERIALIZABLE

同样,我们在终端A中 设置事物隔离级别Serializable然后读取测试表中的数据

mysql> set session transaction isolation level serializable;
Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from s_test;
+----+------+---------------------+---------------------+--------+
| id | name | create_time         | update_time         | status |
+----+------+---------------------+---------------------+--------+
|  1 | y    | 2019-05-07 18:42:37 | 2019-05-08 11:08:51 |      1 |
|  2 | b    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
|  3 | c    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
|  4 | d    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
|  5 | e    | 2019-05-08 11:13:54 | 2019-05-08 11:13:54 |      1 |
+----+------+---------------------+---------------------+--------+
5 rows in set (0.00 sec)

然后在终端B中设置事物隔离级别Serializable然后尝试修改测试表中的数据

mysql> set session transaction isolation level serializable;
Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into s_test(`name`) values ('f');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

然后会发现,终端B事务被挂住了,然后过了一段时间,提示了错误 (1205, u’Lock wait timeout exceeded; try restarting transaction’) ,说等待锁超时。

在串行化隔离级别中,事物会在读取的每一行数据都加上锁,也就是说,上面A事务在读取时,已经加了锁,此时B事务在插入操作时,得等待锁的放开,时间一长,A锁未放开,B就报错了。

  • 0
    点赞
  • 0
    评论
  • 0
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

相关推荐
©️2020 CSDN 皮肤主题: Age of Ai 设计师:meimeiellie 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值