CODEWITHSUNDEEP
sqlsql-servernhibernate
Kieran Benton
Kieran Benton

Reputation: 8900

NHibernate transaction and race condition

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)

Upvotes: 1

Views: 2661

Answers (3)

Emre Tiryaki
Emre Tiryaki

Reputation: 23

The above solutions can be used as an option. If I sent 100 request by using "Parallel.For" while transaction level is serializable, yess there is no duplicated reqeust id but 25 transaction is failed. It is not acceptable for my client. So we fixed the problem with only storing request id and adding an unique index on other table as temp.

Upvotes: 0

Kevin Up
Kevin Up

Reputation: 801

Your database should manage your data integrity.

You could make your 'date' column unique. Therefore, if 2 threads try to get the same date. One will throw a unique key violation and the other will succeed.

Upvotes: -1

Carsten Hess
Carsten Hess

Reputation:

  • 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

Upvotes: 5

Related Questions