SQL同时事务忽略彼此的锁??? DEADLOCK [InnoDB，Python] [英] SQL Simultaneous transactions ignore each other's locks??? DEADLOCK [InnoDB, Python]

问题描述

美好的一天！

我遇到了头灯.我的客户要求我重新调整python程序的用途，使其与MySQL而不是Microsoft的SQL Server一起使用.我无法在SQL中找到等效的解决方案.

I've run into a head burner. My client requires me to repurpose a python program to work with MySQL instead of Microsoft's SQL Server. I'm having trouble finding an equivalent solution in SQL.

我似乎无法在一行上创建适当的更新锁.当两个相同的事务同时执行时，尽管在串行化隔离级别打开了一个事务，并且使用SELECT ... FOR UPDATE.它们都读取了该行.

I can't seem to create a proper update lock on a row. When two identical transactions execute simultaneously, they both read the row despite opening a transaction at the serialize isolation level, and with SELECT ... FOR UPDATE.

也许我的代码会更好地解释它:

Maybe my code will explain it better:

execute("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE")
execute("START TRANSACTION")
execute("SELECT * FROM job WHERE status = %s LIMIT 1 FOR UPDATE", jobStatus.imported)
job_data = cursor.fetchone()
if not job_data:
    connection.rollback()
else:
    execute("UPDATE job SET status = %s WHERE jobID = %s", jobStatus.ingesting, job_data['jobID']) # Update the job data

    if job_data['jobUUID'] == None:
        job_data['jobUUID'] = new_unused_uuid().bytes
        execute("UPDATE job SET jobUUID = %s WHERE jobID = %s LIMIT 1", job_data['jobUUID'], job_data['jobID'])
    if job_data['dateAdded'] == None:
        job_data['dateAdded'] = datetime.datetime.now()
        execute("UPDATE job SET dateAdded = %s WHERE jobID = %s LIMIT 1", job_data['dateAdded'], job_data['jobID'])

    execute("INSERT INTO ingestJob (fk_jobUUID, fk_nodeUUID, status) VALUES (%s, %s, %s) ON DUPLICATE KEY UPDATE fk_nodeUUID = %s, status = %s", job_data['jobUUID'], unique_id.bytes, smallJobStatus.running, unique_id.bytes, smallJobStatus.running)

    connection.commit()

过程如下:

1. 使用FOR UPDATE选择一项可能的工作
2. 如果没有作业，则回滚(释放锁)，或者...
3. ...更新该行，使其无法重新选择，进行一些不相关的更改
4. 提交

他们俩都做自己的事，而忽略了对方的锁和交易

让我害怕的是它是随机的.几乎每隔一次运行都会发生一次.在隔离的环境中尝试相同的查询时，如果有足够的延迟，我会得到想要的确切结果.
Alice调用SELECT ... FOR UPDATE后，Barry 无法读取行，并挂起，直到Alice提交或回滚为止.我的现象要求在同一程序的两个实例之间同时准确执行.

They both do their own thing, ignoring each other's lock and transaction

What scares me is that it's random. It happens roughly every other run. When trying the same queries in an isolated environment, with sufficient delay, I get the exact result I want.
As soon as SELECT ... FOR UPDATE is called by Alice, Barry can't read the row, and hangs until Alice commits or rolls back. My phenomenon requires exact simultaneous execution between the two instances of the same program.

我尝试在第4行打印获取的行，它们返回 exact 相同的行...我在Ubuntu Server上的InnoDB引擎，Python和MySQLdb上使用MariaDB 10.1.30 (mysqlclient)模块进行通讯.是玛丽亚吗?我认为与MySQL相比，它可能是更好的选择.

I tried printing the fetched row on line 4, they return the exact same row... I'm using MariaDB 10.1.30 with the InnoDB engine on Ubuntu Server, with Python and the MySQLdb (mysqlclient) module for communication. Is it Maria? I thought it might be the superior alternative compared to MySQL.

一个提出了一个例外，因为它正在与另一个争夺资源(太慢了！)

One raises an exception, because it's fighting over resources with the other (too slow noob!)

