Re: [HACKERS] Possible deadlock issue when one transaction waiting on other and vice versa? Should, ideally, postgres recognize blocking situation?
On Tue, Apr 26, 2011 at 2:45 AM, Prakash Itnal 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Possible deadlock issue when one transaction waiting on other and vice versa? Should, ideally, postgres recognize blocking situation?
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Possible deadlock issue when one transaction waiting on other and vice versa? Should, ideally, postgres recognize blocking situation?
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. Some outputs: select * from pg_locks; locktype| database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted ---+--+--+--+---++---+-+---+--++---+--+- tuple |16385 |16427 |0 | 8 | | | | | | 3/80 | 9230 | ShareLock| t relation |16385 |10985 | | | | | | | | 4/247 | 16535 | AccessShareLock | t virtualxid| | | | | 4/247 | | | | | 4/247 | 16535 | ExclusiveLock| t relation |16385 |16443 | | | | | | | | 3/80 | 9230 | RowExclusiveLock | t transactionid | | | | || 584 | | | | 3/80 | 9230 | ExclusiveLock| t virtualxid| | | | | 3/80 | | | | | 3/80 | 9230 | ExclusiveLock| t relation |16385 |16433 | | | | | | | | 3/80 | 9230 | AccessShareLock | t relation |16385 |16427 | | | | | | | | 5/535 | 2814 | RowExclusiveLock | t virtualxid| | | | | 5/535 | | | | | 5/535 | 2814 | ExclusiveLock| t transactionid | | | | || 583 | | | | 5/535 | 2814 | ExclusiveLock| t relation |16385 |16449 | | | | | | | | 3/80 | 9230 | RowExclusiveLock | t relation |16385 |16427 | | | | | | | | 3/80 | 9230 | RowShareLock | t transactionid | | | | || 583 | | | | 3/80 | 9230 | ShareLock| f relation |16385 |16433 | | | | | | | | 5/535 | 2814 | RowExclusiveLock | t (14 rows) select relname, pg_class.oid from pg_class; act_rnc_pkey| 16433 pg_inherits_parent_index| 2187 pg_inherits_relid_seqno_index | 2680 pg_toast_16435 | 16438 pg_trigger_oid_index| 2702 pg_toast_16435_index| 16440 act_rncgen | 16435 act_rncgen_pkey | 16441 pg_toast_16443 | 16446 pg_toast_16443_index| 16448 act_iuo_pkey| 16449 pg_amop | 2602 act_iuo | 16443 pg_largeobject | 2613 act_rnc | 16427 pg_toast_11361 | 11363 pg_toast_11361_index| 11365 pg_toast_11366_index| 11370 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? -- Cheers, Prakash
