Hi Knut,

Thanks. That's what it looked like to me too.  If the two transactions were both trying drop the same column, just curious why they would obtain locks on table XXX and the SYSCONGLOMERATES table in opposite orders, which seems like a recipe for deadlock.

Also, would it make sense to use isolation level TRANSACTION_SERIALIZABLE for performing any modifications to the structure of a table? Would that prevent the deadlock?

----- Original Message ----
From: Knut Anders Hatlen <Knut.Hatlen@Sun.COM>
To: Derby Discussion <derby-user@db.apache.org>
Sent: Friday, September 5, 2008 4:13:06 PM
Subject: Re: deadlock

Geoff hendrey <geoff_hendrey@yahoo.com> writes:

> Any thoughts on why dropping a column gave a deadlock? Also, where can I go to
> understand the syntax of the "Lock" print. For example, what does "IX" mean?
> What does "S" mean? What does (5,15) mean?
> java.sql.SQLTransactionRollbackException: A lock could not be obtained due to
> a deadlock, cycle of
> locks and waiters is:
> Lock : TABLE, XXX, Tablelock
> Granted XID : {176, X}
> Waiting XID : {176, X} , TEST, ALTER TABLE XXX DROP COLUMN "v"
> Granted XID : {176, S} , {178, S}
> . The selected victim is XID : 178.

Hi Geoff,

It looks like you have two transactions trying to drop the same column
at about the same time. That would probably have caused an exception
even if it didn't run into a deadlock.

This is what the error message tells:

One of the transactions (176) has obtained an exclusive table lock on
table XXX, and it has requested an exclusive row lock on row number 15
on page 5 in the SYSCONGLOMERATES table. The other transaction (178) has
obtained a shared lock on the same row in SYSCONGLOMERATES and therefore
blocks transaction 176, and is at the same time trying to obtain an IX
lock on table XXX (which expresses that it intends to lock one or more
of the rows in the table) which means that it is blocked by transaction
176's exclusive table lock on that table.

Knut Anders