Обсуждение: Possible deadlock issue when one transaction waiting on other and vice versa? Should, ideally, postgres recognize blocking situation?

<br clear="all" /><span class="Apple-style-span" style="border-collapse: collapse; "><p style="font-family: arial,
sans-serif;"><font face="Courier New">Hi, </font><p><font class="Apple-style-span" face="'Courier New'"><br
/></font><fontface="Courier New" style="font-family: arial, sans-serif; ">I have create the following
tables: </font><br/><font face="Courier New" style="font-family: arial, sans-serif; ">1. rnc table</font><font
class="Apple-style-span"face="arial, sans-serif"> </font><br /><font face="Courier New" style="font-family: arial,
sans-serif;">CREATE TABLE act_rnc(rnc_id integer NOT NULL PRIMARY KEY, rnc_data BYTEA);</font><font
class="Apple-style-span"face="arial, sans-serif"> </font><br /><font face="Courier New" style="font-family: arial,
sans-serif;">2. rncgen table</font><font class="Apple-style-span" face="arial, sans-serif"> </font><br /><font
face="CourierNew" style="font-family: arial, sans-serif; ">CREATE TABLE act_rncgen(rnc_id integer NOT NULL PRIMARY KEY,
rncsubObj_Cntinteger, rncgen_data BYTEA);</font><font class="Apple-style-span" face="arial, sans-serif"> </font><br
/><fontface="Courier New" style="font-family: arial, sans-serif; ">3. iuo table which has a <span class="il"
style="background-image:initial; background-attachment: initial; background-origin: initial; background-clip: initial;
background-color:rgb(255, 255, 136); color: rgb(34, 34, 34); background-position: initial initial; background-repeat:
initialinitial; ">foreign</span> key reference to rnc table</font><font class="Apple-style-span" face="arial,
sans-serif"> </font><br/><font face="Courier New" style="font-family: arial, sans-serif; ">CREATE TABLE act_iuo(iuo_id
integerNOT NULL primary key, rnc_id integer NOT NULL, iuo_data BYTEA, <span class="il" style="background-image:
initial;background-attachment: initial; background-origin: initial; background-clip: initial; background-color:
rgb(255,255, 136); color: rgb(34, 34, 34); background-position: initial initial; background-repeat: initial initial;
">FOREIGN</span> KEY(rnc_id)references act_rnc(rnc_id) on delete cascade);</font><p style="font-family: arial,
sans-serif;"><font face="Courier New">Now i open two transactions (separate session with psql). In the first
transactionI give the following sql sequence: </font><br /><font face="Courier New">begin; </font><br /><font
face="CourierNew">update act_rnc set rnc_data='rnc_data' where rnc_id=1;</font><p style="font-family: arial,
sans-serif;"><font face="Courier New">The transaction will be open.</font><p style="font-family: arial, sans-serif;
"><fontface="Courier New">In a second transaction i give the following sql sequence: </font><br /><font face="Courier
New">begin; </font><br/><font face="Courier New">insert into act_iuo values (1,1,'iuo_data');</font><p
style="font-family:arial, sans-serif; "><font face="Courier New">--> now the second transaction is blocked. I work
withPostgreSQL 9.0.</font><p style="font-family: arial, sans-serif; "><font face="Courier New">Some outputs: </font><br
/><fontface="Courier New">select * from pg_locks;</font> <br /><font face="Courier New">   locktype    | database |
relation| page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction |  pid  |      
mode      | granted</font><p style="font-family: arial, sans-serif; "><font face="Courier
New">---------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+------------------+---------</font><p
style="font-family:arial, sans-serif; "><font face="Courier New"> tuple         |    16385 |    16427 |    0 |     8
|           |               |         |       |          | 3/80               |  9230 | ShareLock        | t</font><p
style="font-family:arial, sans-serif; "><font face="Courier New"> relation      |    16385 |    10985 |      |      
|           |               |         |       |          | 4/247              | 16535 | AccessShareLock  | t</font><p
style="font-family:arial, sans-serif; "><font face="Courier New"> virtualxid    |          |          |      |       |
4/247     |               |         |       |          | 4/247              | 16535 | ExclusiveLock    | t</font><p
style="font-family:arial, sans-serif; "><font face="Courier New"> relation      |    16385 |    16443 |      |      
|           |               |         |       |          | 3/80               |  9230 | RowExclusiveLock | t</font><p
style="font-family:arial, sans-serif; "><font face="Courier New"> transactionid |          |          |      |      
|           |           584 |         |       |          | 3/80               |  9230 | ExclusiveLock    | t</font><p
style="font-family:arial, sans-serif; "><font face="Courier New"> virtualxid    |          |          |      |       |
3/80      |               |         |       |          | 3/80               |  9230 | ExclusiveLock    | t</font><p
style="font-family:arial, sans-serif; "><font face="Courier New"> relation      |    16385 |    16433 |      |      
|           |               |         |       |          | 3/80               |  9230 | AccessShareLock  | t</font><p
style="font-family:arial, sans-serif; "><font face="Courier New"> relation      |    16385 |    16427 |      |      
|           |               |         |       |          | 5/535              |  2814 | RowExclusiveLock | t</font><p
style="font-family:arial, sans-serif; "><font face="Courier New"> virtualxid    |          |          |      |       |
5/535     |               |         |       |          | 5/535              |  2814 | ExclusiveLock    | t</font><p
style="font-family:arial, sans-serif; "><font face="Courier New"> transactionid |          |          |      |      
|           |           583 |         |       |          | 5/535              |  2814 | ExclusiveLock    | t</font><p
style="font-family:arial, sans-serif; "><font face="Courier New"> relation      |    16385 |    16449 |      |      
|           |               |         |       |          | 3/80               |  9230 | RowExclusiveLock | t</font><p
style="font-family:arial, sans-serif; "><font face="Courier New"> relation      |    16385 |    16427 |      |      
|           |               |         |       |          | 3/80               |  9230 | RowShareLock     | t</font><p
style="font-family:arial, sans-serif; "><font face="Courier New"> transactionid |          |          |      |      
|           |           583 |         |       |          | 3/80               |  9230 | ShareLock        | f</font><p
style="font-family:arial, sans-serif; "><font face="Courier New"> relation      |    16385 |    16433 |      |      
|           |               |         |       |          | 5/535              |  2814 | RowExclusiveLock | t</font><p
style="font-family:arial, sans-serif; "><font face="Courier New">(14 rows)</font><p style="font-family: arial,
sans-serif;"><font face="Courier New">select relname, pg_class.oid from pg_class;</font> <br /><font face="Courier
New"> act_rnc_pkey                           | 16433</font> <br /><font face="Courier
New"> pg_inherits_parent_index               |  2187</font> <br /><font face="Courier
New"> pg_inherits_relid_seqno_index          |  2680</font> <br /><font face="Courier
New"> pg_toast_16435                         | 16438</font> <br /><font face="Courier
New"> pg_trigger_oid_index                   |  2702</font> <br /><font face="Courier
New"> pg_toast_16435_index                   | 16440</font> <br /><font face="Courier
New"> act_rncgen                             | 16435</font> <br /><font face="Courier
New"> act_rncgen_pkey                        | 16441</font> <br /><font face="Courier
New"> pg_toast_16443                         | 16446</font> <br /><font face="Courier
New"> pg_toast_16443_index                   | 16448</font> <br /><font face="Courier
New"> act_iuo_pkey                           | 16449</font> <br /><font face="Courier
New"> pg_amop                                |  2602</font> <br /><font face="Courier
New"> act_iuo                                | 16443</font> <br /><font face="Courier
New"> pg_largeobject                         |  2613</font> <br /><font face="Courier
New"> act_rnc                                | 16427</font> <br /><font face="Courier
New"> pg_toast_11361                         | 11363</font> <br /><font face="Courier
New"> pg_toast_11361_index                   | 11365</font> <br /><font face="Courier
New"> pg_toast_11366_index                   | 11370</font><p style="font-family: arial, sans-serif; "><font
face="CourierNew">I assume that the access to act_rnc_pkey causes the blocking, however why? Or how I can resolve the
blocking(commit one transaction solves the problem, but should Postgres not recognize the blocking situation and
releaseone transaction?). Is this an error in Postgres?</font><p><span class="Apple-style-span" style="border-collapse:
separate;">-- </span></span>Cheers,<br />Prakash<br /> 

