BUG #8434: Why does dead lock occur many times ? - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      8434
Logged by:          Tomonari Katsumata
Email address:      katsumata.tomonari@po.ntts.co.jp
PostgreSQL version: 9.3rc1
Operating system:   RedHatEnterpriseLinux 6.4(x86_64)
Description:

Hi,


I'm testing PostgreSQL 9.3rc1.
Many times updates and selects for update become dead lock situation.


The reproduce is:
1. initializing data
createdb testdb
psql testdb -c "create table t (col1 int, col2 int, col3 text);"
psql testdb -c "insert into t values (1, 4, 'A');"
psql testdb -c "insert into t values (2, 5, 'B');"
psql testdb -c "insert into t values (3, 6, 'C');"


2. executing updates and selects for update
(run below script)
----
#!/bin/sh


./tx1 > /dev/null &
./tx2 > /dev/null &
./tx3 > /dev/null &


wait
----


tx1 is:
----
#!/bin/sh


while :
do
psql testdb << EOF
BEGIN;
UPDATE t SET col3 = 'c' WHERE col1 = 3 AND col2 = 6;
COMMIT;
\q
EOF
done
----


tx2 is:
----
#!/bin/sh


while :
do
psql testdb << EOF
BEGIN;
SELECT col1, col2, col3 FROM t WHERE col1 = 3 AND col2 = 6 FOR UPDATE;
UPDATE t SET col3 = 'c' WHERE col1 = 3 AND col2 = 6;
COMMIT;
\q
EOF
done
----


tx3 is:
----
#!/bin/sh


while :
do
psql testdb << EOF
BEGIN;
UPDATE t SET col3 = 'c' WHERE col1 = 3 AND col2 = 6;
COMMIT;
\q
EOF
done
----


Then, I got below messages.
--------
2013-09-04 15:25:25 JST 29630 5226d254.73be-1 659102 (pgsql, testdb,
[local], psql) LOG:  00000: process 29630 detected deadlock while waiting
for ShareLock on transaction 659103 after 1000.136 ms
2013-09-04 15:25:25 JST 29630 5226d254.73be-2 659102 (pgsql, testdb,
[local], psql) LOCATION:  ProcSleep, proc.c:1232
2013-09-04 15:25:25 JST 29630 5226d254.73be-3 659102 (pgsql, testdb,
[local], psql) STATEMENT:  UPDATE t SET col3 = 'c' WHERE col1 = 3 AND col2 =
6;
2013-09-04 15:25:25 JST 29630 5226d254.73be-4 659102 (pgsql, testdb,
[local], psql) ERROR:  40P01: deadlock detected
2013-09-04 15:25:25 JST 29630 5226d254.73be-5 659102 (pgsql, testdb,
[local], psql) DETAIL:  Process 29630 waits for ShareLock on transaction
659103; blocked by process 29631.
        Process 29631 waits for ExclusiveLock on tuple (0,153) of relation
16385 of database 16384; blocked by process 29630.
        Process 29630: UPDATE t SET col3 = 'c' WHERE col1 = 3 AND col2 = 6;
        Process 29631: UPDATE t SET col3 = 'c' WHERE col1 = 3 AND col2 = 6;
2013-09-04 15:25:25 JST 29630 5226d254.73be-6 659102 (pgsql, testdb,
[local], psql) HINT:  See server log for query details.
2013-09-04 15:25:25 JST 29630 5226d254.73be-7 659102 (pgsql, testdb,
[local], psql) LOCATION:  DeadLockReport, deadlock.c:956
2013-09-04 15:25:25 JST 29630 5226d254.73be-8 659102 (pgsql, testdb,
[local], psql) STATEMENT:  UPDATE t SET col3 = 'c' WHERE col1 = 3 AND col2 =
6;
--------


I did not get these messages with PostgreSQL 9.2.4.


Why did I get the dead lock situation with PostgreSQL9.3rc1?
degrading? or I'm missing something?


regards,
------------
Tomonari Katsumata