0

I'm trying to make sure that one and only one row gets inserted into a table, but I'm running into issues where multiple processes are bumping into each other and I get more than one row. Here's the details (probably more detail than is needed, sorry):

There's a table called Areas that holds a hierarchy of "areas". Each "area" may have pending "orders" in the Orders table. Since it's a hierarchy, multiple "areas" can be grouped under a parent "area".

I have a stored procedure called FindNextOrder that, given an area, finds the next pending order (which could be in a child area) and "activates" it. "Activating" it means inserting the OrderID into the QueueActive table. The business rule is that an area can only have one active order at a time.

So my stored procedure has a statement like this:

IF EXISTS (SELECT 1 FROM QueueActive WHERE <Order exists for the given area>) RETURN
...
INSERT INTO QueueActive <Order data for the given area>

My problem is that there every once in a while, two different processes will call this stored procedure at almost the same time. When each one does the check for an existing row, each comes back with a zero. Because of that both processes do the insert statement and I end up with TWO active orders instead of just one.

How do I prevent this? Oh, and I happen to be using SQL Server 2012 Express but I need a solution that works in SQL Server 2000, 2005, and 2008 as well.

I already did a search for exclusively locking a table and found this answer but my attempt to implement this failed.

Community
  • 1
  • 1
Jason
  • 156
  • 1
  • 11

2 Answers2

0

I would use some query hints on your select statement. The trouble is coming along because your procedure is only taking out shared locks and thus the other procedures can join in.

Tag on a WITH (ROWLOCK, XLOCK, READPAST) to your SELECT

ROWLOCK ensures that you are only locking the row.
XLOCK takes out an exclusive lock on the row, that way no one else can read it.
READPAST allows the query to skip over any locked rows and keep working instead of waiting.

The last one is optional and depends upon your concurrency requirements.

Further reading:
SQL Server ROWLOCK over a SELECT if not exists INSERT transaction
http://technet.microsoft.com/en-us/library/ms187373.aspx

Community
  • 1
  • 1
Mathmagician
  • 113
  • 5
  • Good suggestions, but I tried this as well. The problem is that I need to lock the whole table, not just a single row. (I need the whole table locked because there might not be a row that exists to lock.) – Jason Sep 20 '13 at 20:50
  • @user2800568 you need `holdlock` To lock the range per the duplicate and `updlock` not `xlock` to avoid deadlock. See the dupe. – Martin Smith Sep 20 '13 at 21:10
0

Have you tried to create a trigger that rolls back second transaction if there is one Active order in a table?

sqlint
  • 1,071
  • 9
  • 8