default isolation level being ignored? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

default isolation level being ignored?

Author

Topic

ferrethouse
Constraint Violating Yak Guru

352 Posts

Posted - 2012-09-11 : 12:58:31

If I run...

DBCC USEROPTIONS

I get...

isolation level read committed snapshot

But If I turn on profiler I see this for SELECT statements...

-- network protocol: TCP/IP
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed

Is profiler just not reporting the "SNAPSHOT" part or is it actually not running in snapshot mode?

The reason I ask is because I am experiencing a decent amount of blocking caused by SELECT statements.

chadmat
The Chadinator

1974 Posts

Posted - 2012-09-11 : 14:05:39

From BOL:

"DBCC USEROPTIONS reports an isolation level of 'read committed snapshot' when the database option READ_COMMITTED_SNAPSHOT is set to ON and the transaction isolation level is set to 'read committed'. The actual isolation level is read committed."

-Chad

ferrethouse
Constraint Violating Yak Guru

352 Posts

Posted - 2012-09-11 : 14:14:31

quote:
Originally posted by chadmat

From BOL:

"DBCC USEROPTIONS reports an isolation level of 'read committed snapshot' when the database option READ_COMMITTED_SNAPSHOT is set to ON and the transaction isolation level is set to 'read committed'. The actual isolation level is read committed."

-Chad

So profiler is probably just not reporting the SNAPSHOT setting. That's what I suspected. I'm struggling to figure out why I'm getting SELECT locks if I'm in READ COMMITTED SNAPSHOT isolation level.

ferrethouse
Constraint Violating Yak Guru

352 Posts

Posted - 2012-09-11 : 14:19:09

I may have just found my answer but am thoroughly confused now.

DBCC UserOptions gives READ COMMITTED SNAPSHOT

But if I run...

SELECT snapshot_isolation_state_desc from sys.databases
where name='mydb'

I get... OFF

But read_committed_snapshot_on has a value of 1

Urg.

chadmat
The Chadinator

1974 Posts

Posted - 2012-09-11 : 14:27:26

sys.databases is reporting ALLOW_SNAPSHOT_ISOLATION is off, which is different than READ_COMMITTED_SNAPSHOT

http://msdn.microsoft.com/en-us/library/ms175095(v=SQL.105).aspx

-Chad

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources