read committed snapshot

• msimone

  SSCommitted

  Points: 1842

  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

   

• Grant Fritchey

  SSC Guru

  Points: 397721

  More actions

  September 15, 2021 at 1:17 pm

  #3929795

  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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

• msimone

  SSCommitted

  Points: 1842

  More actions

  September 15, 2021 at 1:46 pm

  #3929829

  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: 18484

  More actions

  September 15, 2021 at 2:08 pm

  #3929834

  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: 1842

  More actions

  September 15, 2021 at 2:27 pm

  #3929838

  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: 100262

  More actions

  September 15, 2021 at 2:39 pm

  #3929850

  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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

• msimone

  SSCommitted

  Points: 1842

  More actions

  September 15, 2021 at 2:41 pm

  #3929851

  Thanks Scott, it was my doubt.