7/10/2023 0 Comments Simple sql deadlock example![]() That is necessary so that no concurrent transaction can delete the row referenced by our not yet committed INSERT. The INSERT statements take a FOR KEY SHARE lock on the referenced rows in parent.If we know the statements from our transactions in the example above, we can deduce the following: By examining the locks taken by these statements, you should be able to reconstruct the cause of the deadlock. Perhaps you are lucky, the SQL statements from the error message are enough to identify the database transactions involved, and it is easy to reconstruct the SQL statements that were issued from each transaction. Usually you need help from the application’s developers. However, PostgreSQL does not retain any memory of these previous statements, so we have to find some other way to get the whole picture. So some earlier statement in these transactions must already have taken locks that are part of the problem. The two statements from the error message alone could never cause a deadlock. To understand the cause of the deadlock, you would have to know all statements from the involved transactions, because locks are always held until the end of a transaction. The PostgreSQL log contains more helpful data, but also not enough to understand the deadlock. That is because the SQL statement from the other sessions participating in the deadlock may contain data that you have no right to see. ![]() The error message sent to the client does not contain any meaningful detail. STATEMENT: SELECT pdata FROM parent WHERE pid = 1 FOR UPDATE Process 19718: SELECT pdata FROM parent WHERE pid = 1 FOR UPDATE Process 19674: SELECT pdata FROM parent WHERE pid = 1 FOR UPDATE session 2 is unblocked and receives a result Process 19718 waits for ShareLock on transaction 1108 blocked by process 19674.ĬONTEXT: while locking tuple (0,1) in relation "parent" session 1 blocks, but after one second, it receives an errorĭETAIL: Process 19674 waits for ShareLock on transaction 1109 blocked by process 19718. SELECT pdata FROM parent WHERE pid = 1 FOR UPDATE ![]() INSERT INTO child VALUES (101, 1, 'another child') INSERT INTO child VALUES (100, 1, 'new child') Each transaction adds a row to the child table and then tries to modify the corresponding row in parent. To provoke the deadlock, we run the following transactions in parallel. INSERT INTO parent VALUES (1, 'no children yet') We will test our techniques for debugging deadlocks with the following example: A simple deadlock example Setting the stage This article shows some techniques on how to figure out the cause of a deadlock. (Updated ) Even if you understand what a deadlock is, debugging deadlocks can be tricky. ![]() Deadlock deadlock_timeout debugging error lock logging postgresql ![]()
0 Comments
Leave a Reply. |