Sense-checking my locking strategy

emarthinsen · **Joined:** Thu Mar 09, 2006 12:31 am **Posts:** 8

Hello-

I have an app that assigns tasks to a staff member. I'm using NHiberate in my DAL and MySql 5 for the database. What I want to do is select an available task, assign it to a staff member and commit, without the possibility of the task being assigned to two staff members. Here's some rough code that approximates what I want to do.

Code:

ITransaction tx = session.BeginTransaction();

Task task = (Task)session.CreateQuery("FROM tasks t WHERE StaffMember IS NULL ORDER BY t.Created DESC").SetMaxResults(1).UniqueResult();

task.StaffMember = availableStaffMember;

tx.Commit();

To prevent another query from selecting the same Task, I was thinking about using pessimistic locking, by changing the query line above to this:

Code:

IQuery query = _session.CreateQuery("FROM tasks t WHERE StaffMember IS NULL ORDER BY t.Created DESC").SetMaxResults(1);

query.SetLockMode("myKey", LockMode.Upgrade);

Task task = query.UniqueResult<Task>();

The Tasks table is a small table that just holds unassigned tasks.

Two questions. First, is this the best way to achieve my goal (and if not, what would you recommend)? Two, does anyone see any potential performance issues with the approach?

Regards-
Eric

emarthinsen · **Joined:** Thu Mar 09, 2006 12:31 am **Posts:** 8

FYI, this doesn't work. I can still get situations where the last write wins. Does anyone have a good idea on what would be a better way to go about this?

merge_s.rottem · **Joined:** Tue Aug 23, 2005 5:52 am **Posts:** 335

Use optimistic concurrency and versioning. Read about the <version> and <timestamp> mapping tags in the documentation for further information.

Cheers,

Symon.