read committed snapshot

  • 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

     

  • 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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • 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.

     

     

  • 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
  • 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?

     

  • 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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • Thanks Scott, it was my doubt.

     

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

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