deadlock

Started by Alexey Nalbatabout 19 years ago6 messagesgeneral

nalbat@price.ru

about 19 years ago

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/

Tom Lane

tgl@sss.pgh.pa.us

about 19 years ago

In reply to: Alexey Nalbat (#1)

Re: deadlock

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

Scott Marlowe

smarlowe@g2switchworks.com

about 19 years ago

In reply to: Alexey Nalbat (#1)

Re: deadlock

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

about 19 years ago

In reply to: Tom Lane (#2)

Re: deadlock

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/

Tom Lane

tgl@sss.pgh.pa.us

about 19 years ago

In reply to: Alexey Nalbat (#4)

Re: deadlock

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

Alexey Nalbat

nalbat@price.ru

about 19 years ago

In reply to: Tom Lane (#5)

Re: deadlock

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/