The following bug has been logged on the website:
Bug reference: 8434
Logged by: Tomonari Katsumata
Email address: [email protected]
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
--
Sent via pgsql-bugs mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
