Understanding Postres Deadlock

user_2370400 0
Viewed 215

I have a Python Webapp with Flask and SQLAlchemy, and there's a system update process that occurs in multiple threads. When I run it, I'm getting a DeadlocK from Postgres.

The queries that appear in the logs are the following.

ERROR: deadlock detected
DETAIL:  Process 2269053 waits for ShareLock on transaction 42979254; blocked by process 2269014.
Process 2269014 waits for ShareLock on transaction 42979253; blocked by process 2269053.
Process 2269053: UPDATE sequence SET item_list='{"item_list": [162, 164]}' WHERE sequence.id = 1978

Process 2269014: UPDATE sequence SET item_list='{"item_list": [162, 165]}' WHERE sequence.id = 1977
HINT: See server log for query details.
while updating tuple (102,44) in relation "sequence"
STATEMENT:  UPDATE sequence SET item_list='{"item_list": [162, 164]}' WHERE sequence.id = 1978

I see that they are 2 different PK's, from my understanding when an update is performed only the row is locked and the statements are from 2 different rows. There's clearly something I'm misunderstanding so I wanted to ask if someone could help me clarify why this deadlocks happens and how can I solve it?

Thank you

deadlock sqlalchemy postgresql
1 Answers

The missing information is that a transaction can span multiple statements, and each of these can take locks. So each of the quoted UPDATE statements blocked on a lock taken by some statement in the other transaction.

For example, the previous statement of process 2269053 might have updated the row with id 1977, and the previous statement of process 2269014 might have updated the row with id 1978. There are of course numerous other possibilities.

You should figure out which part of your application issued these statements (application log file?) and look what these transactions did before. You might have to crank up application or database logging to get that information, if you cannot reconstruct it by looking at the code.

user_64643080
Related