测试环境隔离级别:REPEATABLE-READ
行级别的 - Share and Exclusive Locks
- 共享锁 S:允许持有S锁的事务对行进行读操作
- 排他锁 X: 允许持有X锁的事务对行进行update或delete操作
表级别的意向锁 - Intention Lock
InnoDB支持多粒度的锁定,允许行锁和表锁共存。通过意向锁来实现。
比如,SELECT ... LOCK IN SHARE MODE
sets an IS
lock and SELECT ... FOR UPDATE
sets an IX
lock.
- 意向共享锁(IS):在对数据行获取S锁之前,必须先获取IS锁或更强级别的锁
- 意向排他锁(IX):在对数据行获取X锁之前,必须先获取IX锁
不同类型锁之间的兼容总结
X | IX | S | IS | |
---|---|---|---|---|
X | Conflict | Conflict | Conflict | Conflict |
IX | Conflict | Compatible | Conflict | Compatible |
S | Conflict | Conflict | Compatible | Compatible |
IS | Conflict | Compatible | Compatible | Compatible |
A lock is granted to a requesting transaction if it is compatible with existing locks, but not if it conflicts with existing locks.
A transaction waits until the conflicting existing lock is released. If a lock request conflicts with an existing lock and cannotbe granted because it would cause deadlock, an error occurs.
因此,除了对整个表的请求(比如,LOCK TABLES ... WRITE
)外,意向锁不会阻塞其他的事务。持有意向锁表示持有者正在锁定数据行,或者即将锁定数据行。
deadlock:
A deadlock can occur when the transactions lock rows in multiple tables (through statements such as UPDATE
or SELECT ... FOR UPDATE
), but in the opposite order. A deadlock can also occur when such statements lock ranges of index records and gaps, with each transaction acquiring some locks but not others due to a timing issue.
索引记录上的锁 - Record Lock
比如,SELECT c1 FOR UPDATE FROM t WHERE c1 = 10;
避免其他的事务在t.c1=10的位置进行insert、update和delete操作
record lock始终锁定索引记录,即使一个表没有进行索引定义,对于这种情况,InnoDB创建一个隐藏的聚集索引并使用该索引记录锁定
Cap Lock
比如,SELECT c1 FOR UPDATE FROM t WHERE c1 BETWEEN 10 and 20;会在t.c1在10到20之间的索引记录上加锁,防止其他的事务在t.c1列上插入10到20之间的值
测试:t2.id上无索引
mysql> select * from t2;
+------+
| id |
+------+
| 1 |
| 5 |
| 8 |
| 11 |
+------+
4 rows in set (0.00 sec)
S1 S2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> begin;
mysql> update t2 set id=6 where id=5; Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0 mysql> insert into t2 values(5);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into t2 values(1);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into t2 values(8);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into t2 values(6);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
S1锁住整张表
t2.id上加上索引的验证
mysql> alter table t2 add index id_idx(id);
Query OK, 0 rows affected (0.54 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> begin;
mysql> update t2 set id=6 where id=5; Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0 mysql> insert into t2 values(8);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t2 values(7);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into t2 values(1);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
S1会在[1,5] [5,8)区间加上间隙锁
Next-Key Locks
A next-key lock is a combination of a record lock on the index record and a gap lock on the gap before the index record.
InnoDB
uses next-key locks for searches and index scans, which prevents phantom rows .
幻影读的解释:http://dev.mysql.com/doc/refman/5.6/en/innodb-next-key-locking.html
验证如上:S1会在id=5的行加上X锁,为防止幻读,又在5的左边[1,5) 和5的又边(5,8)加上了gap锁
Insert Intention Locks
是gap锁的一种。多个事务如果不是在相同的索引范围内插入,则无需等待彼此
测试:
mysql> select * from t2;
+------+
| id |
+------+
| 1 |
| 5 |
| 8 |
| 11 |
+------+
会话S1 会话S2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t2(id) values(7); mysql> begin;
Query OK, 1 row affected (0.00 sec) Query OK, 0 rows affected (0.00 sec)
mysql> insert into t2(id) values(6);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t2(id) values(7);
Query OK, 1 row affected (0.00 sec)
两个会话在插入的行上获取排他锁前,分别在id为5~8的行记录范围内持有intention locks,但不会阻塞对方,因为行之间没有冲突。
再举一个会话在要插入的行记录上获取排他锁之前获取insert intention lock但被阻塞的例子:
S1在id>5的行持有排它锁。此排它锁包含5~11之间的gap锁
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t2 where id>5 for update;
+------+
| id |
+------+
| 8 |
| 11 |
+------+
S2上在S1查询出的行范围内插入行记录。会持有insert intention lock,但是会等待排它锁
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t2 values(7);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
参考链接:
http://dev.mysql.com/doc/refman/5.6/en/innodb-locking.html
http://www.hollischuang.com/archives/923