CODEWITHSUNDEEP
pythonsqlpostgresqlconcurrencysqlalchemy
lollercoaster
lollercoaster

Reputation: 16503

postgres locking with UPDATE FOR and foreign keys

My Postgres tables:

performers

performances

groups

I often need to select all the performers from a group and update all their ranks. But this often leads to deadlocks since I'm doing an SELECT ... FOR UPDATE select and other threads are INSERTing at the same time.

Example of the error I see a lot (in Python SqlAlchemy):

DBAPIError: (TransactionRollbackError) deadlock detected
DETAIL:  Process 83182 waits for ShareLock on transaction 14282922; blocked by process 83171.
Process 83171 waits for ShareLock on transaction 14282925; blocked by process 83182.
HINT:  See server log for query details.
 'SELECT performers.id AS performers_id, performers.rank AS performers_rank, performers.group_id AS performers_group_id \nFROM performers \nWHERE performers.group_id = %(group_id_1)s FOR UPDATE' {'group_id_1': 2}

I've found a few examples of this behavior around as well.

How can I fix this? Can I switch to a different transaction locking level? I'd rather not just abort and have to retry - I want the database to take care of this contention for me.

There must be some way to fix this - what I want to do is quite simple.

Upvotes: 4

Views: 1868

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656754

You can avoid the deadlocks, if all concurrent write operations go about it in the same unique order. Add ORDER BY to your locking statement, and use the same sort order everywhere. Something like:

SELECT ... FROM performers WHERE ... ORDER BY performers.id FOR UPDATE

Where id would be the primary key (or any other stable, unambiguous combination of columns).

Also, if your operations involve multiple columns, do the locking in strictly the same sequence across tables.

More details in this thread in pgsql-general list.
Or in the manual.

Keep triggers (and foreign key constraints) to the necessary minimum. Either way, first sort out the FOR UPDATE locks. That might just solve your problems. If not, consider a different transaction isolation level. Serializable should do the trick. But then you have to be prepared to retry transactions until they succeed.

Upvotes: 2

Related Questions