MySQL锁定机制

共享锁

共享锁（S），是Share的缩写，共享锁的锁粒度是行或者元组（多行）。一个事务获得了共享锁之后，就可以对锁定范围内的数据进行读操作。

排他锁

排他锁（X），是eXclusive的缩写，排他锁的粒度和共享锁一样，也是行或者元组。一个事务获得排他锁之后，就可以对锁定范围内的数据执行insert/delete/update操作。

意向锁

意向锁是一种表级锁，锁的粒度是整张表。分为意向共享锁（IS）和意向排他锁（IX）。

那么意向锁是从何而来的呢？

因为表锁覆盖了行锁的数据，所以表锁和行锁也会产成冲突。例如：

A、事务1：BEGIN

B、事务1给T1加上了X锁，修改表表结构（如alter table

C、事务2：BEGIN

D、事务2对T1的一行加S锁或者X锁（事务2会被阻塞，等待事务1释放锁，它才能加锁成功）

事务1要操作整张表，故锁住了整张表。那么事务2就不能对该表的单条记录或者元组加X锁或者S锁，去读取或者修改记录。

为了方便地检测表级锁和行级锁之间的冲突，于是引入了意向锁。

采用了意向锁之后，上述的例子就变成：

A：事务1：BEGIN

B：事务1给T1加X锁，修改表结构

C：事务2：BEGIN

D：事务2给T1加上IX锁（事务被阻塞，等待加锁成功）

E：事务2给T1加上S锁或者X锁，成功

意向锁为了方便检测表级锁和行级锁之间的冲突，故在给一行记录加锁前，首先给该表加意向锁。也就是同时加意向锁和行级锁。

S、X锁兼容性矩阵

    S      X

S   +      -

X  -       -

+：兼容 ； -：不兼容

S、X、IS、IX锁兼容性矩阵

        IS     IX     S     X

IS     +      +      +    -

IX     +      +      -     -

S      +      -       +    -

X      -      -        -     -

+：兼容； -：不兼容

S、X、IS、IX锁兼容性矩阵为什么是这样子呢？

1、意向锁之间彼此不会冲突，因为它们都只是“有意”，而不是真干，所以是可以兼容的。在加行锁之前，会使用意向锁判断是否冲突；

2、IX和X的关系等同于X和X之间的关系，为什么呢？因为事务获得了IX锁，接下来就有权利获取X锁，这样就会出现两个事务都获取X锁的情况，这和我们已知的X锁和X锁之间互斥是矛盾的；

3、S和IS、X和IS、IX和IS也可以由此推导出来。

Innodb 锁定机制示例

mysql> create table test_innodb_lock (a int(11),b varchar(16)) engine=innodb;
Query OK, 0 rows affected (0.02 sec)

mysql> create index test_innodb_a_ind on test_innodb_lock(a);
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0 mysql> create index test_innodb_lock_b_ind on test_innodb_lock(b);
Query OK, 11 rows affected (0.01 sec)
Records: 11 Duplicates: 0 Warnings: 0

 

时刻	Session a	Session b
	行锁定基本演示
1	mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec)	mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec)
	mysql> update test_innodb_lock set b = 'b1' where a = 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 更新，但是不提交
2		mysql> update test_innodb_lock set b = 'b1' where a = 1; 被阻塞，等待
3	mysql> commit; Query OK, 0 rows affected (0.05 sec) 提交
4		mysql> update test_innodb_lock set b = 'b1' where a = 1; Query OK, 0 rows affected (36.14 sec) Rows matched: 1 Changed: 0 Warnings: 0 解除阻塞，更新正常进行
	无索引升级为表锁演示
5	mysql> update test_innodb_lock set b = '2' where b = 2000; Query OK, 1 row affected (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0	mysql> update test_innodb_lock set b = '3' where b = 3000; 被阻塞，等待
6
7	mysql> commit; Query OK, 0 rows affected (0.10 sec)
8		mysql> update test_innodb_lock set b = '3' where b = 3000; Query OK, 1 row affected (1 min 3.41 sec) Rows matched: 1 Changed: 1 Warnings: 0 阻塞解除，完成更新
	间隙锁带来的插入问题演示
9	mysql> select * from test_innodb_lock; +------+------+ | a | b |+------+------+ | 1 | b2 | | 3 | 3 | | 4 | 4000 | | 5 | 5000 | | 6 | 6000 | | 7 | 7000 | | 8 | 8000 | | 9 | 9000 | | 1 | b1 | +------+------+ 9 rows in set (0.00 sec) mysql> update test_innodb_lock set b = a * 100 where a < 4 and a > 1; Query OK, 1 row affected (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0
10		mysql> insert into test_innodb_lock values(2,'200'); 被阻塞，等待
11	mysql> commit; Query OK, 0 rows affected (0.02 sec)
12		mysql> insert into test_innodb_lock values(2,'200'); Query OK, 1 row affected (38.68 sec) 阻塞解除，完成插入
	使用共同索引不同数据的阻塞示例
13	mysql> update test_innodb_lock set b = 'bbbbb' where a = 1 and b = 'b2'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
14		mysql> update test_innodb_lock set b = 'bbbbb' where a = 1 and b = 'b1'; 被阻塞
15	mysql> commit; Query OK, 0 rows affected (0.02 sec)
16		mysql> update test_innodb_lock set b = 'bbbbb' where a = 1 and b = 'b1'; Query OK, 1 row affected (42.89 sec) Rows matched: 1 Changed: 1 Warnings: 0 session 提交事务，阻塞去除，更新完成
	死锁示例
17	mysql> update t1 set id = 110 where id = 11; Query OK, 0 rows affected (0.00 sec) Rows matched: 0 Changed: 0 Warnings: 0
18		mysql> update t2 set id = 210 where id = 21; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
19	mysql>update t2 set id=2100 where id=21; 等待sessionb释放资源，被阻塞
20		mysql>update t1 set id=1100 where id=11; Query OK,0 rows affected (0.39sec) Rows matched: 0 Changed: 0 Warnings:0 等待sessiona释放资源，被阻塞
	两个 session 互相等等待对方的资源释放之后才能释放自己的资源,造成了死锁