经验首页 前端设计 程序设计 Java相关 移动开发 数据库/运维 软件/图像 大数据/云计算 其他经验
当前位置:技术经验 » 数据库/运维 » MySQL » 查看文章
事务隔离级别
来源:cnblogs  作者:废物大师兄  时间:2021/2/1 11:51:14  对本文有异议

隔离级别是在多个事务同时进行更改和执行查询时,对性能与结果的可靠性、一致性和再现性之间的平衡进行微调的设置。

提供了SQL:1992标准中描述的四种事务隔离级别:READ UNCOMMITTEDREAD COMMITTEDREPEATABLE READSERIALIZABLE。InnoDB默认的隔离级别是REPEATABLE READ

InnoDB使用不同的锁定策略支持这里描述的每个事务隔离级别。下面的列表描述了MySQL如何支持不同的事务级别。

1.  REPEATABLE READ(可重复读)

这是InnoDB的默认隔离级别。同一个事务中的一致性读取读的是第一次读取时建立的快照。这意味着,如果在同一个事务中发出几个普通(非锁定)SELECT语句,这些SELECT语句彼此之间也是一致的。(PS:可重复读这个级别就是要保证同一个事务中,多次读取相同的数据,返回的结果是一样的。)

对于锁定读(SELECT with FOR UPDATE or LOCK IN SHARE MODE)、UPDATE 和 DELETE 语句,锁定取决于语句是使用具有唯一搜索条件的唯一索引,还是使用范围类型的搜索条件。

  • 对于具有唯一搜索条件的唯一索引,InnoDB只锁定找到的索引记录,而不锁定之间的间隙。
  • 对于其他搜索条件,InnoDB锁定扫描到的索引范围,使用间隙锁或next-key锁以阻止其他会话向该范围覆盖的间隙中插入。 

2.  READ COMMITTED(读已提交)

即使在同一事务中,每个一致的读取都将设置并读取自己的新快照。(PS:对比一下,对于一致性读,可重复读是读取事务中第一次读取时候建立的快照;而读已提交,每次都是读最新的快照)

对于锁读(SELECT with For UPDATE or LOCK IN SHARE MODE)、UPDATE语句和DELETE语句,InnoDB只锁住索引记录,而不锁住它们之间的间隙,从而允许在锁住的记录旁边自由插入新记录。间隙锁定仅用于外键约束检查和重复键检查。 

由于禁用了间隙锁,可能会出现幻像问题,因为其他会话可以在间隙中插入新行。

使用READ COMMITTED具有额外的效果:

  • 对于UPDATE或DELETE语句,InnoDB仅对其更新或删除的行持有锁。MySQL评估WHERE条件后,将释放不匹配行的记录锁。这大大降低了死锁的可能性,但是仍然可能发生。
  • 对于UPDATE语句,如果某行已被锁定,则InnoDB执行“半一致”读取,将最新的提交版本返回给MySQL,以便MySQL可以确定该行是否与UPDATE的WHERE条件匹配。如果该行匹配(必须更新),则MySQL会再次读取该行,这一次InnoDB将其锁定或等待对其进行锁定。 

举个例子,考虑下面的语句:

CREATE TABLE t (a INT NOT NULL, b INT) ENGINE = InnoDB;
INSERT INTO t VALUES (1,2),(2,3),(3,2),(4,3),(5,2);
COMMIT;

在这种情况下,表没有索引,因此搜索和索引扫描使用隐藏的聚集索引锁定记录

假设,第一个会话要执行update语句:

# Session A
START TRANSACTION;
UPDATE t SET b = 5 WHERE b = 3;

假设,在第一个会话之后,第二个会话也要执行如下update语句:

# Session B
UPDATE t SET b = 4 WHERE b = 2;

当InnoDB执行每个UPDATE时,它首先为读取的每一行获取一个排他锁,然后决定是否修改它。如果InnoDB没有修改该行,则释放锁。否则,InnoDB会保留锁直到事务结束。这对事务处理的影响如下。

如果用默认的REPEATABLE READ隔离级别,第一个UPDATE在其读取的每一行上获取一个X锁,并且不释放其中的任何一个:

x-lock(1,2); retain x-lock
x-lock(2,3); update(2,3) to (2,5); retain x-lock
x-lock(3,2); retain x-lock
x-lock(4,3); update(4,3) to (4,5); retain x-lock
x-lock(5,2); retain x-lock

(PS:之所以每一行都加排它锁,是因为b上没有唯一索引)

第二个UPDATE在尝试获取任何锁时立即阻塞(因为第一个UPDATE已在所有行上保留了锁),并且直到第一个UPDATE提交或回滚后才继续进行:

x-lock(1,2); block and wait for first UPDATE to commit or roll back

如果用READ COMMITTED隔离级别,第一个UPDATE在其读取的每一行上获取一个X锁,并为未修改的行释放X锁:

x-lock(1,2); unlock(1,2)
x-lock(2,3); update(2,3) to (2,5); retain x-lock
x-lock(3,2); unlock(3,2)
x-lock(4,3); update(4,3) to (4,5); retain x-lock
x-lock(5,2); unlock(5,2)

但是,如果WHERE条件包含一个索引列,并且InnoDB使用该索引,那么在获取和保留记录锁时,只考虑索引列。

再看下面一个例子,第一个UPDATE在b = 2的每一行上获取并保留一个X锁。第二个UPDATE在尝试获取同一记录上的X锁时会阻塞,因为它也使用在b列上定义的索引 。

CREATE TABLE t (a INT NOT NULL, b INT, c INT, INDEX (b)) ENGINE = InnoDB;
INSERT INTO t VALUES (1,2,3),(2,2,4);
COMMIT;

# Session A
START TRANSACTION;
UPDATE t SET b = 3 WHERE b = 2 AND c = 3;

# Session B
UPDATE t SET b = 4 WHERE b = 2 AND c = 4;

(PS:b列上有索引,这两条语句在检索时使用了b列上的索引,因此第一会话在b=2的列上加了X锁,第一个会话更新b=2时会阻塞)

3.  READ UNCOMMITTED(读未提交)

会出现脏读,一般不使用

4.  SERIALIZABLE(串行化)

此级别类似于REPEATABLE READ,但如果禁用了自动提交,则InnoDB会将所有普通的SELECT语句隐式转换为SELECT ... LOCK IN SHARE MODE。 

 

原文链接:http://www.cnblogs.com/cjsblog/p/14336188.html

 友情链接: NPS