Shared locks acquired even after setting tran isolation as snapshot

• netflix_y

  Old Hand

  Points: 386

  More actions

  July 13, 2010 at 7:54 pm

  #141313

  I have SQL Server 2005 Sp2 mode 90

  Even though I use

  read committed Snapshot

  or

  snapshot

  Isolation level I can see shared locks being acquired on select statements. Is something wrong in what I am expecting?

  I have set these options at DB level as well

  alter database SnapshotPromotion

  set allow_snapshot_isolation on

  go

  alter database SnapshotPromotion

  set read_committed_snapshot on

  go

  Thanks

  Suresh

• Grant Fritchey

  SSC Guru

  Points: 398890

  More actions

  July 14, 2010 at 6:01 am

  #1192716

  No, that's normal. What you'll see is that when updates are occurring, a version of the row is stored off in tempdb and that will be used instead of the row being updated. But when nothing is occurring on the table, everything will be pretty much as normal.

  "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
  - Theodore Roosevelt

  Author of:
  SQL Server Execution Plans
  SQL Server Query Performance Tuning

• Paul White

  SSC Guru

  Points: 150467

  More actions

  July 14, 2010 at 7:16 am

  #1192782

  netflix_y (7/13/2010)

  ---

  Even though I use

  read committed Snapshot or snapshot Isolation level I can see shared locks being acquired on select statements. Is something wrong in what I am expecting?

  Suresh,

  To use snapshot isolation level, you also have to issue a SET TRANSACTION ISOLATION LEVEL SNAPSHOT statement. You can verify if the database is enabled for either of the two row-versioning isolation levels using this code: (in the database to test)

  SELECT snapshot_isolation_state_desc, is_read_committed_snapshot_on

  FROM sys.databases

  WHERE database_id = DB_ID();

  As I explained on your other thread, there are some occasions where the database engine must still take shared locks, even when running under a row-versioning isolation level. You are right that, in general, shared locks are not taken when using a row-versioning isolation level.

  Try running a simple SELECT on a stand-alone table with no foreign key relationships, and check the locks taken using Profiler (for example).

  Paul

  Paul White
  SQLPerformance.com
  SQLkiwi blog
  @SQL_Kiwi