Re: Table modifications with dependent views - best practices?

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/