SQL并发事务忽略彼此的锁???死锁 [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 引擎的 MariaDB 10.1.30、Python 和 MySQLdb(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)

一旦主脚本获得锁，小戳脚本就会挂起，无法选择行.十秒后，poke 脚本获得了锁，但是两个节点都执行了，再次！！！.如您所见，顶部的那个抱怨死锁，因为底部的已经在事务的其他地方插入了一行.

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 的过程尤其令人痛苦.由于我的客户希望使用 docker，我担心这可能不仅是一种不太可能的情况，而且是一种可能性，因为在产生极端负载时可能会同时创建 docker.

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 中读取旧数据，让他在 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.

由于这是一个完全不同的问题，我在这里用不同的解释和更相关的代码示例重新发布了这个问题:SELECT...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并发事务忽略彼此的锁???死锁 [InnoDB, Python]的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！