[hibernate-dev] lock timeout support

[Strong Liu]

On Jun 1, 2012, at 7:44 PM, Steve Ebersole wrote:

> 
> On Fri 01 Jun 2012 05:27:12 AM CDT, Strong Liu wrote:
>> Hi there,
>> 
>> I'm working on this org.hibernate.ejb.test.lock.LockTest#testFindWithPessimisticWriteLockTimeoutException test, it failed on lots of DBs
>> 
>> for now, I found:
>> 
>> sql server : supports nowait, but not other lock timeout value
>> DB2 : doesn't support either
>> sybase: doesn't support either
>> 
>> by "doesn't support" I mean, can't set this timeout from sql statement level, but they do support set it as a DB global config.
> 
> Sybase at least supports it as a connection-level setting as well.  Do the rest?  The idea being that we could set and reset that connection setting as the means to specify lock timeout.  Practically though that means they would also have to expose a way to read the current setting value so that we knew what to reset to.

does this useful?

when using connection pool or datasource, there is not a way to set the connection level settings for each query, is it?

> 
>> 
>> so, I'm wondering if we could choose this way:
>> 
>> 1. if DB supports both, then good
>> 2. if DB supports nowait only, and lock timeout is set to nowait, then good
>> 2.1 if lock timeout is set to 5 milliseconds, for example, then we setQueryTimeout(1) // (locktimeout +500)/1000+1
> 
> I think with your 2.1 we just need to be careful when the user has specified a query timeout already.
> 

correct

>> 3. if DB doesn't supports either, then same as 2.1, use java.sql.Statement#setQueryTimeout
> 
> Set to what?  Just some really low number?

(lock timeout + 500) / 1000 + 1

since 0 in timeout means no wait, but in setQueryTimeout(), it means no limits 

> 
> 
>> does this acceptable? or we just ignore lock timeout if the underlying Db doesn't support it?
> 
> The only possible outcomes if we cannot apply the requested lock timeout in any manner is to (a) error or (b) ignore.  Ignoring makes lock timeout more of a hint than a requirement.  Thats the fundamental question we need to answer here.   Don't know the answer, just re-stating the question.

it is a hint as defined in JPA spec, so if we choose this way, I would suggest (b)

> 
> --
> steve at hibernate.org
> http://hibernate.org

-------------------------
Best Regards,

Strong Liu <stliu at hibernate.org>
http://about.me/stliu/bio