Deadlocks

It is possible to create an unrecoverable lock condition, called a deadlock. Table  illustrates how two transactions might become deadlocked.

 

Table: Deadlock

Transaction 1	Transaction 2	Description
BEGIN WORK	BEGIN WORK	start both transactions
UPDATE row 64	UPDATE row 83	independent rows write-locked
UPDATE row 83		holds waiting for transaction 2 to release write lock
	UPDATE row 64	attempt to get write lock held by transaction 1
	auto-ROLLBACK WORK	deadlock detected--transaction 2 is rolled back
COMMIT WORK		transaction 1 returns from UPDATE and commits

In this example, each transaction holds a lock and is waiting for the other transaction's lock to be released. POSTGRESQL must roll back one transaction because otherwise the two transactions will wait forever. Obviously, if they had acquired locks in the same order, no deadlock would occur.