On 26.04.2011 09:45, Prakash Itnal wrote:
> I have create the following tables:
> 1. rnc table
> CREATE TABLE act_rnc(rnc_id integer NOT NULL PRIMARY KEY, rnc_data BYTEA);
> 2. rncgen table
> CREATE TABLE act_rncgen(rnc_id integer NOT NULL PRIMARY KEY, rncsubObj_Cnt
> integer, rncgen_data BYTEA);
> 3. iuo table which has a foreign key reference to rnc table
> CREATE TABLE act_iuo(iuo_id integer NOT NULL primary key, rnc_id integer NOT
> NULL, iuo_data BYTEA, FOREIGN KEY(rnc_id) references act_rnc(rnc_id) on
> delete cascade);
>
> Now i open two transactions (separate session with psql). In the first
> transaction I give the following sql sequence:
> begin;
> update act_rnc set rnc_data='rnc_data' where rnc_id=1;
>
> The transaction will be open.
>
> In a second transaction i give the following sql sequence:
> begin;
> insert into act_iuo values (1,1,'iuo_data');
>
> -->  now the second transaction is blocked. I work with PostgreSQL 9.0.
>
>...
>
> I assume that the access to act_rnc_pkey causes the blocking, however why?
> Or how I can resolve the blocking (commit one transaction solves the
> problem, but should Postgres not recognize the blocking situation and
> release one transaction?). Is this an error in Postgres?

No. It's the application's responsibility to commit the first 
transaction. PostgreSQL won't kill your transaction just because it's 
blocking some other transaction.

PostgreSQL does detect deadlocks, but the above is not a deadlock.

--   Heikki Linnakangas  EnterpriseDB   http://www.enterprisedb.com

On Tue, Apr 26, 2011 at 2:45 AM, Prakash Itnal <prakash074@gmail.com> wrote:
> I assume that the access to act_rnc_pkey causes the blocking, however why?
> Or how I can resolve the blocking (commit one transaction solves the
> problem, but should Postgres not recognize the blocking situation and
> release one transaction?). Is this an error in Postgres?

The UPDATE locks the tuple in exclusive mode, which then prevents the
INSERT from obtaining the share lock that it needs to hold until
transaction commit.

Alvaro Herrera is working on something related to this problem:

http://www.commandprompt.com/blogs/alvaro_herrera/2010/11/fixing_foreign_key_deadlocks/
http://www.commandprompt.com/blogs/alvaro_herrera/2010/11/fixing_foreign_key_deadlocks_part_2/

...but I don't think it will actually fix this particular case,
because here the tuple is getting updated before the foreign key
attempts to apply a share-lock.

Off-hand, I'm not sure what to do about that.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company