Re: Table modifications with dependent views - best practices?

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?

A simple example would help me understand this.

Thanks!

-tfo

--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC

Strategic Open Source: Open Your i™

http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005

On Apr 22, 2005, at 6:11 AM, Michael Fuhr wrote:

> On Fri, Apr 22, 2005 at 11:34:29AM +0100, David Roussel wrote:
>>
>>> I usually put DDL statements in a transaction, for a couple of
>>> reasons: so that a mistake doesn't leave me with half-done work
>>> (any error will cause the entire transaction to roll back), and to
>>> make the changes atomic for the benefit of other transactions.
>>
>> Can you do that in postgres?  Will it really make the DDL atomic?
>
> Yes, although locking will probably prevent concurrent access and
> can cause deadlock.  DDL statements like DROP, CREATE, and ALTER
> acquire an AccessExclusiveLock on the objects they're modifying,
> so the transaction doing the DDL will block until no other transactions
> hold locks on those objects, and other transactions' attempts to
> use those objects will block until the DDL transaction commits or
> rolls back.  If the DDL transaction rolls back, then nobody else
> will ever have seen the changes; if it commits then the changes all
> become visible at the same time.
>
> Try it and see what happens.  You might see blocking and you might
> be able to cause deadlock, but you shouldn't ever see some changes
> but not others.