jdbc mysql innodb 死锁 deadlock_记一次mysql死锁的原因分析和解决方法

本文中数据库是mysql，使用InnoDB引擎。

Deadlock found when trying to get lock; try restarting transaction; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction.

大概的意思是，数据库运行过程中发生了死锁现象。

=========================================================================

首先，先了解一下锁的一些知识：

1.共享锁(S锁)、排它锁(X锁)

共享锁又称读锁，是读取操作创建的锁。其他用户可以并发读取数据，但任何事务都不能对数据进行修改(获取数据上的排他锁)，直到已释放所有共享锁。如果事务T对数据A加上共享锁后，则其他事务只能对A再加共享锁，不能加排他锁。获准共享锁的事务只能读数据，不能修改数据。

排它锁又称写锁，如果事务T对数据A加上排他锁后，则其他事务不能再对A加任任何类型的封锁。获准排他锁的事务既能读数据，又能修改数据。

2.什么操作加锁

select * from t where 条件 --不会加锁

select * from t where 条件 for update --对符合条件的行加X锁

update t set xx=xx where 条件 --对要修改的行加X锁

update t1 left join t2 on t1.xx = t2.xx set xx = xx where  条件 --对t2加S锁，t1加X锁(本次死锁)

=====================================================================

下面是分析

T1:

BEGIN;

update t1 left join t2 on t1.id = t2.id set t1.num = t2.num;

update t2 set num = 80 where id = 400;

COMMIT;

T2:

BEGIN;

update t2 set num = 80 where id = 400;

COMMIT;

查看死锁日志：

show ENGINE InnoDB STATUS;

看日志分析如下：

事务1执行第一条语句时，对t2表相应的行加了S锁，对t1表相应的行加了X锁；同时，事务2到达，执行sql时要对行id=400加X锁，但发现事务1对该行加了S锁，所以只能进去队列等待；接着，事务1要执行第2条sql，需要对id=400的行加X锁，但发现事务2先申请了加X锁(在队列)，所以也只能进去队列等待。注意，此时已形成死锁条件了，事务2等事务1，事务1在等事务2，等到天荒地老海枯石烂！但mysql发现了这一次死锁，并把事务2干掉了。

日志部分内容如下：

------------------------

LATEST DETECTED DEADLOCK

------------------------

2016-10-09 16:54:04 7f245afa5700

*** (1) TRANSACTION:(事务2)

TRANSACTION 5865896, ACTIVE 4 sec starting index read

mysql tables in use 1, locked 1

LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)

MySQL thread id 3359, OS thread handle 0x7f245b027700, query id 118089 ... updating

UPDATE t2 set num = 80 where id = 400

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:(事务2要给id=400的行加排它锁，但要等)

RECORD LOCKS space id 438 page no 3 n bits 72 index `PRIMARY` of table `db`.`t2` trx id 5865896 lock_mode X locks rec but not gap waiting

Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 0

0: len 4; hex 80000190; asc     ;;(16进制，190即400)

1: len 6; hex 0000005980af; asc    Y  ;;

2: len 7; hex 26000001b624b3; asc &    $ ;;

3: len 4; hex 80000050; asc    P;;

*** (2) TRANSACTION:(事务1)

TRANSACTION 5865895, ACTIVE 9 sec starting index read

mysql tables in use 1, locked 1

6 lock struct(s), heap size 1184, 238 row lock(s)

MySQL thread id 3357, OS thread handle 0x7f245afa5700, query id 118093 ... updating

UPDATE t2 set num = 80 where id = 400

*** (2) HOLDS THE LOCK(S):(事务1对id=400的行已持有S锁)

RECORD LOCKS space id 438 page no 3 n bits 72 index `PRIMARY` of table `db`.`t2` trx id 5865895 lock mode S locks rec but not gap

Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 0

0: len 4; hex 80000190; asc     ;;(16进制，190即400)

1: len 6; hex 0000005980af; asc    Y  ;;

2: len 7; hex 26000001b624b3; asc &    $ ;;

3: len 4; hex 80000050; asc    P;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:(事务1要对id=400的行加X锁，发现要等事务2先完事)

RECORD LOCKS space id 438 page no 3 n bits 72 index `PRIMARY` of table `db`.`t2` trx id 5865895 lock_mode X locks rec but not gap waiting

Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 0

0: len 4; hex 80000190; asc     ;;(16进制，190即400)

1: len 6; hex 0000005980af; asc    Y  ;;

2: len 7; hex 26000001b624b3; asc &    $ ;;

3: len 4; hex 80000050; asc    P;;

*** WE ROLL BACK TRANSACTION (1) (事务2被强制回滚了)

------------

TRANSACTIONS

------------

====================================================================

解决方法：

如果事务1对表t2相应的行不加S锁，那么此死锁就不会形成了。所以我们可以对事务1的第一条语句拆为2条sql，先查询(普通的查询select * from t2 where 条件 是不会加S锁的)，再根据查询结果修改相应的行(update t1 set xx=xx where id = xx)，也就是从1拆为1+N。