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.
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@xxxxxxxxxxxxxx
