Re: BUG #8434: Why does dead lock occur many times ? - Mailing list pgsql-bugs
| From | OTSUKA Kenji |
|---|---|
| Subject | Re: BUG #8434: Why does dead lock occur many times ? |
| Date | |
| Msg-id | CAFU_jja+8iWb-Hf7U_0uPb8p8Bqu1q+zMRJP1eMVbzEDKWWx2w@mail.gmail.com Whole thread Raw |
| In response to | BUG #8434: Why does dead lock occur many times ? (katsumata.tomonari@po.ntts.co.jp) |
| Responses |
Re: BUG #8434: Why does dead lock occur many times ?
|
| List | pgsql-bugs |
Hi,
This issue occurred even on 9.3.0.
I took the following information, and I attached them.
pg_locks.txt ... pg_locks during deadlock
pg_stat_activity.txt ... pg_stat_activity during deadlock
postgresql.log ... PostgreSQL log (including LOCK_DEBUG log)
The OID of the table is 16459.
I run 3 transactions.
One transaction of them executes SELECT FOR UPDATE and UPDATE a row.
And two transactions of them execute only UPDATE the same line.
The results is that all of UPDATE is waiting.
I changed a little bit how to reproduce.
It is as follows.
- Compiling PostgreSQL
Add -DLOCK_DEBUG to CFLAGS
- Changing postgresql.conf following parameters
log_lock_waits = on
deadlock_timeout = 1min # for getting information during deadlock
debug_deadlocks = on
trace_lock_table = 16459
log_line_prefix = '%t [%p] %q(%a) '
logging_collector = on
log_filename = 'postgresql.log'
log_min_messages = info
log_error_verbosity = verbose
- Testing
1. Initializing data
Executing createdb.sh
This creates a table with 2 columns, and insert 1 row.
2. Running the transactions
Executing test.sh
This runs 3 transactions.
2 transactions of them (tx1) are
BEGIN;
UPDATE t SET col2 = 'A' WHERE col1 = 1;
COMMIT;
1 transaction of them (tx2) is
BEGIN;
SELECT * FROM t WHERE col1 = 1 FOR UPDATE;
UPDATE t SET col2 = 'A' WHERE col1 = 1;
COMMIT;
regards,
This issue occurred even on 9.3.0.
I took the following information, and I attached them.
pg_locks.txt ... pg_locks during deadlock
pg_stat_activity.txt ... pg_stat_activity during deadlock
postgresql.log ... PostgreSQL log (including LOCK_DEBUG log)
The OID of the table is 16459.
I run 3 transactions.
One transaction of them executes SELECT FOR UPDATE and UPDATE a row.
And two transactions of them execute only UPDATE the same line.
The results is that all of UPDATE is waiting.
I changed a little bit how to reproduce.
It is as follows.
- Compiling PostgreSQL
Add -DLOCK_DEBUG to CFLAGS
- Changing postgresql.conf following parameters
log_lock_waits = on
deadlock_timeout = 1min # for getting information during deadlock
debug_deadlocks = on
trace_lock_table = 16459
log_line_prefix = '%t [%p] %q(%a) '
logging_collector = on
log_filename = 'postgresql.log'
log_min_messages = info
log_error_verbosity = verbose
- Testing
1. Initializing data
Executing createdb.sh
This creates a table with 2 columns, and insert 1 row.
2. Running the transactions
Executing test.sh
This runs 3 transactions.
2 transactions of them (tx1) are
BEGIN;
UPDATE t SET col2 = 'A' WHERE col1 = 1;
COMMIT;
1 transaction of them (tx2) is
BEGIN;
SELECT * FROM t WHERE col1 = 1 FOR UPDATE;
UPDATE t SET col2 = 'A' WHERE col1 = 1;
COMMIT;
regards,
2013/9/4 <katsumata.tomonari@po.ntts.co.jp>
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
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
--
OTSUKA Kenji
Attachment
pgsql-bugs by date: