如何确保innodb线程安全

I have an InnoDB table which lists a bunch of files which need to be processed.

我有一个InnoDB表，它列出了一些需要处理的文件。

The python script which handles the processing basically does this:

处理处理处理过程的python脚本基本上是这样做的:

1. get next file which has status = 'unprocessed'
2. 获取状态= '未处理'的下一个文件
3. set the status to 'processing'
4. 将状态设置为“processing”
5. call some C++ script to process that file
6. 调用一些c++脚本来处理该文件
7. mark it as 'processed'
8. 其标记为“加工”
9. end
10. 结束

This python script will be called multiple times, and so I am somewhat concerned that there could be some problem at step 1 and 2, where process B will get the same row from the db as process A before process A has the time to update the status in step 2.

这个python脚本将调用多次,所以我有点担心,可能会有一些问题在步骤1和2,进程B将得到相同的行从db处理过程之前有时间更新状态在步骤2中。

I am guessing the Python GIL doesn't help me here since the interpreter will release the GIL when doing the SQL (right?). And from what I am reading, doing table locks on InnoDB doesn't work. Will the row-level locking take care of the issue here? If not, what are my other options?

我猜Python GIL在这里没有帮助我，因为在做SQL的时候，解释器会释放GIL(对吧?)从我正在阅读的内容来看，在InnoDB上做表格锁是行不通的。行级锁会处理这里的问题吗?如果没有，我还有其他选择吗?

Could I simply do "from threading import Lock" and add

我可以简单地做“从线程导入锁”和添加吗

with lock:
    update_file_status(file_id, "processing")

?

吗?

1 个解决方案

#1

use SELECT FOR UPDATE statement - this will aquire exclusive lock on the row and won't allow other SELECT FOR UPDATE on the same row.

使用SELECT FOR UPDATE语句——这将获取行上的独占锁，并且不允许其他SELECT对同一行进行更新。