Multi-process Contention in SQL

Multi-process Contention in SQL
When you have multiple processes managing the same data in your database, often you will need some sort of transactional model assuring multiple processes don’t pick up the same unit of work (rows in a table).

The easiest technique to separate processes is to lock records to/being processed. I like the queue table concept which I demonstrated months ago. Using this technique you simply delete from a table using query hints WITH (ROWLOCK, READPAST). Those two hints lock the row being deleted from other processes, and skip any rows with locks from another process. This allows the query to continue without having to wait for other locks to be dropped, and to block other processes from getting rows it is processing.

A delete statement is not the only statement you could use. Instead you could use an update statement. The key is, you need to execute a query that will lock the intended record or records until your process is complete.

In contrast, you can’t use:

SELECT TOP 1 @SomeID = SomeID FROM SomeTable WITH (ROWLOCK)

While this will lock the row you are reading, as soon as the query completes the lock is dropped.

Here is a simple method that works…

SELECT *
INTO #SomeTable
WHERE 0 = 1

DELETE TOP (1)
OUTPUT DELETED.*
INTO #SomeTable
FROM SomeTable WITH (ROWLOCK, READPAST)

Now you have a temp table #tempTable that will always have the same schema as SomeTable…because it is created new each time. Notice the SELECT * which implements this functionality. I don’t use SELECT * often. But in this case, the query continues to work, even after the table schema has changed.

The best part is that #SomeTable contains the row(s) that were deleted…and I can execute work based on that work. Moreover, since I deleted the row, no other query can obtain that row, and I still have the full contents of the data deleted.

If instead you needed to retain the data in the row you could use an Update statement with the output clause. However, the difference is that it will need to be embedded in a transaction to assure no other processes picks up the row(s) you wish to process. Doing this, the lifetime of the transaction continues until you execute a COMMIT or ROLLBACK. This differentiates the UPDATE method from a simple SELECT statement placing the ID into a variable.

DECLARE @Record TABLE (ID INT NOT NULL)

COMMIT

…Do Other SQL Work Joining @Record

When using multiple processes, the UPDATE or DELETE technique assure that only a single process will manipulate records.

Cheers,

Ben

$$SWYNK$$

Featured Article(s)
Troubleshooting SQL Server 2012 Integration Services
In this article, Alexander Chigrik explains some problems that you can have when you work with SQL Server 2012 Integration Services. He also tells how you can resolve these problems.

Featured White Paper(s)
Ten Tips for Optimizing SQL Server Performance
read more)