首页新闻找找看学习计划

一条简单SQL语句出现死锁情况,请帮忙各位大神帮忙分析一下。

0
[待解决问题]

小弟有一SQL语句现在在多并发的情况下会出现死锁。希望各位大神帮忙给小弟分析一下。

UPDATE accountinfo SET usecnt = 20, version = version+1 WHERE usecnt > 0 AND accountid = 12 and version = 102

MySQL数据库,存储引擎为InnoDB,其中accountid为主键,usecnt和version没有任何索引。 当前事务使用的事务隔离级别为:READ_COMMITTED

 

发生死锁的日志如下:

*** (1) TRANSACTION:
TRANSACTION 1331664, ACTIVE 40 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 11 lock struct(s), heap size 2936, 6 row lock(s), undo log entries 9
MySQL thread id 106, OS thread handle 0x334c, query id 2285 192.168.25.85 root updating
UPDATE accountinfo SET usecnt = 20, version = version+1 
            WHERE usecnt > 0 AND accountid = 12 and version = 102
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2151 page no 3 n bits 80 index `PRIMARY` of table `stamp_server_db`.`accountinfo` trx id 1331664 lock_mode X locks rec but not gap waiting
Record lock, heap no 10 PHYSICAL RECORD: n_fields 35; compact format; info bits 0

 

*** (2) TRANSACTION:
TRANSACTION 1331673, ACTIVE 39 sec starting index read
mysql tables in use 1, locked 1
11 lock struct(s), heap size 2936, 6 row lock(s), undo log entries 9
MySQL thread id 109, OS thread handle 0x3910, query id 2355 192.168.25.85 root updating
UPDATE accountinfo SET usecnt = 20, version = version+1 
            WHERE usecnt > 0 AND accountid = 12 and version = 102
 
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 2151 page no 3 n bits 80 index `PRIMARY` of table `stamp_server_db`.`accountinfo` trx id 1331673 lock mode S locks rec but not gap
Record lock, heap no 10 PHYSICAL RECORD: n_fields 35; compact format; info bits 0
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
 
RECORD LOCKS space id 2151 page no 3 n bits 80 index `PRIMARY` of table `stamp_server_db`.`accountinfo` trx id 1331673 lock_mode X locks rec but not gap waiting
Record lock, heap no 10 PHYSICAL RECORD: n_fields 35; compact format; info bits 0
 
*** WE ROLL BACK TRANSACTION (2)

 

 

reddog的主页 reddog | 菜鸟二级 | 园豆:202
提问于:2017-01-08 15:45
< >
分享
所有回答(2)
0

肯定会出现索引啊,锁的变化是S,U,X,你使用table hint,直接U锁试试

悦光阴 | 园豆:2239 (老鸟四级) | 2017-01-09 10:04

非常感谢大神的回答。这里让我很不解的是,为什么会去获取S锁呢?我理解的,UPDATE操作会直接获取一个X锁,其他事务过来只会等待。这里死锁的原因,貌似第一个事务先拿了X锁,第二个事务先拿了S锁,然后等待X锁,结果第一个事务又去拿S锁,这俩事务就锁在那里了。应该是这样的吧?

支持(0) 反对(0) reddog | 园豆:202 (菜鸟二级) | 2017-01-09 14:54

@reddog: 你首先要找到数据,这是一个读取的过程,SQL Server会在读数据的过程中加S锁,在找到数据后,会加U锁;在更新数据时,会加X锁;MySQL,我不是很了解,你可以尝试一下,不加S锁,直接加X锁

支持(0) 反对(0) 悦光阴 | 园豆:2239 (老鸟四级) | 2017-01-09 17:51
0

看一下这篇文章 http://hedengcheng.com/?p=771

让我发会呆 | 园豆:2629 (老鸟四级) | 2017-01-09 10:21

非常感谢啊,这篇文章我看过了,有些东西没有太理解,还是看不出来我这里有什么问题。我这技术实在太水了。

支持(0) 反对(0) reddog | 园豆:202 (菜鸟二级) | 2017-01-09 14:55

@reddog: 不是gap锁的问题吗?

支持(0) 反对(0) 让我发会呆 | 园豆:2629 (老鸟四级) | 2017-01-09 15:37
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册