为了证明已进行FOR UPDATE锁定和正确的事务处理，我进行了以下测试.我同时运行了这个小的Poke脚本，同时在对主脚本进行提交之前添加了time.sleep(10)，以使锁保持活动状态至少10秒钟.

To show that a FOR UPDATE lock and a proper transaction are made, I did the following test. I ran this little poke-script at the same time, while adding time.sleep(10) just before the commit on the main script, to keep the lock active for at least 10 seconds.

while True:
    cursor.execute("SELECT * from job FOR UPDATE")
    print('Selected')
    time.sleep(1)
    connection.rollback()
    print('Released')
    time.sleep(1)

一旦主脚本获得了锁定，小戳脚本便会挂起，无法选择该行.十秒钟后，戳脚本获取了锁，但是两个节点都再次执行！.如您所见，最上面的那个抱怨死锁，因为最下面的那个已经在事务中的其他位置插入了一行.

As soon as the main scripts acquire the lock, the small poke script would hang, not being able to select the row. After ten seconds, the poke script acquires the lock, but both nodes executed, again!!!. As you can see, the one on the top complains about a deadlock, because the bottom one already inserted a row somewhere else in the transaction.

我愿意接受其他更正确的SQL解决方案.也许我做错了.在T-SQL中，可以更新一行并使用OUTPUT子句返回修改后的行，就好像在UPDATE之后运行了SELECT语句一样.我唯一的解决方案是使用FOR UPDATE选择一行，然后运行UPDATE.我还没有真正考虑过使用过程，将它从Python中移除并在MariaDB上本机运行会更好吗?

I'm open to other more-correct SQL solutions. Maybe I'm doing it wrong. In T-SQL, it was possible to UPDATE a row and return the modified row with the OUTPUT clause, as if a SELECT statement had been run after the UPDATE. My only solution is to SELECT a row with FOR UPDATE, and then run the UPDATE. I haven't really thought about using procedures, would it be better to take it away from Python and run native on MariaDB?

如果有任何提示或建议，我将不胜感激.我对SQL的经验不是很丰富，但是离开SQL Server的经历特别麻烦.由于我的客户希望使用泊坞窗，我担心这可能不只是一种不太可能的情况，而是一种可能性，因为在产生极高的负载时可能会同时创建泊坞窗.

I would really appreciate any hints or suggestions. I'm not that experienced with SQL, but the move away from SQL Server has been particularly punishing. As my client wishes to use dockers, I fear that this may not just be an unlikely scenario, but a possibility, as dockers might be created simultaneously when an extreme load is generated.

谢谢，祝你有美好的一天！

Thanks, and have a great day!

推荐答案

在事务的不同部分添加了一些time.sleep语句后，我意识到问题与 Alice 无关.和 Barry 同时执行或忽略彼此的锁.

After adding some time.sleep statements in different parts of the transaction, I realized that the problem has got nothing to do with Alice and Barry executing simultaneously or ignoring each other's locks.

如果没有sleep语句，那么查看发生了什么事情太快了.真正的问题是，即使在 Alice 的COMMIT更新工作状态之后，Barry仍会读取其SELECT... FOR UPDATE中的OLD数据，从而使他在Alice释放锁定后立即承担相同的工作.

Without the sleep statements, it was too fast to see what was going on. The real issue is that Barry reads OLD data in his SELECT... FOR UPDATE, even after Alice's COMMIT which updates the job status, leaving him to take up the same job immediately after Alice releases the lock.

由于这是一个完全不同的问题，因此我在此处重新发布了问题，并提供了不同的解释和更相关的代码示例:选择...提交后进行FOR UPDATE选择旧数据

As this is a completely different issue, I've reposted the question with a different explanation and more relevant code samples here: SELECT... FOR UPDATE selecting old data after a commit

对不起，这对您没有帮助.我还没有亲自找到问题.

I'm sorry this couldn't help you. I've yet to find the problem myself.

这篇关于SQL同时事务忽略彼此的锁??? DEADLOCK [InnoDB，Python]的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！