read committed snapshot

Question

read committed snapshot

msimone

SSCommitted

Points: 1846
More actions
September 15, 2021 at 8:59 am

#3929732

In a DB I executed
alter database xxx set read_committed_snapshot ON
If I execute
set transaction isolation level read committed
Is the session with isolation level read committed or read committed snapshot?
Thanks for all.

set transaction isolation level read committed

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 1

Changing the database changes the default behavior. Changing the specifics of the connection changes that connection only.

"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

msimone SSCommitted Points: 1846 More actions · Answer 2

Thanks Grant, when you see the results from sys.dm_exec_requests, the column transaction_isolation_level always is 2 without the execution of the sentence set transaction isolation level read committed and with this sentence executed, when the BD is setting with read_committed_snapshot.

Y don't know how to distinguish if a session is read_committed or read_committed_snapshot.

Ken McKelvey SSCoach Points: 18854 More actions · Answer 3

For the session's default database:

SELECT
CASE S.transaction_isolation_level
WHEN 0 THEN 'Unspecified' 
WHEN 1 THEN 'Read Uncommitted'
WHEN 2 THEN 'Read Committed'
WHEN 3 THEN 'Repeatable Read'
WHEN 4 THEN 'Serializable' 
WHEN 5 THEN 'Snapshot'
END
+ CASE
WHEN D.is_read_committed_snapshot_on = 1
THEN ' Snapshot'
ELSE ''
END AS IsolationOnDefaultDB
FROM sys.dm_exec_sessions S
JOIN sys.databases D
ON S.database_id = D.database_id
WHERE S.session_id = @@SPID

msimone SSCommitted Points: 1846 More actions · Answer 4

Thanks Ken, but a question:

How the DB is setting read_committed_snapshot, what's the matter if I execute in my session "set transaction isolation level read committed", is the session in read committed or read committed snapshot?

ScottPletcher SSC Guru Points: 101118 More actions · Answer 5

msimone wrote:

In a DB I executed
alter database xxx set read_committed_snapshot ON
If I execute
set transaction isolation level read committed
Is the session with isolation level read committed or read committed snapshot?

Read Committed Snapshot. Once turned on, this is in effect for all trans running at Read Committed level.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

msimone SSCommitted Points: 1846 More actions · Answer 6

msimone

SSCommitted

Points: 1846

September 15, 2021 at 2:41 pm

#3929851

Thanks Scott, it was my doubt.