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
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 |