CODEWITHSUNDEEP
sqlsql-serverlockingsharedisolation
UVData
UVData

Reputation: 511

exclusive lock and shared lock for select statement - SQL Server

I am not able to understand how select will behave while its part of exclusive transaction. Please consider following scenarios –

Scenario 1 Step 1.1

create table Tmp(x int)
insert into Tmp values(1)

Step 1.2 – session 1

begin tran
set transaction isolation level serializable 
select * from Tmp

Step 1.3 – session 2

select * from Tmp

Even first session hasn't been finished, session 2 will be able to read tmp table. I thought Tmp will have exclusive lock and shared lock should not be issued to select query in session 2. And it’s not happening. I have made sure that default isolation level is READ COMMITED.

Thanks in advance for helping me in understanding this behavior.

EDIT : Why I need select in exclusive lock?

I have a SP which actually generate sequential values. So flow is -

  1. read max values from Table and store value in variables
  2. Update table set value=value+1

This SP is executed in parallel by several thousand instances. If two instances execute SP at same time, then they will read same value and will update value+1. Though I would like to have sequential value for every execution. I think its possible only if select is also part of exclusive lock.

Upvotes: 0

Views: 12149

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239784

If you want a transaction to be serializable, you have to change that option before you start the outermost transaction. So your first session is incorrect and is still actually running under read committed (or whatever other level was in effect for that session).

But even if you correct the order of statements, it still will not acquire an exclusive lock for a plain SELECT statement.

If you want the plain SELECT to acquire an exclusive lock, you need to ask for it:

select * from Tmp with (XLOCK)

or you need to execute a statement that actually requires an exclusive lock:

update Tmp set x = x

Your first session doesn't need an exclusive lock because it's not changing the data. If your first (serializable) session had run to completion and either rolled back or committed, before your second session was started, that session's results would still be the same because your first session didn't change the data - and so the "serializable" nature of the transaction was correct.

Upvotes: 0

Related Questions