Обсуждение: Blocking Issue
<p><font face="Courier New" size="1">Hi, </font><br /><font face="Courier New" size="1">I have create the following tables:</font><br /><font face="Courier New" size="1">1. rnc table</font><br /><font face="Courier New" size="1">CREATE TABLEact_rnc(rnc_id integer NOT NULL PRIMARY KEY, rnc_data BYTEA);</font><br /><font face="Courier New" size="1">2. rncgentable</font><br /><font face="Courier New" size="1">CREATE TABLE act_rncgen(rnc_id integer NOT NULL PRIMARY KEY, rncsubObj_Cntinteger, rncgen_data BYTEA);</font><br /><font face="Courier New" size="1">3. iuo table which has a foreignkey reference to rnc table</font><br /><font face="Courier New" size="1">CREATE TABLE act_iuo(iuo_id integer NOT NULLprimary key, rnc_id integer NOT NULL, iuo_data BYTEA, FOREIGN KEY(rnc_id) references act_rnc(rnc_id) on delete cascade);</font><p><fontface="Courier New" size="1">Now i open two transactions (separate session with psql). In the firsttransaction I give the following sql sequence: </font><br /><font face="Courier New" size="1">begin; </font><br /><fontface="Courier New" size="1">update act_rnc set rnc_data='rnc_data' where rnc_id=1;</font><p><font face="Courier New"size="1">The transaction will be open. </font><p><font face="Courier New" size="1">In a second transaction i give thefollowing sql sequence: </font><br /><font face="Courier New" size="1">begin; </font><br /><font face="Courier New" size="1">insertinto act_iuo values (1,1,'iuo_data');</font><p><font face="Courier New" size="1">--> now the second transactionis blocked. I work with PostgreSQL 9.0. </font><p><font face="Courier New" size="1">Some outputs: </font><br /><fontface="Courier New" size="1">select * from pg_locks;</font><br /><font face="Courier New" size="1"> locktype |database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted</font><p><font face="Courier New" size="1">---------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+------------------+---------</font><p><font face="CourierNew" size="1"> tuple | 16385 | 16427 | 0 | 8 | | | | | | 3/80 | 9230 | ShareLock | t</font><p><font face="Courier New" size="1"> relation | 16385 | 10985 | | | | | | | | 4/247 | 16535 | AccessShareLock | t</font><p><font face="Courier New" size="1"> virtualxid | | | | | 4/247 | | | | | 4/247 | 16535 | ExclusiveLock | t</font><p><font face="Courier New" size="1"> relation | 16385 | 16443 | | | | | | | | 3/80 | 9230 | RowExclusiveLock | t</font><p><fontface="Courier New" size="1"> transactionid | | | | | | 584 | | | | 3/80 | 9230 | ExclusiveLock | t</font><p><font face="CourierNew" size="1"> virtualxid | | | | | 3/80 | | | | | 3/80 | 9230 | ExclusiveLock | t</font><p><font face="Courier New" size="1"> relation | 16385 | 16433 | | | | | | | | 3/80 | 9230 | AccessShareLock | t</font><p><font face="Courier New" size="1"> relation | 16385 | 16427 | | | | | | | | 5/535 | 2814 | RowExclusiveLock| t</font><p><font face="Courier New" size="1"> virtualxid | | | | | 5/535 | | | | | 5/535 | 2814 | ExclusiveLock | t</font><p><fontface="Courier New" size="1"> transactionid | | | | | | 583 | | | | 5/535 | 2814 | ExclusiveLock | t</font><p><font face="CourierNew" size="1"> relation | 16385 | 16449 | | | | | | | | 3/80 | 9230 | RowExclusiveLock | t</font><p><font face="Courier New" size="1"> relation | 16385 | 16427 | | | | | | | | 3/80 | 9230 | RowShareLock | t</font><p><font face="Courier New" size="1"> transactionid | | | | | | 583 | | | | 3/80 | 9230 | ShareLock | f</font><p><font face="Courier New" size="1"> relation | 16385 | 16433 | | | | | | | | 5/535 | 2814 | RowExclusiveLock | t</font><p><fontface="Courier New" size="1">(14 rows)</font><p><font face="Courier New" size="1">select relname, pg_class.oidfrom pg_class;</font><br /><font face="Courier New" size="1"> act_rnc_pkey | 16433</font><br/><font face="Courier New" size="1"> pg_inherits_parent_index | 2187</font><br /><font face="CourierNew" size="1"> pg_inherits_relid_seqno_index | 2680</font><br /><font face="Courier New" size="1"> pg_toast_16435 | 16438</font><br /><font face="Courier New" size="1"> pg_trigger_oid_index | 2702</font><br /><font face="Courier New" size="1"> pg_toast_16435_index | 16440</font><br /><font face="Courier New" size="1"> act_rncgen | 16435</font><br /><font face="Courier New" size="1"> act_rncgen_pkey | 16441</font><br /><font face="Courier New" size="1"> pg_toast_16443 | 16446</font><br /><font face="Courier New" size="1"> pg_toast_16443_index | 16448</font><br /><font face="Courier New" size="1"> act_iuo_pkey | 16449</font><br /><font face="Courier New" size="1"> pg_amop | 2602</font><br /><font face="Courier New" size="1"> act_iuo | 16443</font><br /><font face="Courier New" size="1"> pg_largeobject | 2613</font><br /><font face="Courier New" size="1"> act_rnc | 16427</font><br /><font face="Courier New" size="1"> pg_toast_11361 | 11363</font><br /><font face="Courier New" size="1"> pg_toast_11361_index | 11365</font><br /><font face="Courier New" size="1"> pg_toast_11366_index | 11370</font><p><font face="Courier New" size="1">I assume that the accessto act_rnc_pkey causes the blocking, however why? Or how I can resolve the blocking (commit one transaction solvesthe problem, but should Postgres not recognize the blocking situation and release one transaction?). Is this an errorin Postgres?</font><p><font face="Courier New" size="1">Best Regards</font><br /><font face="Courier New" size="1">IngoSander</font><p><i><font color="#000000" face="Brush Script MT" size="4">Best Regards/mfG</font></i><br /><i><b><fontcolor="#FF0000" face="Brush Script MT" size="6">Ingo Sander</font></b></i><br /><font face="Times New Roman"size="2">=========================================================</font><br /><font face="Arial Black" size="2">NokiaSiemens Networks GmbH &Co. KG</font><br /><span lang="en-us"><font face="Arial" size="2">NWS EP</font></span><spanlang="de"> <font face="Arial" size="2">I&V Platf Technical Service DE</font><br /></span><span lang="en-us"></span><br/><span lang="en-us"><font face="Courier New" size="2">St.-Martin-Str. 76</font></span><br /><spanlang="en-us"><font face="Courier New" size="2">D-81541 München</font></span><br /><span lang="de"><font color="#008080"face="Wingdings" size="5">(</font><font face="Tahoma" size="2">Tel.: +49-89-515938390</font></span><br /><spanlang="de"><font color="#008080" face="Wingdings" size="5">+</font><u></u><u></u><u><font color="#0000FF" face="Tahoma"size="2">ingo.sander@nsn.com</font></u></span><p><span lang="de"><font color="#808080" face="Arial" size="1">NokiaSiemens Networks GmbH & Co. KG</font><font face="Times New Roman" size="1"><br /></font><font color="#808080"face="Arial" size="1">Sitz der Gesellschaft: München / Registered office: Munich</font><font face="Times NewRoman" size="1"><br /></font><font color="#808080" face="Arial" size="1">Registergericht: München / Commercial registry:Munich, HRA 88537</font><font face="Times New Roman" size="1"><br /></font><font color="#808080" face="Arial" size="1">WEEE-Reg.-Nr.:DE 52984304</font><font face="Times New Roman" size="1"><br /></font><font color="#808080" face="Arial"size="1">Persönlich haftende Gesellschafterin / General Partner: Nokia Siemens Networks Management GmbH</font><fontface="Times New Roman" size="1"><br /></font><font color="#808080" face="Arial" size="1">Geschäftsleitung/ Board of Directors: Dr. Hermann Rodler, Lydia Sommer, Olaf Horsthemke</font><font face="TimesNew Roman" size="1"><br /></font><font color="#808080" face="Arial" size="1">Vorsitzender des Aufsichtsrats /Chairman of supervisory board: Herbert Merz</font><font face="Times New Roman" size="1"><br /></font><font color="#808080"face="Arial" size="1">Sitz der Gesellschaft: München / Registered office: Munich</font><font face="Times NewRoman" size="1"><br /></font><font color="#808080" face="Arial" size="1">Registergericht: München / Commercial registry:Munich, HRB 163416</font><font face="Times New Roman" size="1"> </font></span><br /><br /><br /><br /><br /><br/><br />
On 09.02.2011 12:15, Sander, Ingo (NSN - DE/Munich) wrote: > Hi, > 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 onetransaction solves the problem, but should Postgres not recognize the blocking situation and release one transaction?).Is this an error in Postgres? The foreign key causes the blocking. PostgreSQL doesn't make a distinction on which columns are updated, as far as locking is concerned. If the update was "update act_rnc set rnc_id=2 where rnc_id=1", the insert would have to block to see if the update commits or not - if it commits the insert would violate the foreign key and needs to be aborted, but if it aborts the insert can succeed. With your original example, the insert could go ahead in either case without violating the foreign key, since the update doesn't change rnc_id field, but PostgreSQL doesn't pay attention to that detail. There's actually a patch in the current commitfest, awaiting review, to address exactly that scenario. See https://commitfest.postgresql.org/action/patch_view?id=502 and http://archives.postgresql.org/message-id/1294953201-sup-2099@alvh.no-ip.org. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com