曹耘豪的博客

MySQL之死锁处理

  1. 死锁如何发生

死锁如何发生

  1. 进程1开始事务，修改a记录
  2. 进程2开始事务，修改b、然后修改a，此时进程2会卡住，等待进程1释放a记录
  3. 进程1尝试修改b记录，报错如下
1
1213 - Deadlock found when trying to get lock; try restarting transaction

此时：

进程1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
mysql> select * from user;
+---
-+---
---
+
| id | name |
+---
-+---
---
+
|  1 | a    |
|  2 | b    |
|  3 | c    |
+---
-+---
---
+
3 rows in set (0.04 sec)

mysql> begin;
Query OK, 0 rows affected (0.01 sec)

mysql> update user set name = 'a1' where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update user set name = 'b1' where id = 2;
1213 - Deadlock found when trying to get lock; try restarting transaction

mysql> select * from user;
+---
-+---
---
+
| id | name |
+---
-+---
---
+
|  1 | a    |
|  2 | b    |
|  3 | c    |
+---
-+---
---
+
3 rows in set (0.04 sec)
进程2
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update user set name = 'b2' where id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update user set name = 'a2' where id = 1;
Query OK, 1 row affected (8.39 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from user;
+---
-+---
---
+
| id | name |
+---
-+---
---
+
|  1 | a2   |
|  2 | b2   |
|  3 | c    |
+---
-+---
---
+
3 rows in set (0.04 sec)
  技术 / 数据库