TL;DR
MySQL InnoDB可以设定ANSI SQL-92中规定的四个事务隔离级别,事务并发度和事务隔离级别成反比,事务隔离级别越高,并发度越低。
是否有锁操作,取决于当前的读取是快照读,还是当前读,快照读读取的是可见的历史版本,无需上锁,简单的读操作是快照读(SELECT无附加语句),其他都是当前读,需要加锁。
对无索引的数据,InnoDB会锁定全表的记录,但是会在扫描过程中释放不符合筛选规则的记录的锁定。
对于有索引的数据,需要具体分析,在RR这一级别,InnoDB通过GAP锁机制避免了幻读问题。
ANSI SQL-92标准
事务隔离级别在ANSI SQL-92标准中有了明确的定义,即我们熟知的READ UNCOMMITTED
/READ COMMITTED
/REPEATABLE READ
/SERIALIZABLE
,级别由低到高。
在大量博文中经常提到的比如脏读(Dirty read)
,不可重复读(Non-repeatable read)
,幻读(Phantom)
,标准也有提及。
脏读(Dirty read)
1 2 3 4 5 6 |
1) P1 ("Dirty read"): SQL-transaction T1 modifies a row. SQL- transaction T2 then reads that row before T1 performs a COMMIT. If T1 then performs a ROLLBACK, T2 will have read a row that was never committed and that may thus be considered to have never existed. |
简而言之,即两个或多个事务中有一个事务更改了数据,无论提交与否,都会读取到数据的改动。
在ANSI SQL-92标准中,READ UNCOMMITTED
会可能出现脏读。
不可重复读(Non-repeatable read)
1 2 3 4 5 |
2) P2 ("Non-repeatable read"): SQL-transaction T1 reads a row. SQL- transaction T2 then modifies or deletes that row and performs a COMMIT. If T1 then attempts to reread the row, it may receive the modified value or discover that the row has been deleted. |
两个或多个事务中有一个事务更改了数据,只有提交之后,才会读取到数据的改动。
在ANSI SQL-92标准中,READ COMMITTED
不会出现脏读,但是会可能出现不可重复读。
幻读(Phantom)
1 2 3 4 5 6 7 |
3) P3 ("Phantom"): SQL-transaction T1 reads the set of rows N that satisfy some <search condition>. SQL-transaction T2 then executes SQL-statements that generate one or more rows that satisfy the <search condition> used by SQL-transaction T1. If SQL-transaction T1 then repeats the initial read with the same <search condition>, it obtains a different collection of rows. |
幻读发生的前提是一个事务改动(新增)并提交处于其他某一事务查询条件范围内,并且其他事务重复使用同一条件
进行查询。
在ANSI SQL-92标准中,REPEATABLE READ
不会出现脏读,不会出现不可重复读,但是会可能出现幻读。
在最高的事务隔离级别SERIALIZABLE
中,以上问题都不会出现。
MySQL InnoDB的RR事务隔离级别
InnoDB已经是MySQL首选存储引擎,内容会针对默认的隔离级别展开。
MySQL InnoDB实现了标准中的4个事务隔离级别,默认的事务隔离级别是REPEATABLE READ
:
1 2 3 4 5 6 7 |
mysql> SELECT @@tx_isolation; +-----------------+ | @@tx_isolation | +-----------------+ | REPEATABLE-READ | +-----------------+ |
和标准不同的是,MySQL在这一级别通过GAP锁消除了幻读问题。
MySQL的并发度和事务隔离级别成反比,事务隔离级别越高,并发度越低。
幻读的解决方案:GAP锁
GAP锁用于锁定记录的间隙,防止事务并发时同一搜索条件下,出现事务中前后两次读取结果不同的情况。
来看测试表test
:
1 2 3 4 5 6 7 8 9 10 |
mysql> desc test; +-------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+------------------+------+-----+---------+----------------+ | id | int(11) unsigned | NO | PRI | NULL | auto_increment | | v1 | int(11) | NO | UNI | 0 | | | v2 | int(11) | NO | MUL | 0 | | | v3 | int(11) | NO | | 0 | | +-------+------------------+------+-----+---------+----------------+ |
现有数据:
1 2 3 4 5 6 7 8 9 10 11 12 |
mysql> SELECT * FROM test; +----+-----+-----+-----+ | id | v1 | v2 | v3 | +----+-----+-----+-----+ | 1 | 1 | 1 | 1 | | 3 | 2 | 2 | 2 | | 4 | 3 | 3 | 4 | | 5 | 4 | 4 | 5 | | 8 | 100 | 100 | 101 | | 12 | 200 | 200 | 200 | +----+-----+-----+-----+ |
事务A(ID 381797)尝试读取v1=10的记录:
1 2 3 |
mysql> SELECT * FROM test WHERE v1=10 FOR UPDATE; Empty set (0.01 sec) |
这是一个当前读,由于v1
是一个唯一索引,v1=10这一个条件处于id=5,v1=4与id=8,v1=100的记录之间的间隙,所以为了防止两次读取数据不一样,需要在这之间增加GAP锁。
同时事务B(ID 381798)尝试写入一条记录:
1 2 3 |
mysql> INSERT INTO test(v1,v2,v3) VALUES(6,6,6); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction |
v1=6处于间隙之中,通过查询当前锁情况:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
mysql> SELECT * FROM information_schema.INNODB_LOCKS\G *************************** 1. row *************************** lock_id: 381798:1437:4:6 lock_trx_id: 381798 lock_mode: X,GAP lock_type: RECORD lock_table: `learn`.`test` lock_index: v1 lock_space: 1437 lock_page: 4 lock_rec: 6 lock_data: 100 *************************** 2. row *************************** lock_id: 381797:1437:4:6 lock_trx_id: 381797 lock_mode: X,GAP lock_type: RECORD lock_table: `learn`.`test` lock_index: v1 lock_space: 1437 lock_page: 4 lock_rec: 6 lock_data: 100 |
后来事务B需要等待先来事务A释放这一区间上的GAP锁,最终锁等待超时。
快照读
通过锁定,是可以实现RR事务隔离级别的。关键在于,对于需要改动的数据或者数据范围,不要过早的释放锁,在事务完成之后
才进行释放。
简单读取操作是S锁,其他操作可以是X锁,S锁和X锁不能同时存在。
《事务处理原理》一书中提到两阶段锁定
(2PL,two-phase locking),即一个事务必须在释放它的任何锁之前获得它所有的锁。如果用锁定的方式实现RR,在整个事务中,其他事务的简单读操作也会被阻塞。
InnoDB实现了MVCC的方式,可以实现事务中简单读不需锁定的效果,即实现了快照读。