SELECT ... FOR UPDATE提交后选择旧数据 [英] SELECT... FOR UPDATE selecting old data after a commit
问题描述
美好的一天，
这是我在 SQL上的旧帖子的更新? DEADLOCK [InnoDB，Python] ，后来我意识到这个问题与我认为的问题无关.我正在尝试为客户端创建与基于T-SQL的脚本等效的MySQL.
This is an update to my old post at SQL Simultaneous transactions ignore each other's locks??? DEADLOCK [InnoDB, Python], as I realized later that the issue had nothing to do with what I thought the problem was. I'm trying to create the MySQL equivilent to a T-SQL based script for a client.
我有两个相同的脚本，同时运行 Alice 和 Barry .他们的目标是
I have two identical scripts, Alice and Barry running concurrently. Their goal is to
-
SELECT * FROM job WHERE status = 0 LIMIT 1 FOR UPDATE -
UPDATE job SET status = 1匹配jobID，执行其他操作，然后... -
COMMIT进行更改，然后继续工作
SELECT * FROM job WHERE status = 0 LIMIT 1 FOR UPDATEUPDATE job SET status = 1where jobIDs match, do some other stuff, and then...COMMITthe change, and proceed with the job
我遇到的问题是， Alice 进行了锁定，读取了作业，将其UPDATES更改为状态1，但是一旦她提交了更改， Barry 拿起锁并读取状态0，即原始状态...即使Alice有时在COMMIT
The problem I have is that Alice makes the lock, reads the job, UPDATES it to status 1, but as soon as she commits the change, Barry takes up the lock and reads a status of 0, the original status... Even Alice sometimes sees status 0 just after her COMMIT
这是我的python脚本的一小部分，但足以理解该过程:
This is an small segment from my python script, but it should be enough to understand the procedure:
connection = MySQLdb.connect(host=..., user=..., [...])
cursor = connection.cursor(MySQLdb.cursors.DictCursor)
[...]
execute("START TRANSACTION")
execute("SELECT * FROM job WHERE status = %s LIMIT 1 FOR UPDATE", 0)
job_data = cursor.fetchone()
# debug("Made lock")
if not job_data:
connection.commit()
# debug("No rows")
else:
# debug("Locked row with status "+str(job_data['status']))
execute("SELECT status FROM job")
# debug("Double checked status is "+str(cursor.fetchone()))
execute("UPDATE job SET status = %s WHERE jobID = %s", 1, job_data['jobID'])
time.sleep(5)
execute("SELECT status FROM job")
# debug("Status before commit "+str(cursor.fetchone()))
connection.commit()
# debug('Committed')
execute("SELECT status FROM job")
# debug("Status after commit "+str(cursor.fetchone()))
这个原始脚本的肿版本充满了调试方法，可以尝试并了解正在发生的事情，而time.sleep可以跟上正在发生的事情.我已在此摘录中注释掉了调试功能，以使其更易于阅读实际发生的情况.
这些是 Alice 和 Barry 的输出:
This bloated version of the original script is full of debug methods to try and understand what is happening, with time.sleep to be able to keep up with what's going on. I've commented out the debug functions in this extract to make it easier to read what is actually happening.
These are the outputs from Alice and Barry:
爱丽丝
41,351161: Made lock
41,351161: Locked row with status 0
41,351161: Double checked status is {'status': 0}
46,352156: Status before commit {'status': 1}
46,370601: Committed
46,370601: Status after commit {'status': 1} (Sometimes Alice sees 0 here)
鲍里
46,352682: Made lock
46,353184: No rows
48,365044: Made lock
48,365044: Locked row with status 0
48,365044: Double checked status is {'status': 0}
53,365062: Status before commit {'status': 1}
53,386910: Committed
53,388846: Status after commit {'status': 1}
输出开头的数字是时间戳，如SECONDS,MICROSECONDS.
The numbers at the start of the output are the timestamp, as SECONDS,MICROSECONDS.
Alice 持有该锁五秒钟，而 Barry 刚获得COMMITS锁.但是，Barry看到状态为0(测试期间只有一行).最后，他们俩都认为他们可以处理这项工作.
Alice holds the lock for five seconds, and as soon as she COMMITS, Barry takes the lock. However, Barry sees a status of 0 (there is only one row during testing). This ends up with both thinking they can process the job.
我不确定在提交后Barry为什么读取状态为0.它回滚了吗?从调用SELECT开始，他是否正在读取旧值的缓存?不同的隔离级别会有所帮助(似乎没有)吗?
I'm not sure why Barry reads a status of 0, after the commit. Is it rolled back? Is he reading a cache of the old value, from when he called his SELECT? Would a different isolation level help (didn't seem to)?
当我在封闭环境中执行此代码时，没有程序的其余部分，它就可以工作！！！ Barry 最终获得锁定后，便没有报告任何行.我不知道它有什么不同?我猜这意味着脚本中其他地方出了点问题.但是那会是什么呢?这是一个相当孤立的事务.在此代码未更改之前调用COMMIT，我认为以前的事务可能未正确关闭.
Alice 和 Barry 是测试时访问数据库的唯一进程(除了phpmyadmin).
When I execute this code in a closed environment, without the rest of the program, it works!!! Barry reports no rows as soon as he finally gets the lock. I don't understand how it could differ? I'm guessing this means that something is wrong elsewhere in the script. But what could it be? This is a fairly isolated transaction. Calling COMMIT just before this code doesn't change anything, I thought maybe a previous transaction wasn't closed properly.
Alice and Barry are the only processes accessing the database when testing (apart from phpmyadmin).
我正在运行MariaDB的InnoDB引擎，并使用MySQLdb(mysqlclient)作为Python的连接器.据我所知AUTOCOMMIT已关闭.该脚本很长，这就是为什么我只发送了几行的原因.我将在接下来的几天中尝试将其分割开，以尝试找出问题或创建一个小的示例脚本.我似乎找不到如何使MariaDB打印出更改和锁定日志的信息，但是如果我这样做，我将更新此帖子.
I'm running MariaDB's InnoDB engine, with MySQLdb (mysqlclient) as the connector for Python. AUTOCOMMITis off as far as I can tell. The script is quite long, which is why I only sent a few lines of it. I'll try slicing it apart in the next few days to try and isolate the issue, or to create a small example script. I can't seem to find out how to make MariaDB print out a log of changes and locks, but if I do I'll update this post.
任何想法，建议或评论都将很棒.
Any ideas, suggestions or comments would be amazing.
祝你有美好的一天！
推荐答案
在遵循了 Solarflare 的关于将程序一点一点地分割的建议之后，我最终发现了导致该问题的远程部分的问题.代码正常运行，但从T-SQL迁移到SQL后完全不同.
After following Solarflare's advice on slicing apart the program bit by bit, I eventually found the problem leading to a remote part of the code, which was functioning without error, but completely differently after the move from T-SQL to SQL.
我起初以为是SQL服务器的问题，但这只是一个讨厌的错误.由于某种原因，我现在陷入了可怕的僵局，但又有种值得研究的乐趣.
I thought at first that the issue with the SQL server, but it was just a nasty bug. I'm now getting horrible deadlocks for some reason, yet another pleasure to look into.
感谢您的评论以及您花费在解释什么地方上的问题上的时间，很抱歉，这只是一个个人愚蠢的问题
Thanks for the comment and the time you spent explaining what could be wrong, I'm sorry this turned out to be just a personal silly issue
祝你有美好的一天！
这篇关于SELECT ... FOR UPDATE提交后选择旧数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
