Deadlock involving foreign key constraint

Question

I would like to better understand the locking mechanism in postgres.

Let's say that a tree can have apples (through an external key on the apple table). It seems that when choosing a tree to block the update is obtained on the apple. However, the operation is not blocked, even if someone else has a lock on this apple.

Why is this so?

ps Please do not offer to remove "choose to update."

Scenario

Transaction 1 Transaction 2 BEGIN . update apple; . . BEGIN . select tree for update; . update apple; . --halts because of the other transaction locking an apple update apple; . -- deadlock . COMMIT --transaction succeeds 

The code

If you want to try it in your postgres - here is the code you can copy / paste.

I have the following db schema

 CREATE TABLE trees ( id integer primary key ); create table apples ( id integer primary key, tree_id integer references trees(id) ); 

and very simple data

 insert into trees values(1); insert into apples values(1,1); 

There are two simple transactions. One updates the apples, the second blocks the tree and updates the apple.

 BEGIN; UPDATE apples SET id = id WHERE id = 1; -- run second transaction in paralell UPDATE apples SET id = id WHERE id = 1; COMMIT; BEGIN; SELECT id FROM trees WHERE id = 1 FOR UPDATE; UPDATE apples SET id = id WHERE id = 1; COMMIT; 

When I run them, a deadlock occurs in the second update of the first transaction.

 ERROR: deadlock detected DETAIL: Process 81122 waits for ShareLock on transaction 227154; blocked by process 81100. Process 81100 waits for ShareLock on transaction 227153; blocked by process 81122. CONTEXT: SQL statement "SELECT 1 FROM ONLY "public"."trees" x WHERE "id" OPERATOR(pg_catalog.=) $1 FOR SHARE OF x" 

Answer 1

Just a wild hunch: you ran into a problem with implementation detail ...

In particular, your select tree for update statement gets an exclusive tree lock. And update apples operators get an exclusive lock on the corresponding apples.

When you run the update on apples, the external Postgres calls associated with each row are launched to ensure tree_id . I don’t remember their exact names from the head, but they are in the directory, and in the documentation there are fragments and fragments that refer to them explicitly or implicitly, for example:

 create constraint trigger ... on ... from ... 

http://www.postgresql.org/docs/current/static/sql-createtrigger.html

In any case, these triggers will fire something that makes up the following:

 select exists (select 1 from trees where id = 1); 

This is your problem: exclusive access due to select for update makes it wait for transaction 2 to release the tree lock to complete work on the update statement for apples, but transaction 2 is waiting for transaction 1 to complete to get the apple lock to start the expression about updating for apples.

As a result, Postgres blocks the deadlock.

Answer 2

It seems that index locks are not held for the entire transaction period. I think the main problem is that transaction 1 does the same UPDATE twice, but it needs to get more locks to execute the second UPDATE .

According to docs , index locks are held for a short time. Unlike data locks, they are not saved until the transaction is completed. Let's look at the chart in more detail.

Transaction 1 does the first UPDATE . This gets the row level lock in row in apples . During the operation, it also gets an index lock in trees . The transaction has not yet been completed, so row-level data locks are still held by passing 1. However, index lock on trees immediately released. Not sure why Postgres does this for all types of indexes.

Transaction 2 comes and blocks trees for updating. This blocks both data and index. This is not blocked because transaction 1 has already issued an index lock. This time both locks are held until the end of the transaction. You do not know why this index lock is held and the other is released.

Trade 1 returns and tries UPDATE again. Locking on apples great, as it already is. The lock on trees , however, is blocked because Transaction 2 already has it.

Adding UPDATE to transaction 2 causes it to wait for transaction 1, causing a deadlock.

EDIT:

I came back to investigate this a bit more when I have Postgres installed. This is actually really weird. I looked at pg_locks after transaction 2.

Transaction 1 has the following locks:

• RowExclusive on apples_pkey and apples
• Exclusive on its transaction and virtualxid

Transaction 2 has the following locks (and many other irrelevant ones):

• AccessShare on trees_pkey
• RowShare in the trees
• Exclusive on its transaction and virtualxid
• RowExclusive on apples_pkey and apples
• Exclusive on the tuple in apples

Transaction 2 also expects to receive a Share lock on transaction 1.

Interestingly, two transactions may contain a RowExclusive lock in the same table . However, exclusive locks conflict with Share, so transaction 2 expects transaction ID 1 of the transaction. docs mentions transaction blocking as a way to wait for another transaction. Therefore, it appears that transaction 2, although complete, is still waiting for transaction 1.

When transaction 1 continues, she wants to acquire a Share lock on transaction 2, and this creates a dead end. Why does he want to acquire a block of shares on transaction 2? Not too sure about that. Documents indicate that this information is not available in pg_locks . I'm going to guess that this is related to MVCC, but for me this is still a mystery.