0

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.

UVData
  • 459
  • 2
  • 6
  • 13
  • this has to do with some optimization that sql does, refer to this link.http://sqlblog.com/blogs/louis_davidson/archive/2006/12/13/does-xlock-always-prevent-reads-by-others.aspx. If you want it to work properly use 2 tabs. – Aflred Dec 03 '16 at 19:48

1 Answers1

0

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.

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
  • 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. – UVData Aug 08 '14 at 18:09
  • It's phenomenally expensive, from a resource usage perspective, to actually generate sequential numbers without gaps, especially in the face of multiple connections. That's *why* the built in generators (e.g. `IDENTITY` columns and, since 2012, sequences) don't offer such a guarantee. Are you *sure* you need such a guarantee and can't just use the built in features? – Damien_The_Unbeliever Aug 09 '14 at 06:11
  • I agree with you, we are facing problems but right now its not possible to change design. But still, it seems to be a valid question to me that why cant I have exclusive lock on select statement? – UVData Aug 11 '14 at 10:43
  • You can - I show in my answer how to do it - you have to explicitly ask for it using the `XLOCK` hint. – Damien_The_Unbeliever Aug 11 '14 at 10:46
  • Yes, I tried that and that works. However, I am not able to understand why "set transaction isolation level serializable" doesn't work for select statement. Is there difference between exclusive lock obtained using "set transaction isolation level serializable" and "XLOCK". Should I learn that "set transaction isolation level serializable" doesn't work for select statements while XLOXK works? – UVData Aug 12 '14 at 11:50
  • As I point out in the first part of my answer, you need to set the isolation level *before* starting the transaction for it to have any effect, but even if you *do* swap the statements around, it still has no effect because it only *needs* a shared lock, even under serializable isolation, if your only activity is `SELECT`. – Damien_The_Unbeliever Aug 12 '14 at 12:36