Обсуждение: deadlock
Hello.
I've encountered deadlock on postgresql 8.1. Here is the simple example.
create table t1 (
id1 integer primary key,
val1 integer
);
create table t2 (
id2 integer primary key,
id1 integer references t1 on delete cascade,
val1 integer,
val2 integer,
val3 integer
);
insert into t1 select
generate_series(1,10) as id1;
insert into t2 select
generate_series(1,100) as id2,
generate_series(1,10) as id1;
Then three concurrent transaction start.
/*1*/ begin;
/*1*/ update t2 set val1=1 where id2=50;
/*1*/ update t2 set val2=2 where id2=50;
/*2*/ begin;
/*2*/ update t2 set val1=1 where id2=40;
/*2*/ update t2 set val2=2 where id2=40;
/*2*/ commit;
/*3*/ begin;
/*3*/ update t1 set val1=1 where id1=10;
/*1*/ update t2 set val3=3 where id2=50;
Here we have deadlock for transactions 1 and 3.
Is it bug? And if so, will it be fixed?
Or is it not a bug? What should I do then?
Thank you. Sorry for bad english.
--
Alexey A. Nalbat
Price Express
http://www.price.ru/
http://www.tyndex.ru/
Alexey Nalbat <nalbat@price.ru> writes:
> I've encountered deadlock on postgresql 8.1. Here is the simple example.
Your example doesn't deadlock for me ...
regards, tom lane
On Mon, 2007-04-09 at 04:24, Alexey Nalbat wrote: > Hello. > > I've encountered deadlock on postgresql 8.1. Here is the simple example. > > create table t1 ( > id1 integer primary key, > val1 integer > ); > create table t2 ( > id2 integer primary key, > id1 integer references t1 on delete cascade, > val1 integer, > val2 integer, > val3 integer > ); > insert into t1 select > generate_series(1,10) as id1; > insert into t2 select > generate_series(1,100) as id2, > generate_series(1,10) as id1; > > Then three concurrent transaction start. > > /*1*/ begin; > /*1*/ update t2 set val1=1 where id2=50; > /*1*/ update t2 set val2=2 where id2=50; > /*2*/ begin; > /*2*/ update t2 set val1=1 where id2=40; > /*2*/ update t2 set val2=2 where id2=40; > /*2*/ commit; > /*3*/ begin; > /*3*/ update t1 set val1=1 where id1=10; > /*1*/ update t2 set val3=3 where id2=50; > > Here we have deadlock for transactions 1 and 3. That's not a deadlock, transaction 3 is simply waiting for transaction 1 to commit or rollback. If you run a commit or rollback on transaction 1 then transaction 3 will then be ready to commit or rollback as needed.
Alexey Nalbat <nalbat@price.ru> writes:
> Tom Lane wrote:
>> Your example doesn't deadlock for me ...
> With default value "deadlock_timeout=1000" error raises in first transaction:
Then there's a typo in your example --- please recheck it.
regards, tom lane
Tom Lane wrote:
>
> Your example doesn't deadlock for me ...
Scott Marlowe wrote:
>
> That's not a deadlock, transaction 3 is simply waiting for transaction 1
> to commit or rollback.
>
> If you run a commit or rollback on transaction 1 then transaction 3 will
> then be ready to commit or rollback as needed.
With default value "deadlock_timeout=1000" error raises in first transaction:
ERROR: deadlock detected
DETAIL: Process 31712 waits for ShareLock on tuple (0,10) of relation 451542 of database 391598; blocked by process
31786.
Process 31786 waits for ShareLock on transaction 918858; blocked by process 31712.
CONTEXT: SQL statement "SELECT 1 FROM ONLY "public"."t1" x WHERE "id1" = $1 FOR SHARE OF x"
After setting "deadlock_timeout=3600000" we can see deadlock in pg_locks:
datid | datname | procpid | usesysid | usename | current_query | query_start
| backend_start | client_addr | client_port
--------+---------+---------+----------+---------+------------------------------------------+-------------------------------+-------------------------------+-------------+-------------
391598 | nalbat | 32025 | 16384 | nalbat | /*1*/ update t2 set val3=3 where id2=50; | 2007-04-13
11:14:06.966372+04| 2007-04-13 11:13:11.018896+04 | | -1
391598 | nalbat | 32029 | 16384 | nalbat | /*3*/ update t1 set val1=1 where id1=10; | 2007-04-13
11:13:58.607838+04| 2007-04-13 11:13:17.212922+04 | | -1
locktype | database | relation | page | tuple | transactionid | classid | objid | objsubid | transaction | pid
| mode | granted
---------------+----------+----------+------+-------+---------------+---------+-------+----------+-------------+-------+------------------+---------
tuple | 391598 | 451542 | 0 | 10 | | | | | 918867 | 32025
|ShareLock | f
tuple | 391598 | 451542 | 0 | 10 | | | | | 918869 | 32029
|ExclusiveLock | t
relation | 391598 | 451542 | | | | | | | 918867 | 32025
|AccessShareLock | t
relation | 391598 | 451542 | | | | | | | 918867 | 32025
|RowShareLock | t
relation | 391598 | 451542 | | | | | | | 918869 | 32029
|AccessShareLock | t
relation | 391598 | 451542 | | | | | | | 918869 | 32029
|RowExclusiveLock | t
relation | 391598 | 451544 | | | | | | | 918867 | 32025
|AccessShareLock | t
relation | 391598 | 451544 | | | | | | | 918869 | 32029
|AccessShareLock | t
relation | 391598 | 451544 | | | | | | | 918869 | 32029
|RowExclusiveLock | t
relation | 391598 | 451546 | | | | | | | 918867 | 32025
|AccessShareLock | t
relation | 391598 | 451546 | | | | | | | 918867 | 32025
|RowExclusiveLock | t
relation | 391598 | 451548 | | | | | | | 918867 | 32025
|AccessShareLock | t
relation | 391598 | 451548 | | | | | | | 918867 | 32025
|RowExclusiveLock | t
transactionid | | | | | 918867 | | | | 918867 | 32025
|ExclusiveLock | t
transactionid | | | | | 918869 | | | | 918869 | 32029
|ExclusiveLock | t
transactionid | | | | | 918867 | | | | 918869 | 32029
|ShareLock | f
relname | relnamespace | reltype | relowner | relam | relfilenode | reltablespace | relpages | reltuples |
reltoastrelid| reltoastidxid | relhasindex | relisshared | relkind | relnatts | relchecks | reltriggers | relukeys |
relfkeys| relrefs | relhasoids | relhaspkey | relhasrules | relhassubclass | relacl
---------+--------------+---------+----------+-------+-------------+---------------+----------+-----------+---------------+---------------+-------------+-------------+---------+----------+-----------+-------------+----------+----------+---------+------------+------------+-------------+----------------+--------
t1 | 2200 | 451543 | 16384 | 0 | 451542 | 0 | 0 | 0 |
0| 0 | t | f | r | 2 | 0 | 2 | 0 | 0 |
0 | f | t | f | f |
t1_pkey | 2200 | 0 | 16384 | 403 | 451544 | 0 | 1 | 0 |
0| 0 | f | f | i | 1 | 0 | 0 | 0 | 0 |
0 | f | f | f | f |
t2 | 2200 | 451547 | 16384 | 0 | 451546 | 0 | 0 | 0 |
0| 0 | t | f | r | 5 | 0 | 2 | 0 | 0 |
0 | f | t | f | f |
t2_pkey | 2200 | 0 | 16384 | 403 | 451548 | 0 | 1 | 0 |
0| 0 | f | f | i | 1 | 0 | 0 | 0 | 0 |
0 | f | f | f | f |
I have installed PostgreSql 8.1.4 on SUSE Linux 10.1.
version
-------------------------------------------------------------------------------------
PostgreSQL 8.1.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.0 (SUSE Linux)
$ cat /etc/SuSE-release
SUSE LINUX 10.1 (i586)
VERSION = 10.1
$ uname -a
Linux geryon 2.6.16.21-0.25-default #1 Tue Sep 19 07:26:15 UTC 2006 i686 athlon i386 GNU/Linux
--
Alexey A. Nalbat
Price Express
http://www.price.ru/
http://www.tyndex.ru/
I've asked russian postgresql users to proceed my deadlock example. http://sql.ru/forum/actualthread.aspx?tid=418296 Here is the results. OS, version, build PG version, build Status (Person) SUSE Linux 10.1 8.1.4 from official SUSE distribution deadlock between /*1*/ and /*3*/ detected (Nalbat) SUSE Linux 10.1 8.2.3 self-made package /*3*/ waits for /*1*/, deadlock not detected (Nalbat) itanium2(Debian) 8.2.3 deadlock not detected (SergeyK) laptop(Slackware) 8.2.3 deadlock not detected (SergeyK) SUSE 10.0 8.1.4 self-made deadlock detected (SergeyK) Windows 2000 Pro Rus 8.0.4 /*2*/ waits, deadlock not detected (ZemA) Windows 2000 Pro Rus 8.1.3 deadlock detected (ZemA) win xp sp2 8.2.0, i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2 (mingw-special) /*3*/ waits for /*1*/, deadlock not detected (st_serg) It seems that deadlock rises for Postgresql 8.1 at any operating system. Tom Lane wrote: > Alexey Nalbat <nalbat@price.ru> writes: > > Tom Lane wrote: > >> Your example doesn't deadlock for me ... > > > With default value "deadlock_timeout=1000" error raises in first transaction: > > Then there's a typo in your example --- please recheck it. -- Alexey A. Nalbat Price Express http://www.price.ru/ http://www.tyndex.ru/