Possible deadlock issue when one transaction waiting on other and vice versa? Should, ideally, postgres recognize blocking situation? - Mailing list pgsql-hackers
| From | Prakash Itnal |
|---|---|
| Subject | Possible deadlock issue when one transaction waiting on other and vice versa? Should, ideally, postgres recognize blocking situation? |
| Date | |
| Msg-id | BANLkTik99npZhr4nN70CK2Nf8CciMBJyUA@mail.gmail.com Whole thread Raw |
| Responses |
Re: Possible deadlock issue when one transaction waiting
on other and vice versa? Should, ideally, postgres recognize blocking situation?
Re: Possible deadlock issue when one transaction waiting on other and vice versa? Should, ideally, postgres recognize blocking situation? |
| List | pgsql-hackers |
<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 />
pgsql-hackers by date: