为什么 mysql 死锁发生在选择更新和插入?

Question

我使用peewee，mysql编写了一个简单的锁类。获取方法有时会引发死锁异常。

这是 innodb 状态监控和代码的输出。

1. 似乎第一笔交易正在等待锁定并且什么都没有。那么，如果它没有持有任何需要的交易二，为什么会发生死锁呢？

2. 似乎选择更新和插入语句将相互锁定。但我仍然不明白为什么。你能帮忙说明一下吗？

------------------------
LATEST DETECTED DEADLOCK
------------------------
2022-01-18 13:40:37 0x7f565a5bc700
*** (1) TRANSACTION:
TRANSACTION 4137600947, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
SELECT `t1`.`id`, `t1`.`business_type`, `t1`.`resource`, `t1`.`owner`, `t1`.`timeout`, `t1`.`comment`, `t1`.`gmt_created`, `t1`.`gmt_modified` FROM `resource_((`t1`.`resource` = 'r') AND (`t1`.`business_type` = 't')) AND ((`t1`.`owner` = '119909') OR ((`t1`.`timeout` != 0) AND ((to_seconds('2022-01-18 13:40:37.3448`.`gmt_modified`)) > `t1`.`timeout`)))) LIMIT 1 OFFSET 0 FOR UPDATE
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 255733 page no 5 n bits 72 index lock_business_type_resource of table `database_name`.`resource_lock` trx id 4137600947 lock_mode X lociting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 1; hex 74; asc t;;
1: len 1; hex 72; asc r;;
2: len 4; hex 800036f5; asc   6 ;;
*** (2) TRANSACTION:
TRANSACTION 4137600943, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1
INSERT INTO `resource_lock` (`business_type`, `resource`, `owner`, `timeout`) VALUES ('t', 'r', '120048', 1)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 255733 page no 5 n bits 72 index lock_business_type_resource of table `database_name`.`resource_lock` trx id 4137600943 lock_mode X loc
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 1; hex 74; asc t;;
1: len 1; hex 72; asc r;;
2: len 4; hex 800036f5; asc   6 ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 255733 page no 5 n bits 72 index lock_business_type_resource of table `database_name`.`resource_lock` trx id 4137600943 lock mode S wai
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 1; hex 74; asc t;;
1: len 1; hex 72; asc r;;
2: len 4; hex 800036f5; asc   6 ;;
*** WE ROLL BACK TRANSACTION (1)

# coding=utf-8
import datetime
import logging
import peewee
from peewee import fn
from peewee import Model, CharField, IntegerField, DateTimeField, SQL
from peewee import MySQLDatabase

db_info = {}  # mysql database address port user and password
database = MySQLDatabase(db_info)  # db connection

class Lock(Model):
# below two row have unique index 
business_type = CharField(null=False)
resource = CharField(null=False)
owner = CharField(null=False)
timeout = IntegerField(null=False, default=0)
comment = CharField(null=True)
gmt_created = DateTimeField(constraints=[SQL("DEFAULT CURRENT_TIMESTAMP")])
gmt_modified = DateTimeField(constraints=[SQL("DEFAULT CURRENT_TIMESTAMP")])
class Meta:
indexes = (
(('business_type', 'resource'), True),
)
table_name = 'resource_lock'
database = database

def acquire(owner, timeout):
with database.atomic():
now = datetime.datetime.now()
try:
# two case that the lock can be selected
# 1. current process is the owner
# 2. lock timeout
# but the select statement here may hit deadlock occasionally.
# db deadlock should meas two sessions holding a row/lock
# and waiting for each other. but here we have
# unique constraint on resource and business_type. Only one row should be involved
lock = Lock.select().where(
Lock.resource == 'r',
Lock.business_type == 't',
((Lock.owner == owner) | (Lock.timeout != 0) & (fn.to_seconds(now) - fn.to_seconds(Lock.gmt_modified) > Lock.timeout))
).for_update().get()
if str(lock.owner) == (owner):
print 'select success {} {}'.format(owner, time.time())
else:
print 'select success timeout, lock_owner: {}, owner: {} {}'.format(lock.owner, owner, time.time())
lock.timeout = timeout
lock.gmt_created = now
lock.gmt_modified = now
lock.owner = owner
lock.save()
print 'select success {}'.format(owner)
return True
except Lock.DoesNotExist:
pass
# if lock not exist "Lock.DoesNotExist" multiple processes can create the lock at same time. Only one
# can succeeded. Others hit peewee.IntegrityError and acquire fail because of unique index
try:
Lock.create(business_type='t', resource='r', owner=owner, timeout=timeout)
print 'create success {} {}'.format(owner, time.time())
return True
except peewee.IntegrityError:
print 'create fail {} {}'.format(owner, time.time())
return False

if __name__ == '__main__':
while True:
time.sleep(random.choice([0.1, 0.2, 0.3, 0.4, 0.5]))
acquire(owner=os.getpid(), timeout=1)

Answer 1

如果该查询中确实存在OR，我建议您执行以下操作之一：

• 摆脱OR并使用UNION构建等效查询，然后找出最佳索引。

• 将查询拆分为两个SELECTs，然后为每个查询构建一个单独的事务。