玉面飞龙的BLOG

Innodb虽然号称是row level lock，不过在隔离级别READ COMMITTED和REPEATABLE READ情况下,锁的范围比Oracle大很多。

DML时候，Innodb会对访问到（类似Oracle explain plan中的Predicate Information 中的access条目）的每一行上锁 ,尽管有些行不满足Where 子句中的全部条件（类似Oracle explain plan中的Predicate Information 中的filter条目），只要被访问(access)过，就会被锁住。而且还存在 Next-Key Locking。

在REPEATABLE READ 下测试,

mysql> create table t(id int primary key,name char(20)) engine=innodb;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from t;
+—-+——+
| id | name |
+—-+——+
| 1 | a |
| 2 | a2 |
| 3 | a3 |
| 4 | a4 |
| 5 | a5 |
| 6 | a6 |
| 7 | a7 |
| 8 | a8 |
| 9 | a9 |
| 10 | a10 |
| 11 | a11 |
| 12 | a12 |
| 13 | a13 |
+—-+——+
13 rows in set (0.00 sec)

测试表包括13行纪录

Session 1，使用全表扫描，查找满足名字是’a'的记录。相当于每一行都被access到；这在每一行上都加了锁。

mysql> set autocommit=0;
mysql> begin;
mysql> select * from t where name=’a’ for update;
+—-+——+
| id | name |
+—-+——+
| 1 | a |
+—-+——+
1 row in set (0.00 sec)

Session 2 去更新与session 1不相关的行，结果被锁住

mysql> update t set name=’binzhang’ where id=13;

This session hang……..

这说明在更新频繁的innodb表上，任何DML至少都要通过索引来完成。索引可以缩小要access的数据集，从而减少被”无辜”上锁的纪录。

在看什么叫做next-key lock。

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

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

mysql> select * from t where id>=12 and name=’a12′ for update;
+—-+——+
| id | name |
+—-+——+
| 12 | a12 |
+—-+——+
1 row in set (0.00 sec)

mysql> update t set name=’update it’ where id=12;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from t;
+—-+———–+
| id | name |
+—-+———–+
| 1 | a |
| 2 | a2 |
| 3 | a3 |
| 4 | a4 |
| 5 | a5 |
| 6 | a6 |
| 7 | a7 |
| 8 | a8 |
| 9 | a9 |
| 10 | a10 |
| 11 | a11 |
| 12 | update it |
| 13 | binzhang |
+—-+———–+
13 rows in set (0.00 sec)

如上session 1按照范围查询id>=12的，access的纪录有2条,为12，13。这时候如果其他session要对id=13上锁则需要等待。

但如果其他session要插入新的纪录,新纪录id也在id>=12之中。则也需要等待。

如下session 2 需要等待这个为了保护repeatiable read的Next-Key Locking。

mysql> insert into t values(14,’new record’);

在read commited下测试

Session 1

mysql> show variables like ‘%ISOLATION%’;
+—————+—————-+
| Variable_name | Value |
+—————+—————-+
| tx_isolation | READ-COMMITTED |
+—————+—————-+
1 row in set (0.01 sec)

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

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

mysql> select * from t where id>=12 and name=’a12′ for update;
+—-+——+
| id | name |
+—-+——+
| 12 | a12 |
+—-+——+
1 row in set (0.00 sec)

Session 2 插入新的纪录，未被阻塞

mysql> insert into t values(14,’this is at read commit level’);
Query OK, 1 row affected, 1 warning (0.01 sec)

似乎很好。我们将session 1的select for update替换为update再次测试

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

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

mysql> update t set name=’good test2′  where id>=12 and name=’good test’ ;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings:

Session 2插入新的纪录，则开始等待Session 1。可见，read commit模式仍然存在next-key locking.
mysql>  insert into t values(17,’binzhang’);
Hang……………..

总而言之，Mysql的锁是和隔离级别互相搭配的。在设计应用程序的时候，要提前选择好隔离级别。尤其是默认情况下,oracle是read committed; mysql是 repeatable read.且二者同在read commited模式下，锁的情况也是不同的。

作DML操作的时候最好是基于索引的等于操作，避免range 查询带来的next key locking问题。

• READ COMMITTEDA somewhat Oracle-like isolation level with respect to consistent (non-locking) reads: Each consistent read, even within the same transaction, sets and reads its own fresh snapshot. See Section 13.2.9.2, “Consistent Non-Locking Reads”.

  For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE), InnoDB locks only index records, not the gaps before them, and thus allows the free insertion of new records next to locked records. For UPDATE and DELETE statements, locking depends on whether the statement uses a unique index with a unique search condition (such as WHERE id = 100), or a range-type search condition (such as WHERE id > 100). For a unique index with a unique search condition, InnoDB locks only the index record found, not the gap before it. For range-type searches, InnoDB locks the index range scanned, using gap locks or next-key (gap plus index-record) locks to block insertions by other sessions into the gaps covered by the range. This is necessary because “phantom rows” must be blocked for MySQL replication and recovery to work.

• REPEATABLE READThis is the default isolation level of InnoDB. For consistent reads, there is an important difference from the READ COMMITTED isolation level: All consistent reads within the same transaction read the snapshot established by the first read. This convention means that if you issue several plain (non-locking) SELECT statements within the same transaction, these SELECT statements are consistent also with respect to each other. See Section 13.2.9.2, “Consistent Non-Locking Reads”.

  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. For a unique index with a unique search condition, InnoDB locks only the index record found, not the gap before it. For other search conditions, InnoDB locks the index range scanned, using gap locks or next-key (gap plus index-record) locks to block insertions by other sessions into the gaps covered by the range.