Does Derby support SQL hints, so I can tell it to NOLOCK on the SELECT?


From: T K <sanokistoka@yahoo.com>
To: Derby Discussion <derby-user@db.apache.org>
Sent: Friday, September 4, 2009 8:57:18 PM
Subject: Re: Can someone explain this deadlock?

I suspect this is a case of this http://mail-archives.apache.org/mod_mbox/db-derby-user/200811.mbox/%3C770100.66392.qm@web31804.mail.mud.yahoo.com%3E
Can someone confirm? Any remedies? Will dirty reads help in this case?

Thanks


From: Dag H. Wanvik <Dag.Wanvik@Sun.COM>
To: Derby Discussion <derby-user@db.apache.org>
Sent: Friday, September 4, 2009 6:42:11 PM
Subject: Re: Can someone explain this deadlock?

T K <sanokistoka@yahoo.com> writes:

> Derby 10.3
>
> Caused by: java.sql.SQLTransactionRollbackException: A lock could not be obtained due to a deadlock, cycle of locks and waiters is:
> Lock : ROW, SOMETABLE, (217,6)
>  Waiting XID : {216333, S} , SOMESCHEMA, select something from SOMETABLE where somethingElse = ?
>  Granted XID : {216146, X}
> Lock : ROW, SOMETABLE, (4,1)
>  Waiting XID : {216146, S} , SOMESCHEMA, update SOMETABLE  set something = ? where somethingElse = ?
>  Granted XID : {216190, S} , {216333, S}
> . The selected victim is XID : 216333.

Hmm, it looks as if transaction 216146 is asking for a shared lock on (4,1), and
there are only shared locks on that row apparently, so I can't see why
that should not be granted, weird. Could this be an error, I wonder?