Hi,
Thanks to your info, otsuka-san.
It seems to be related to some changes in
the [Improve concurrency of foreign key locking] commit(*).
(*) commitid : 0ac5ad5134f2769ccbaefec73844f8504c4d6182
Because I could not reproduce "dead lock" before the commit.
And I'm thinking that also this commit is related to BUG#8470.
If so, I hope Alvaro will find the solution for this problem.
Maybe I can't take enough time, but I'll investigate this continuously.
If someone could reproduce this, please share information.
regards,
---
NTT Software Corporation
Tomonari Katsumata
2013/9/19 OTSUKA Kenji
> 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,
>
>
>
> 2013/9/4
>
> The following bug has been logged on the website:
>>
>> Bug reference: 8434
>> Logged by: Tomonari Katsumata
>> Email address: katsumata.tomon...@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: 0: 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 =
>
