Innodb事务隔离级别

阅读量

Innodb事务隔离级别

REPEATABLE READ

This is the default isolation level for InnoDB. Consistent reads within the same transaction read the snapshot established by the first read. This means that if you issue several plain (nonlocking) SELECT statements within the same transaction, these SELECT statements are consistent also with respect to each other.

这是 InnoDB 默认的事务隔离级别,同一事物通过第一次创建的快照来构造一致性读.这意味着如果你在同一会话执行多次查询(非锁定读),那么每次获取的结果都是彼此相等的

For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE), UPDATE, and DELETE statements, locking depends on whether the statement uses a unique index with a unique search condition, or a range-type search condition.

对于锁定读,(SELECT with FOR UPDATE or LOCK IN SHARE MODE), UPDATE, 和 DELETE语句,是否锁定取决于此次查询(UPDATE DELETE 也是一种查询)是否通过唯一条件或范围条件查询使用唯一索引

For a unique index with a unique search condition, InnoDB locks only the index record found, not the gap before it.

  • For a unique index with a unique search condition,InnoDB只锁索引记录,不锁 gap
    • For other search conditions, InnoDB locks the index range scanned, using gap locks or next-key locks to block insertions by other sessions into the gaps covered by the range

READ COMMITTED

Each consistent read, even within the same transaction, sets and reads its own fresh snapshot. For information about consistent reads

read view在 innodb 如何避免幻读中已经介绍过

For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE), UPDATE statements, and DELETE statements, InnoDB locks only index records, not the gaps before them, and thus permits the free insertion of new records next to locked records. Gap locking is only used for foreign-key constraint checking and duplicate-key checking.

locking reads(SELECT with FOR UPDATE or LOCK IN SHARE MODE),UPDATE 语句,DELETE语句,InnoDB 都只会锁index records(也就是 record lock),不会产生 gap lock,因此允许在 gap 中插入新的 records.Gap locking只在外键约束检查和重复值检查时产生

Because gap locking is disabled, phantom problems may occur, as other sessions can insert new rows into the gaps

因为gap locking被 disabled,幻读问题可能会产生,其他会话可以在 gap 中插入新的记录

If you use READ COMMITTED, you must use row-based binary logging.

如果使用READ COMMITTED隔离级别,必须设置binlog_format为row
举个例子,假设两张表 T1和 T2

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
CREATE TABLE t1 (
  id int(11) NOT NULL
) ENGINE=InnoDB

CREATE TABLE t2 (
  id int(11) NOT NULL
) ENGINE=InnoDB

select * from t1;
+----+
| id |
+----+
|  1 |
|  2 |
+----+ 

select * from t2;
+----+
| id |
+----+
|  2 |
+----+

假设事务隔离级别为read commited,假设发生如下顺序的事务
begin;//事务1
insert into t2 select max(id) from t1;//事务1，t2增加了一个2
begin;//事务2
insert into t1(id) values(10);//事务2，t1增加了一个10
commit;//事务2
commit;//事务1
此时
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
select * from t1;
+----+
| id |
+----+
|  1 |
|  2 |
| 10 |
+----+ 

select * from t2;
+----+
| id |
+----+
|  2 |
|  2 |
+----+

假如binlog模式是statement模式，因为事务2先提交，且binlog是串行的，那么在binlog看来，整体事务为
begin;//事务2
insert into t1(id) values(10);//事务2，t1增加了一个10
commit;//事务2
begin;//事务1
insert into t2 select from max(id) from t1;//事务1，t2增加了一个10
commit;//事务1

此时

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
select * from t1;
+----+
| id |
+----+
|  1 |
|  2 |
| 10 |
+----+ 

select * from t2;
+----+
| id |
+----+
|  2 |
| 10 |
+----+

这就造成了主从不一致,因此read commited事务隔离级别下要使用 RBR 模式

使用READ COMMITTED隔离级别产生的额外影响:

For UPDATE or DELETE statements, InnoDB holds locks only for rows that it updates or deletes. Record locks for nonmatching rows are released after MySQL has evaluated the WHERE condition. This greatly reduces the probability of deadlocks, but they can still happen.

  • 对于 UPDATE 和 DELETE 语句,InnoDB只对更新和删除涉及到的行持有锁,而对于在 where 条件过滤后不匹配的行源会释放锁(释放Record locks).这显著的减少了发生死锁的可能性,但并不是说不会再出现死锁.

