Peewee row level blocking

Question

I'm currently using Peewee as an ORM in my project. In my current situation, I have some processes each having a database connection. All of these processes need to access a certain table simultaneously. I'm looking for some way to make them coordinated without using a central controller. For this, when a row is read by a process, it must get locked and no other process could read that row. Blocked processes should continue with other non-blocked rows.

I searched around and found that MySql already has an internal locking mechanism, described here and apparently it must be used on indexed columns to behave as expected (from here). But I couldn't find anything related in the peewee documents. Is there any extension providing these feature? Or should i write raw SQL queries containing FOR Update clause?

Malanie

Answer 1

Peewee supports using the FOR UPDATE clause, and I think this is probably what you want. It won't prevent other clients from reading, but it will prevent modifications for as long as the transaction holding the lock is open.

Ex:

with db.atomic():
    # lock note.
    note = Note.select().where(Note.id == 123).for_update().get()
    # As long as lock is held no other client can modify note.

Helminthology

Does it support NOWAIT or SKIP LOCKED ? I can't find anything in the documents. Another question, you said that "using FOR UPDATE won't prevent other clients from reading", so how can i prevent these reads ? I know that SELECT ... FOR SHARE are blocked but simple SELECT queries are not blocked. – Malanie

BTW, how isolation level of a transaction can be specified in db.atomic()? – Malanie

.for_update('for share nowait') -- or .for_update('for update nowait') - etc – Helminthology