> The second one happens everyone once in a while when I have a
> multi-threaded client running against the database. *Every* time it
> occurs, a SQL SELECT query is being executed (although there are
> potentially other update operations in the transaction). The exception
> message just says "deadlock". I could add instrumentation to see what
> happened during the failed transaction, but what I would really like to
> find out is, what happened during the conflicting transaction that was
> allowed to succeed.

There are two possibilities for a lock conflict during SELECT:

a) SELECT ... FOR UPDATE WITH LOCK - this construction obtains a lock on the
each record being fetched (note, this is not SQL standart conform).

b) You use READ COMMITTED isolation with NO_REC_VERSION and NOWAIT
transaction parameters. Then, if there is an uncommitted version for the
record being currently fetched, an error is returned.

In all other cases no lock conflict should be reported, but an old record
version should be used.

> So my question is, what is the best way to debug something like this.
> And also, is there any significance to the fact that the text of the
> exception messages is different

I am using P6Spy together with IronTrack SQL, there you can see what
statements were executed, so you can check whethere there can be a conflict
or not.

Roman