For UPDATE statements, if a row is already locked, InnoDB performs a “semi-consistent” read, returning the latest committed version to MySQL so that MySQL can determine whether the row matches the WHERE condition of the UPDATE. If the row matches (must be updated), MySQL reads the row again and this time InnoDB either locks it or waits for a lock on it.

  • 对于 UPDATE 语句,如果一行记录已经被锁定, InnoDB会执行一个”semi-consistent”读MySQL+InnoDB semi-consitent read原理及实现分析
    ,获取最后一次提交的数据版本,MySQL以这一版本的数据过滤where 条件判断是否匹配UPDATE语句,如果匹配,那么这些行将被更新, MySQL再次读取行源并对这些匹配的行加锁或者等待别人释放锁以加锁

考虑下面的例子:
创建表

1
2
3
4
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;

In this case, table has no indexes, so searches and index scans use the hidden clustered index for record locking
在这个例子中,表 t 没有索引,所以查询和索引扫描使用隐藏的聚簇索引来进行加锁

SESSION_A

1
2
SET autocommit = 0;
UPDATE t SET b = 5 WHERE b = 3;

SESSION_B
1
2
SET autocommit = 0;
UPDATE t SET b = 4 WHERE b = 2;

As InnoDB executes each UPDATE, it first acquires an exclusive lock for each row, and then determines whether to modify it. If InnoDB does not modify the row, it releases the lock. Otherwise, InnoDB retains the lock until the end of the transaction. This affects transaction processing as follows.

InnoDB 执行每次UPDATE操作,都会首先对每一行获取排它锁,然后再决定是否修改.如果InnoDB不修改这一行,就会释放锁,否则InnoDB会持有锁直到事物结束

当时使用默认的REPEATABLE READ事务隔离级别,SESSION_A的更细获取 x-locks(排它锁)且不会释放锁:

1
2
3
4
5
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

SESSION_B的更新此时就会被阻塞(因为SESSION_A在每一行都加了排它锁),直到SESSION_A提交或回滚
1
x-lock(1,2); block and wait for first UPDATE to commit or roll back

如果事务隔离级别为READ COMMITTED,SESSION_A获取x-locks(排它锁)后会释放那些不被修改的行的锁:

1
2
3
4
5
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)

SESSION_B执行更新语句,此时InnoDB做了一个“semi-consistent” read,获取并返回了最后一次提交的数据版本给 MySQL,so MySQL 可以判断 where 条件过滤后的数据是否需要被更新,找到匹配的行后,MySQL再次读取行源并对这些匹配的行加锁
1
2
3
4
5
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

使用READ COMMITTED事务隔离级别与启用废弃的参数innodb_locks_unsafe_for_binlog是一样的,除了一下几点:

  • innodb_locks_unsafe_for_binlog是一个 GLOBAL 参数,会影响所有会话,然后事务隔离级别既可以针对全局也可以针对单独会话进行设置
  • innodb_locks_unsafe_for_binlog只可以在MySQL 启动时设置,然后事务隔离级别级别既可以在启动时设置也可以在运行时设置

所以设置事务隔离级别为READ COMMITTED相比修改参数innodb_locks_unsafe_for_binlog具有更好的灵活性

READ UNCOMMITTED

SELECT statements are performed in a nonlocking fashion, but a possible earlier version of a row might be used. Thus, using this isolation level, such reads are not consistent. This is also called a dirty read. Otherwise, this isolation level works like READ COMMITTED.

###SERIALIZABLE

This level is like REPEATABLE READ, but InnoDB implicitly converts all plain SELECT statements to SELECT … LOCK IN SHARE MODE if autocommit is disabled. If autocommit is enabled, the SELECT is its own transaction. It therefore is known to be read only and can be serialized if performed as a consistent (nonlocking) read and need not block for other transactions. (To force a plain SELECT to block if other transactions have modified the selected rows, disable autocommit.)

这个级别有点像REPEATABLE READ,但是当autocommit禁用时时InnoDB明确的转换所有 SELECT 语句为SELECT ... LOCK IN SHARE MODE.

Powered by Hexo and Hexo-theme-hiker

Copyright © 2013 - 2022 Fan() All Rights Reserved.

访客数 : | 访问量 :