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/