This is a follow up post to my previous entry titled, You Can't Lock What Isn't There. In that post I explain the folly of trying to use SQL Server locking hints to prevent duplicate row inserts in certain scenarios.
I exclaimed with great gusto that "you cannot use lock hints to prevent duplicate inserts. Period." Seems pretty final and certain, huh?
Well, it's not entirely true. In fact, you can use locking hints in combination with strict isolation levels to accomplish this feat. Specifically, you can use the serializable isolation level.
Isolation levels are on a per-connection basis in SQL Server. Once they're set, they stick around for that connection until they're changed. The serializable isolation level ensures that not only are results in the results set not updated, like the repeatable read isolation level, but also that no "phantom" rows appear. In other words, it prevents inserts that would be included in the results set from occurring. If I re-run my query again inside the same transaction, I will get the exact same results.
So how does this help us in the scenario I described in my other blog post? Well, the EXISTS check (see below) would include a duplicate value if it were inserted between the check and the subsequent insert and the same code was run again, and since that would violate the "phantom" rows guarantee of the serializable isolation level, SQL Server blocks that insert until the transaction completes.
BEGIN TRAN
IF NOT EXISTS(SELECT 1 FROM Orders WHERE OrderTxID = @OrderTxID)
BEGIN
INSERT INTO Orders (NEWID(), @ProductID, @OrderTxID)
END
COMMIT TRAN
How does SQL Server accomplish this feat? It's not pretty. SQL Server essentially locks the page that will eventually include the indexed column value being used in the query. In this case, it looks at the OrderTxID column's index, finds the page (or pages, or extents) that would include the next value in the sequence for this query, and places a lock on it.
Here is the real kicker: If no index is present, or if it can't determine what page or extent to lock, guess what... it locks the entire table. Now that's gonna be great for scalability, huh?
So I apologize for misleading you with my previous blog post. It wasn't completely correct. But now that you know the truth, pretend like you don't.
