Nhibernate setting query time out period for commands and pessimistic locking
- by Nagesh
I wish to specify a specific command timeout (or LOCK_TIMEOUT) for an SQL and once this time out is reached an exception (or alert) has to be raised in nHibernate.
The following is an example pseudo-code what I have written:
using (var session = sessionFactory.OpenSession()) {
using (var sqlTrans = session.BeginTransaction()) {
ICriteria criteria = session.CreateCriteria(typeof(Foo));
criteria.SetTimeout(5); //Here is the specified command timout, eg: property SqlCommand.CommandTimeout
Foo fooObject = session.Load<Foo>(primaryKeyIntegerValue, LockMode.Force);
session.SaveOrUpdate(fooObject);
sqlTrans.Commit();
}
}
In SQL server we used to achieve this using the following SQL:
BEGIN TRAN
SET LOCK_TIMEOUT 500
SELECT * FROM Foo WITH (UPDLOCK, ROWLOCK) WHERE PrimaryKeyID = 1000001
If PrimaryKeyID row would have locked in other transaction the following error message is being shown by SQL Server:
Msg 1222, Level 16, State 51, Line 3
Lock request time out period exceeded
Similarly I wish to show a lock time out or command time out information using nHibernate. Please help me to achieve this.
Thanks in advance for your help.
