postgres locking with UPDATE FOR and foreign keys

Question

My Postgres tables:

performers

id
rank
group_id REFERENCES groups

performances

id
pay
performer_id REFERENCES performers

groups

id

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.

When inserting into performances, commit after each different performer_id to avoid cross-locking with the UPDATEs. The fact that it's a ShareLock strongly suggests that it's taken for the FK. — Daniel Vérité
– Daniel Vérité, Commented Mar 10, 2014 at 22:01
The error message that PostgreSQL sends back to the client is lacking in information about the other transaction, to avoid leaking information to the client. What does the PostgreSQL log file have to say about both transactions? — jjanes
– jjanes, Commented Mar 11, 2014 at 2:21

Erwin Brandstetter · Accepted Answer · 2025-02-13 01:07:03Z

3

Have all concurrent write operations lock rows in the same, consistent order to avoid deadlocks. Add ORDER BY to your locking statement and use the same sort order everywhere. Something like:

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

Where id would be the primary key or any stable, unambiguous combination of columns. Ideally in an order that goes with the physical order of rows and the flow of the query.

If multiple tables are involved, stick to the same sequence across tables. And try to keep transactions short, especially if you accumulate locks all over the place.

More in this thread 0n pgsql-general.
Or in the manual.

Keep triggers (and foreign key constraints) to the necessary minimum. They might fire to lock rows out of order elsewhere. Either way, first sort 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.

edited Feb 13 at 1:07

answered Mar 10, 2014 at 19:19

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

lollercoaster Over a year ago

what about inserts? this obviously wouldn't apply there, correct?

Erwin Brandstetter Over a year ago

@lollercoaster: Correct. You can't lock what's not there (yet). I don't see how INSERTs would deadlock, though? You might get a duplicate key violation, but that's a different kind of problem.

lollercoaster Over a year ago

yes, I get deadlock when I INSERT into the performance table many values at once from different threads - I suspect because of the foreign key? I'm actually not sure why.

lollercoaster Over a year ago

here's an example: stackoverflow.com/questions/9021249/postgresql-deadlock, and here's another: qcode.co.uk/postgresql-sharelock-deadlocks

Erwin Brandstetter Over a year ago

@lollercoaster: Right, foreign keys might do a number on you.

|

Collectives™ on Stack Overflow

postgres locking with UPDATE FOR and foreign keys

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related