Can one table have an exclusive lock while updating a second table?

user_742356 0
Viewed 42

I'm working in SQL Server 2016. I have two tables, one is a queue of work items (TQueue), and the second is the work items (TWork) that are being processed.

I have a script that grabs the top 100 items from TQueue that do not have a record in TWork, and then inserts those items into TWork to be processed.

For performance reasons, I want to run multiple instances of the script simultaneously. The challenge is that Script 1 grabs 100 items, and before the transaction to insert these items into TWork is committed, Script 2 grabs the same set of items and inserts them as well.

Question

I would like to block the reading of TQueue until insert transaction into TWork has completed. Is this possible?

sql-server
1 Answers

You may use table hints to achieve this goal.

For example:

Create Table Val (ID Int)

Insert Into Val (ID)
Values (0),(1),(2),(3),(4),(5)

First session:

Set Transaction Isolation level Read Committed

Begin Transaction

Select Top 2 * From Val With (ReadPast, XLock, RowLock)
-- Return 0,1

-- Commit has been commented for illustrative purposes.
-- Don't forget to commit the transaction later.

-- Commit

Second session:

Set Transaction Isolation level Read Committed

Begin Transaction

Select * From Val With (ReadPast, XLock, RowLock)
-- Return 2,3,4,5

Commit
user_117698590
Related