事务隔离级别
在InnoDB
事务模型中,目标是将多版本数据库的最佳属性与传统的两阶段锁定相结合。InnoDB
在默认情况下,采用Oracle风格,在行级别执行锁定并以非锁定一致读取的形式运行查询。锁信息以InnoDB
节省空间的方式存储,因此不需要锁升级。通常,允许多个用户锁定InnoDB
表中的每一行或该行的任何随机子集,而不会导致InnoDB
内存耗尽。
事务隔离是数据库处理的基础之一。隔离是缩写ACID中的I ;隔离级别是一种设置,用于在多个事务同时进行更改和执行查询时微调性能与结果的可靠性,一致性和可重复性之间的平衡。
InnoDB
报价由SQL描述的所有四个事务隔离级别:1992标准:READ UNCOMMITTED
,READ COMMITTED
,REPEATABLE READ
,和SERIALIZABLE
。默认隔离级别InnoDB
是REPEATABLE READ
。
用户可以使用该SET TRANSACTION
语句更改单个会话或所有后续连接的隔离级别。要为所有连接设置服务器的默认隔离级别,请--transaction-isolation
在命令行或选项文件中使用该选项。有关隔离级别和级别设置语法的详细信息,请参见“ SET TRANSACTION语句”。
InnoDB
支持使用不同的锁定策略在此描述的每个事务隔离级别。您可以在默认数据REPEATABLE READ
级别上实现高度一致性,以便对ACID合规性很重要的关键数据进行操作。或者,在批量报告之类的情况下,精确的一致性和可重复的结果比最小化锁定开销不那么重要时,也可以放宽READ COMMITTED
甚至使用一致性规则READ UNCOMMITTED
。SERIALIZABLE
实施比甚至更严格的规则REPEATABLE READ
,并且主要用于特殊情况下(例如XA)事务以及用于解决并发和死锁问题。
下表描述了MySQL如何支持不同的事务级别。列表从最常用的级别到最不常用的级别。
REPEATABLE READ
这是的默认隔离级别
InnoDB
。同一事务中的一致读取读取由第一次读取建立的快照。这意味着,如果您SELECT
在同一事务中发出多个普通(非锁定)语句,则这些SELECT
语句彼此之间也是一致的。请参见“一致的非锁定读取”。对于锁定读取(
SELECT
使用FOR UPDATE
或FOR SHARE
),UPDATE
和DELETE
语句,锁定取决于该语句是使用具有唯一搜索条件的唯一索引还是范围类型搜索条件。- 对于具有唯一搜索条件的唯一索引,
InnoDB
仅锁定找到的索引记录,而不锁定其前的空白。 - 对于其他搜索条件,
InnoDB
使用间隙锁定或下一键锁定来锁定扫描的索引范围,以阻止其他会话插入该范围所覆盖的间隙。有关间隙锁定和下一键锁定的信息,请参见“ InnoDB锁定”。
- 对于具有唯一搜索条件的唯一索引,
READ COMMITTED
即使在同一事务中,每个一致的读取也会设置并读取自己的新快照。有关一致读取的信息,请参见“一致非锁定读取”。
对于锁定读取(
SELECT
使用FOR UPDATE
或FOR SHARE
),UPDATE
语句和DELETE
语句,InnoDB
仅锁定索引记录,而不锁定它们之间的间隙,因此允许在锁定记录旁边自由插入新记录。间隙锁定仅用于外键约束检查和重复键检查。由于禁用了间隙锁定,因此可能会产生幻影问题,因为其他会话可以在间隙中插入新行。有关幻像的信息,请参见“幻影行(Phantom Rows)”。
READ COMMITTED
隔离级别仅支持基于行的二进制日志记录。如果READ COMMITTED
与配合使用binlog_format=MIXED
,服务器将自动使用基于行的日志记录。使用
READ COMMITTED
具有其他效果:- 对于
UPDATE
或DELETE
语句,InnoDB
仅对其更新或删除的行持有锁。MySQL评估WHERE
条件后,将释放不匹配行的记录锁。这大大降低了死锁的可能性,但是仍然可以发生。 - 对于
UPDATE
语句,如果某行已被锁定,则InnoDB
执行“半一致”读取,将最新的提交版本返回给MySQL,以便MySQL可以确定该行是否符合WHERE
条件UPDATE
。如果该行匹配(必须更新),则MySQL会再次读取该行,这一次将InnoDB
其锁定或等待对其进行锁定。
请考虑从该表开始的以下示例:
CREATE TABLE t (a INT NOT NULL, b INT)ENGINE = InnoDB;INSERT INTO tVALUES (1,2),(2,3),(3,2),(4,3),(5,2);COMMIT ;在这种情况下,表没有索引,因此搜索和索引扫描将隐藏的聚集索引用于记录锁定(请参见“聚集索引和二级索引”),而不是使用索引列。
假设一个会话
UPDATE
使用以下语句执行:# Session A
START TRANSACTION ;UPDATE tSET b = 5WHERE b = 3;还假设第二个会话
UPDATE
通过在第一个会话的语句之后执行以下语句来执行:# Session B
UPDATE tSET b = 4WHERE b = 2;在
InnoDB
执行each时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
第二个
UPDATE
块在尝试获取任何锁时立即阻塞(因为第一个更新在所有行上都保留了锁),并且直到第一个UPDATE
提交或回滚时才继续进行:x-lock(1,2); block and wait for first UPDATE to commit or roll back
READ COMMITTED
相反,如果使用if ,则第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)
对于第二个
UPDATE
,InnoDB
执行“半一致”读取,将它读取的每一行的最新提交版本返回给MySQL,以便MySQL可以确定该行是否符合以下WHERE
条件UPDATE
:x-lock(1,2); update(1,2) to (1,4); retain x-lock x-lock(2,3); unlock(2,3) x-lock(3,2); update(3,2) to (3,4); retain x-lock x-lock(4,3); unlock(4,3) x-lock(5,2); update(5,2) to (5,4); retain x-lock
但是,如果
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 tVALUES (1,2,3),(2,2,4);COMMIT ; # Session ASTART TRANSACTION ;UPDATE tSET b = 3WHERE b = 2 AND c = 3; # Session BUPDATE tSET b = 4WHERE b = 2 AND c = 4;该
READ COMMITTED
隔离级别可以在启动时设置或在运行时更改。在运行时,可以为所有会话全局设置,也可以为每个会话单独设置。- 对于
READ UNCOMMITTED
SELECT
语句以非锁定方式执行,但是可能会使用行的早期版本。因此,使用此隔离级别,此类读取不一致。这也称为脏读。否则,此隔离级别的工作方式类似于READ COMMITTED
。SERIALIZABLE
此级别类似于
REPEATABLE READ
,但是InnoDB
将所有普通SELECT
语句隐式转换为SELECT ... FOR SHARE
ifautocommit
禁用。如果autocommit
启用,则SELECT
是其自身的事务。因此,它被认为是只读的,并且如果以一致的(非锁定)读取方式执行并且不需要阻塞其他事务就可以序列化。(SELECT
如果其他事务已修改所选行,则要强制平原阻止,请禁用autocommit
。)