On Sat, Apr 23, 2005 at 10:36:00AM -0500, Thomas F.O'Connell wrote: > > Why would DDL statements in a transaction cause deadlocks? I understand > the prevention of concurrent access, but I'm curious to know more about > how deadlocks arise in this situation, as this is something I've seen > in a production environment during transactional DDL traffic. Why would > DDL statements be more likely to cause lock acquisition at cross > purposes?
Locks are held until transaction end, as can be observed by querying pg_locks. DDL statements typically acquire an AccessExclusiveLock, which conflicts with all other lock types. With those in mind, consider the following example: Setup: CREATE TABLE foo (a integer); CREATE TABLE bar (x integer); Transactions: T1: BEGIN; T2: BEGIN; T1: SELECT * FROM foo; T2: ALTER TABLE bar ADD COLUMN y integer; T1 now has an AccessShareLock on foo, and T2 has an AccessExclusiveLock on bar. T1: SELECT * FROM bar; T2: ALTER TABLE foo ADD COLUMN b integer; T1's SELECT blocks because it wants a lock that conflicts with T2's lock on bar, and T2's ALTER blocks because it wants a lock that conflicts with T1's lock on foo. Both transactions are waiting for the other to release a lock, so we get deadlock. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
