Pessimistic locking: select for update

[Abdelhadi92]

I need to lock an object in the database in order to prevent other transactions from modifying the same record. Within the Postgres such a lock should be done using the SELECT FOR UPDATE query.

How I can do this using Piccolo?

you can find it out in Django here and in Pony here

[dantownsend]

It's not currently possible, but I think it would be pretty simple to add.

We could add something like a lock_strength argument to the select method.

https://www.postgresql.org/docs/current/sql-select.html#SQL-FOR-UPDATE-SHARE

await MyTable.select(lock_strength='UPDATE')

I'm not certain on the best API for it yet, but something like that should work.

[deliro]

I guess this one is an absolute must have. Any updates on it?

[dantownsend]

Implemented by #1085