CODEWITHSUNDEEP
pythonmysqlthread-safetylockinginnodb
badideas
badideas

Reputation: 3567

how can i ensure innodb thread safety

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

The python script which handles the processing basically does this:

  1. get next file which has status = 'unprocessed'
  2. set the status to 'processing'
  3. call some C++ script to process that file
  4. mark it as 'processed'
  5. end

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.

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?

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

with lock:
    update_file_status(file_id, "processing")

?

Upvotes: 3

Views: 136

Answers (1)

Maxim Krizhanovsky
Maxim Krizhanovsky

Reputation: 26729

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.

Upvotes: 3

Related Questions