CODEWITHSUNDEEP
sqlsql-serverconcurrencytransactionsacid
Ali. B
Ali. B

Reputation: 65

How to control concurrency in SQL Server SELECT queries?

How to control concurrency in SQL Server SELECT queries?

Here is the problem, these SQL queries below are going to execute at the same time but they should not take the same record.

How could I handle this?

Session 1: 

BEGIN TRAN

DECLARE @Id INT

SELECT TOP 1 @Id = Id 
FROM dbo.Table_1

UPDATE dbo.Table_1
SET is_taken = 1
WHERE Id = @Id

COMMIT TRAN

Session 2:

BEGIN TRAN

DECLARE @Id INT

SELECT TOP 1 @Id = Id 
FROM dbo.Table_1

UPDATE dbo.Table_1
SET is_taken = 1
WHERE Id = @Id

COMMIT TRAN

Upvotes: 2

Views: 2346

Answers (2)

Bulat
Bulat

Reputation: 6969

As mentioned in comments, SQL Server takes care of isolation, hence you can just run update without select:

UPDATE TOP (1) dbo.Table_1
SET is_taken = 1

I guess you meant to also have a condition on select to avoid updating same record each time:

UPDATE TOP (1) dbo.Table_1
SET is_taken = 1
OUTPUT INSERTED.Id into @id
WHERE is_taken != 1

Upvotes: 0

Mark Anderson
Mark Anderson

Reputation: 11

You can lock the row that you are updating which would have session 2 wait until the transaction in session 1 is completed. I would consider using an update lock or a shared lock(read only).

This should eliminate any concerns you have concurrency between session.

Understand Locking in SQL Server

Upvotes: 1

Related Questions