Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>It looks like index locks are not held for the whole duration of the transaction. I think the main issue is that transaction 1 is doing the same <code>UPDATE</code> twice, but it needs to acquire more locks to do the second <code>UPDATE</code>.</p> <p>According to the <a href="http://www.postgresql.org/docs/9.2/static/locking-indexes.html" rel="nofollow">docs</a>, index locks are only held for a short time. Unlike data locks, they are not held until the transaction completes. Let's look at the timeline in more detail.</p> <p>Transaction 1 does the first <code>UPDATE</code>. This acquires a row level lock on the row in <code>apples</code>. During the operation, it also acquires a lock on the index in <code>trees</code>. The transaction has not yet committed, so the row level data lock is still held by transation 1. However, the index lock on <code>trees</code> is immediately released. Not sure why Postgres does this for all index types.</p> <p>Transaction 2 comes along and locks <code>trees</code> for update. This locks both the data and the index. This does not block since Transaction 1 has already released the index lock. This time, both locks are held until the end of the transaction. Not sure why this index lock is held while the other one is released.</p> <p>Transaction 1 comes back and tries to <code>UPDATE</code> again. The lock on <code>apples</code> is fine, since it already has it. The lock on <code>trees</code>, however, blocks since Transaction 2 has it already.</p> <p>Adding the <code>UPDATE</code> in Transaction 2 makes it wait on Transaction 1, causing deadlock. </p> <p><strong>EDIT:</strong></p> <p>I came back to investigate this some more now that I have Postgres installed. It is actually really weird. I looked at <code>pg_locks</code> after committing transaction 2. </p> <p>Transaction 1 has the following locks:</p> <ul> <li>RowExclusive on apples_pkey and apples</li> <li>Exclusive on its transactionid and virtualxid</li> </ul> <p>Transaction 2 has the following locks (and a lot of other irrelevant ones):</p> <ul> <li>AccessShare on trees_pkey</li> <li>RowShare on trees</li> <li>Exclusive on its transactionid and virtualxid</li> <li>RowExclusive on apples_pkey and apples</li> <li>Exclusive on a tuple in apples</li> </ul> <p>Transaction 2 is also waiting to acquire a Share lock on Transaction 1.</p> <p>Interestingly, <a href="http://www.postgresql.org/docs/9.1/static/explicit-locking.html" rel="nofollow">two transactions can hold a RowExclusive lock on the same table</a>. However, an Exclusive locks conflicts with a Share, so Transaction 2 is waiting on Transaction 1's transaction id. The <a href="http://www.postgresql.org/docs/current/static/view-pg-locks.html" rel="nofollow">docs</a> mention transaction locks as a way to wait for the other transaction. Hence, it looks like Transaction 2, although committed, is still waiting for Transaction 1. </p> <p>When Transaction 1 continues, it wants to acquire a Share lock on Transaction 2, and this creates the deadlock. Why does it want to acquire a share lock on Transaction 2? Not too sure about that. <a href="http://www.postgresql.org/docs/current/static/view-pg-locks.html" rel="nofollow">The docs</a> hint that this information is not available in <code>pg_locks</code>. I'm going to guess that this is related to MVCC, but it's still a mystery to me.</p>
    singulars
    1. This table or related slice is empty.
    1. PODeadlock involving foreign key constraint
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USJohn Tseng
    1. USJohn Tseng
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTBountyClose
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload