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/
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
