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

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

 

合理利用锁机制优化MySQL

MyISAM 表锁优化建议