#Postgres deadlock how to#

Because this is hold only by the transaction (upstream transaction) waiting for a remote transaction (downstream transaction) invoked by this upstream transaction, no other transactions in the database where upstream transaction is running do not care about this lock. Here, we define new locktag type, LOCKTAG_EXTERNAL in lock.h, to represent that the transaction acquing this lock is waiting for a remote transaction. We can extended this to represent status of transactions waiting for completion of remote transactions. This is used to chase wait-for-graph when a transaction fails to acquire a lock within the timeframe defined by deadlock_timeout GUC parameter, implemented in deadlock.c. PostgreSQL itself represents object-level lock with various internal functions mainly in lock.c.

#Postgres deadlock how to#

How to represent wait-for-graph including remote transactions Then we can produce global deadlock as follows: Both server has table t1 (c int).įrom server1, server2's table t is seen as svr2_t and from server2, server1's table t is seen as svr1_t. Suppose we have two servers: server1 and server2. We can produce global deadlock very easily with existing FDW.

This works to detect deadloc caused by remote transactions, by wait-for-graph cycle formed with chain of remote transactins. This article desribes how to extend PostgreSQL's locks system to represent wait-for-graph including transactions running at remote database server by many means, for example, FDW, libpq and dblink. 1.3 How to represent wait-for-graph including remote transactionsĭistributed deadlock detection Purpose of this project.