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;

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

test

t5

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;

test>select * from t5;

| 10 |

| 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);

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;

insert into t5 values(12);

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;

+—–+

| 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已经是主键了，不应该不阻塞了的啊，这块就引入了另外的一个概念意向锁，意向锁是表级别的锁，意向锁是间隔锁的一种

X   IX  S   IS
           

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