BUG #8434: Why does dead lock occur many times ?

From: katsumata(dot)tomonari(at)po(dot)ntts(dot)co(dot)jp
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #8434: Why does dead lock occur many times ?
Date: 2013-09-04 09:14:55
Message-ID: E1VH9At-0005VC-Kk@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 8434
Logged by: Tomonari Katsumata
Email address: katsumata(dot)tomonari(at)po(dot)ntts(dot)co(dot)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

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message marta.mihoff 2013-09-04 15:57:09 BUG #8435: PGAdmin backup: obect list missing objects
Previous Message Bruce Momjian 2013-09-03 20:06:36 Re: Re: [BUGS] BUG #7722: extract(epoch from age(...)) appears to be broken