我在使用sqlalchemy与InnoDB表格时,遇到了死锁的大问题。
sqlalchemy.exc.InternalError: (mysql.connector.errors.InternalError) 1213 (40001): Deadlock found when trying to get lock; try restarting transaction.
我已经将访问序列化,但仍然遇到了死锁错误。
这段代码在每个函数的第一次调用时执行。每个线程和进程都应该在这里等待,直到获取锁定。为简化起见,选择器已被删除。
# The work with the index -1 always exists.
f = s.query(WorkerInProgress).with_for_update().filter(
WorkerInProgress.offset == -1).first()
我已将代码精简至最小状态。目前仅在方法next_slice上运行并发调用。会话处理、回滚和死锁处理在外部处理。
即使所有访问都是串行化的,我仍然遇到了死锁问题。我还尝试在offset == -1实体中增加重试计数器。
def next_slice(self, s, processgroup_id, itemcount):
f = s.query(WorkerInProgress).with_for_update().filter(
WorkerInProgress.offset == -1).first()
#Take first matching object if available / Maybe some workers failed
item = s.query(WorkerInProgress).with_for_update().filter(
WorkerInProgress.processgroup_id != processgroup_id,
WorkerInProgress.processgroup_id != 'finished',
WorkerInProgress.processgroup_id != 'finished!locked',
WorkerInProgress.offset != -1
).order_by(WorkerInProgress.offset.asc()).limit(1).first()
# *****
# Some code is missing here. as it's not executed in my testcase
# Fetch the latest item and add a new one
item = s.query(WorkerInProgress).with_for_update().order_by(
WorkerInProgress.offset.desc()).limit(1).first()
new = WorkerInProgress()
new.offset = item.offset + item.count
new.count = itemcount
new.maxtries = 3
new.processgroup_id = processgroup_id
s.add(new)
s.commit()
return new.offset, new.count
我不明白为什么会发生死锁。
我已经通过在一个查询中获取所有项目来减少死锁,但仍然出现死锁。也许有人可以帮助我。