3

I am using nhibernate in a quite big project.

The system has a settings table which has 10 columns and one row, there is always a row and it should not be deleted only modified. Concurrency can be a problem as many clients can increment columns in the table like LastReceiptId.

I'm thinking, it can't be a good idea to use nhibernate to solve this.

With raw sql I can use a transaction and lock the table with for example: With (TABLOCKX, HOLDLOCK) while I select and update the LastReceiptId.

Do you have any reasons to use or not use nhibernate in a situation like this?

Jakob
  • 213
  • 2
  • 10
  • I'm wondering why you would need a table lock for what appears to be an ordinary UPDATE on a single column of a record. If contention on that single record is currently a problem, there are ways to fix that. – Robert Harvey Aug 18 '15 at 20:20
  • @RobertHarvey well, this is a legacy system imported from an old c-tree database to sql server. No identity inserts were possible so a settings table was created to hold on to the newest identities. So when you need to insert into one of those tables, create a lock, read the newest value, increment by one and commit. To complicate things one identity is a num/char system 0-9A-Z which is a bit complicated to compute. Then the table also has some regluar fields, often read and seldomly updated by one client. – Jakob Aug 18 '15 at 21:51
  • You're right; EF seems like a poor fit. – Robert Harvey Aug 18 '15 at 23:02

2 Answers2

2

Your post gives me the (maybe false?) impression that you are questioning the use of NHibernate in full, because of a minor problem which cannot be perfectly solved by an ORM directly. Actually, in such a situation I would try to create a workaround for the minor problem (maybe I got you wrong, and that is what you already have in mind).

For the case you described, I am pretty sure some additional stored procedures can be utilized for solving your problem (assumed you are using a database system with full stored procedure support). For example, if you need custom INSERT operations with specific handling of IDs and/or locking, encapsulate the operation in a stored procedure and combine it with NHibernate like shown here. I am not an expert on NHibernate, but you will find more information on combining it with stored procedures on Stackoverflow.

Doc Brown
  • 199,015
  • 33
  • 367
  • 565
1

Regardless of what you use, you need to make sure you lock the single record in your settings table for the shortest time possible. It is the bottle-neck in all of your receipt creation. Because of this, I would do this in 3 steps with the first 2 wrapped in a transaction (I believe Nhibernate can do that.).

Create Receipt Record

  1. Start Transaction: Update Settings table with new Receipt ID (Realizing you need to get the current ID and perform calc to increment.)
  2. Insert new Receipt ID into receipt table: End Transaction.

Save Receipt Record

  1. Update receipt table based on the Receipt ID for the current user entry with all other relevant data provided by the user. No reason to wait for the user to save the entire record.

I don't believe your database will allow another transaction to read the dirty record in the Settings table during the transaction that wraps steps #1 & #2 unless you do some sort of "no lock" over-ride.

If the user changes her mind after the unique receipt id is created, you'll just have to do some sort of delete (maybe soft) for that record and just eat the ID. I realize in some VAT systems these ID's are important and must be incremental which is why I recommend the soft delete.

JeffO
  • 36,816
  • 2
  • 57
  • 124