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/