Mysql数据库锁定机制详细介绍(9)

MyISAM存储引擎基本上可以说是对MySQL所提供的锁定机制所实现的表级锁定依赖最大的一种存储引擎了，虽然MyISAM存储引擎自己并没有在自身增加其他的锁定机制，但是为了更好的支持相关特性，MySQL在原有锁定机制的基础上为了支持其ConcurrentInsert的特性而进行了相应的实现改造。

而其他几种支持事务的存储存储引擎，如Innodb，NDBCluster以及BerkeleyDB存储引擎则是让MySQL将锁定的处理直接交给存储引擎自己来处理，在MySQL中仅持有WRITE_ALLOW_WRITE类型的锁定。

由于MyISAM存储引擎使用的锁定机制完全是由MySQL提供的表级锁定实现，所以下面我们将以MyISAM存储引擎作为示例存储引擎，来实例演示表级锁定的一些基本特性。由于，为了让示例更加直观，我将使用显示给表加锁来演示：RITE_ALLOW_READ 类型的写锁定。

刻

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 互相等等待对方的资源释放之后才能释放自己的资源,造成了死锁

 

行级锁定

行级锁定不是MySQL自己实现的锁定方式，而是由其他存储引擎自己所实现的，如广为大家所知的Innodb存储引擎，以及MySQL的分布式存储引擎NDBCluster等都是实现了行级锁定。

Innodb 锁定模式及实现机制

考虑到行级锁定君由各个存储引擎自行实现，而且具体实现也各有差别，而Innodb是目前事务型存储引擎中使用最为广泛的存储引擎，所以这里我们就主要分析一下Innodb的锁定特性。