1

I have the following SP: 

CREATE PROCEDURE [dbo].[sp_LockReader]  
AS  
BEGIN  
SET NOCOUNT ON;  
begin try   
set transaction isolation level serializable  
begin tran  
select * from teste  
commit tran  
end try  
begin catch  
rollback tran  
set transaction isolation level READ COMMITTED  
end catch  
set transaction isolation level READ COMMITTED  
END

The table "test" has many values, so "select * from teste" takes several seconds. I run the sp_LockReader at same time in two diferent query windows and the second one starts showing test table contents without the first one terminates.

  • Shouldn't serializeble level forces the second query to wait?
  • How do i get the described behaviour?

Thanks

gbn
  • 422,506
  • 82
  • 585
  • 676
Zé Carlos
  • 3,627
  • 5
  • 43
  • 51

3 Answers3

3

SERIALIZABLE at the most basic means "hold locks for longer". When you SELECT, the held lock is a shared lock which allows other readers.

If you want to block readers, use WITH (TABLOCKX) hint to take an exclusive lock where you don't need SERIALIZABLE. Or XLOCK with SERIALIZABLE

In other words:

  • SERIALIZABLE = Isolation Level = lock duration, concurrency
  • XLOCK = mode= sharing/exclusivity

  • TABLOCK = Granularity = what is locked

  • TABLOCKX = combined

See this question/answer for more info

Community
  • 1
  • 1
gbn
  • 422,506
  • 82
  • 585
  • 676
1

A serializable transaction whose output is not affected by other concurrent transactions. In your case, you are SELECTing twice from the table; neither of those transactions changes the result set of the other, so they may both run simultaneously.

Even if one transaction did update the table, this would not necessarily prevent the other from executing, as the database may work from snapshots.

Have a look here for a better explanation than I can provide... http://en.wikipedia.org/wiki/Isolation_%28database_systems%29

Brian Hooper
  • 21,544
  • 24
  • 88
  • 139
0

Another note here. If you're using XLOCK under a SERIALIZABLE isolation, other transactions with READ COMMITTED isolation will still be able to read XLOCK'ed rows.

To prevent that, use PAGLOCK along with XLOCK. See here for details http://support.microsoft.com/kb/324417

Srinidhi
  • 469
  • 6
  • 8
  • The KB article seems to apply only to SQL Server 2000. I'm pretty sure most people have upgraded to at least 2005 by now. – Jordan Rieger Aug 07 '13 at 22:30
  • 1
    Yes Jordan, you're right. It does successfully block a READ COMMITTED transaction. I tested it with 2008 R2. Read access was granted (to other connections) in two scenarios when the rows were XLOCK'ed - 1) on a READ UNCOMMITTED & 2) on a SNAPSHOT. However a SNAPSHOT with an XLOCK was blocked. – Srinidhi Aug 09 '13 at 18:00