mysql中的行锁

mysql的行锁跟oracle的行锁不一样的，mysql的行锁基于索引

session A:
test>select * from d;
+——+——+
| id | name |
+——+——+
| 1 | aa |
| 2 | bbb |
+——+——+
2 rows in set (0.00 sec)

test>select * from d where name=’aa’ for update;
+——+——+
| id | name |
+——+——+
| 1 | aa |
+——+——+
1 row in set (0.00 se
session B;

test>update d set id=1111 where name=’bbb’;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

select * from information_schema.innodb_locks\G;
***************** 1. row *****************
lock_id: 4637:41:3:1
lock_trx_id: 4637
lock_mode: X
lock_type: RECORD
lock_table: test.t5
lock_index: GEN_CLUST_INDEX
lock_space: 41
lock_page: 3
lock_rec: 1
lock_data: supremum pseudo-record
***************** 2. row *****************
lock_id: 4634:41:3:1
lock_trx_id: 4634
lock_mode: X
lock_type: RECORD
lock_table: test.t5
lock_index: GEN_CLUST_INDEX
lock_space: 41
lock_page: 3
lock_rec: 1
lock_data: supremum pseudo-record
2 rows in set (0.01 sec)

ERROR:
No query specified

select * from information_schema.innodb_lock_waits;
+——————-+——————-+—————–+——————+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
+——————-+——————-+—————–+——————+
| 4637 | 4637:41:3:1 | 4634 | 4634:41:3:1 |
+——————-+——————-+—————–+——————
不管对表进行什么样的insert都会阻塞，因为没有索引就是表锁了。加个索引，在操作就不会被阻塞了。我们看到2个事务都是添加了表锁，4637被4634阻塞。下面看下添加非唯一索引引入的gap lock和next-key lock的问题。
sessionA
+——-+————————————————————————————————————-+
| Table | Create Table |
+——-+————————————————————————————————————-+
| t5 | CREATE TABLE t5 (
id int(11) DEFAULT NULL,
KEY idx_id (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

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

test>select * from t5 where id=11 for update;
+——+
| id |
+——+
| 11 |
+——+
1 row in set (0.01 sec)
session B;
.test>begin;
Query OK, 0 rows affected (0.00 sec)

test>select * from t5;
+——+
| id |
+——+
| 10 |
| 11 |
| 13 |
| 20 |
+——+
4 rows in set (0.00 sec)

.test>insert into t5 values(8);
Query OK, 1 row affected (0.00 sec)

test>insert into t5 values(12);被阻塞
test>insert into t5 values(15);
Query OK, 1 row affected (0.00 sec)

test>insert into t5 values(22);
Query OK, 1 row affected (0.00 sec)
我们看到只有12被阻塞了，这个就涉及到了next-key lock
什么是next-key lock?
Record lock: This is a lock on an index record.
记录锁，是一个索引记录的锁
Gap lock: This is a lock on a gap between index records, or a lock on the gap before the first or after the last index record.
间隔锁，这个锁是索引记录间的间隔上的锁，或是第一个记录或最后一个索引记录的间隔上的锁
Next-key lock: This is a combination of a record lock on the index record and a gap lock on the gap before the index record.
next-key lock:这个是索引记录行锁和索引记录之前间隔锁的组合。

默认情况下innodb是repeatable read的隔离级别，并且innodb_locks_unsafe_for_binlog系统变量禁用，这种情况下innodb使用next-key 锁来查询及索引扫描，来达到阻止幻象读。
innodb当它查询或浏览表索引的时候会在下一个记录上设置共享 或排他锁。上面的表情况中，有记录10，11，13，20，所以，它的间隔区间是
（负无穷，10],(10,11],(11,13],(13,20],(20,正无穷].我们是对记录11设置了锁，所以会对后一个区间加锁，也就是（11，13],就导致了上面的情况发生。
下面看下使用唯一索引去除间隔锁的情况，对上面的表设置唯一索引
create unique index idx_id on t5(id);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

重复之前的步骤，我们看到第二个session能够把12插入进表

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

insert into t5 values(12);
Query OK, 1 row affected (0.00 sec)

gap锁在唯一索引的情况下也是存在的，但是在多列的唯一索引上，查询使用了部分列还是会有gap锁，再看个官网例子
CREATE TABLE child (
id int(11) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
session a:
select * from child where id>90 for update;
+—–+
| id |
+—–+
| 102 |
| 150 |
+—–+
2 rows in set (0.00 sec)
session b:

insert into child values(100);
insert into child values(80);

下面的结论不对，确实是不该阻塞的，阻塞的原因不是for update引起的，应该查看下select * from INNODB_LOCK_WAITS\G;看下是哪个id导致了阻塞，可以使用SELECT CONNECTION_ID();查看当前连接的id来对比确认是否是因for update导致，很可能是之前的锁没有正常释放导致的。

都被阻塞，按之前的理论，id已经是主键了，不应该不阻塞了的啊，这块就引入了另外的一个概念意向锁，意向锁是表级别的锁，意向锁是间隔锁的一种

mysql@172.16.195.101.information_schema>select * from innodb_locks\G
***************** 1. row *****************
lock_id: 8947:90:3:1
lock_trx_id: 8947
lock_mode: X
lock_type: RECORD
lock_table: test.child
lock_index: PRIMARY
lock_space: 90
lock_page: 3
lock_rec: 1
lock_data: supremum pseudo-record
***************** 2. row *****************
lock_id: 8946:90:3:1
lock_trx_id: 8946
lock_mode: X
lock_type: RECORD
lock_table: test.child
lock_index: PRIMARY
lock_space: 90
lock_page: 3
lock_rec: 1
lock_data: supremum pseudo-record
2 rows in set (0.01 sec)

If a gap lock is taken for key values or ranges above the largest value in the index, LOCK_DATA reports “supremum pseudo-record”.
锁的兼容性如下：

X   IX  S   IS

X Conflict Conflict Conflict Conflict
IX Conflict Compatible Conflict Compatible
S Conflict Conflict Compatible Compatible
IS Conflict Compatible Compatible Compatible
for update是加了ix的锁，insert是x锁，insert在获取x锁之前先获得了IX锁与for update不冲突，但是后面的x锁与ix锁是冲突的，insert只能等待了，看来for update的影响还是很大的，对于写入多的系使用for update就是个灾难（是在默认的可重复读的隔离级别下的），在读提交的隔离级别下是另外一种情况了

gap锁可以在读提交的事务隔离级别被禁用，或启用innodb_locks_unsafe_for_binlog 系统变量