NHibernate transaction and race condition

Question

I've got an ASP.NET app using NHibernate to transactionally update a few tables upon a user action. There is a date range involved whereby only one entry to a table 'Booking' can be made such that exclusive dates are specified.

My problem is how to prevent a race condition whereby two user actions occur almost simultaneously and cause mutliple entries into 'Booking' for >1 date. I can't check just prior to calling .Commit() because I think that will still leave be with a race condition?

All I can see is to do a check AFTER the commit and roll the change back manually, but that leaves me with a very bad taste in my mouth! :)

booking_ref (INT) PRIMARY_KEY AUTOINCREMENT

booking_start (DATETIME)

booking_end (DATETIME)

Answer 1

Why does it matter that you have multiple entries with the same date?

Answer 2

Hey. You can either

• make the isolation level of your transaction SERIALIZABLE (session.BeginTransaction(IsolationLevel.Serializable) and check and insert in the same transaction. You should not in general set the isolationlevel to serializable, just in situations like this.

or

• lock the table before you check and eventually insert. You can do this by firing a SQL query through nhibernate:

  session.CreateSQLQuery("SELECT null as dummy FROM Booking WITH (tablockx, holdlock)").AddScalar("dummy", NHibernateUtil.Int32); This will lock only that table for selects / inserts for the duration of that transaction.

Hope it helped