Hibernate version: 1.0.4
Name and version of the database you are using: SQL Server 2005
Hi,
I understand that Locking isn't working for SQL server 2005 and 1.0.4, so I was trying t work around it by setting the isolationlevel to RepeatableRead.
Basically, the effect that I want to achieve is that I want to "lock" an object from when it is read untill it is saved so that an seperate session cannot update this at the same time, therefore to avoid stale object exceptions.
I have the following test code:
Code:
static void Main(string[] args)
{
Thread t = new Thread(new ThreadStart(SecondThread));
ISession session = SessionFactory.Instance.GetSession("TestConsole 1");
Console.WriteLine("Start transaction");
session.BeginTransaction(IsolationLevel.RepeatableRead);
MyObject b = (MyObject)session.Load(typeof(MyObject), 12);
t.Start();
Console.WriteLine("Sleeping ...");
Thread.Sleep(2000);
Console.WriteLine("Finished Sleeping");
b.MyProperty--;
session.SaveOrUpdate(b);
Console.WriteLine("Commiting transaction 1");
try
{
session.Transaction.Commit();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
Console.WriteLine("Commited transaction 1");
if (!t.Join(60000))
{
t.Interrupt();
t.Join();
}
Console.WriteLine("End");
Console.ReadLine();
}
static void SecondThread()
{
Console.WriteLine("Start transaction 2");
ISession session = SessionFactory.Instance.GetSession("TestConsole 2");
session.BeginTransaction(IsolationLevel.RepeatableRead);
MyObjectb = (MyObject)session.Load(typeof(MyObject), 12);
b.MyProperty++;
session.SaveOrUpdate(b);
Console.WriteLine("Commiting transaction 2");
try
{
session.Transaction.Commit();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
Console.WriteLine("Commited transaction 2");
}
So basically, I try to emulate the followng scenario:
- Thread A is loading an object (id 12) because it needs to do some updating.
- Thread B is planning to do the same
- I want thread B to wait till thread A is finished.
I thought the transaction Isolation level would do this as it would hold a read and write locks until the end of the transaction.
However, when I try this, I get an NHiberante.ADOException because it has created a deadlock.
"Transaction (Process ID 51) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction."
Does anybody have any suggestions?
Thanks,
Dries.