Row versionnning and isolation levels

  • Hi everyone,

    In our application we want to dynamically switch between two isolation levels : READ_COMMITTED_SNAPSHOT and SNAPSHOT.

    Most of the time we want to run with the READ_COMMITTED_SNAPSHOT isolation level but under some circonstances

    (consitent reads in multiple tables) we want to switch to the SNAPSHOT isolation level.

    Right now we only use the READ_COMMITED_SNAPSHOT isolation level.

    I read in the documentation that, in order to permit us to use these two isolation levels, we must ALTER the database to SET both READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION to ON.

    I wanted to know what is the impact of doing this :

    [font="Courier New"]ALTER DATABASE my_db SET ALLOW_SNAPSHOT_ISOLATION ON

    GO

    ALTER DATABASE my_db SET READ_COMMITTED_SNAPSHOT ON

    GO

    ...

    SET TRANSACTION ISOLATION LEVEL READ COMMITED

    GO

    [/font]

    Compared to doing just this:

    [font="Courier New"]ALTER DATABASE my_db SET READ_COMMITTED_SNAPSHOT ON

    GO

    ...

    SET TRANSACTION ISOLATION LEVEL READ COMMITED

    GO[/font]

    For a connection running just in READ_COMMITED_SNAPSHOT isolation level?

    I ask this question because our ALTER DATABASE statements are not dynamic, they are issued at the creation of the database itself.

    Best regards.

    Carl

  • Are you suggesting setting these database settings whenever you connect to the database? That's not necessary. You should set this when the database is created, and only use those that you actually need. From your brief description of your business case it would seem that you need both, but I think READ_COMMITTED_SNAPSHOT could be enough. Perhaps you should provide more information regarding the actual business needs (in business terms).

    As far as the differences between SNAPSHOT and READ COMMITTED SNAPSHOT are concerned, perhaps an example might help:

    http://milambda.blogspot.com/2006/09/snapshot-transaction-isolation.html

    (Skip to Mental Aggregation if you're only interested in the differences.)

    ML

    ---
    Matija Lah, SQL Server MVP
    http://milambda.blogspot.com

  • Hi Matija,

    I don't suggest to set these database settings at connection time. I wrote : "I ask this question because our ALTER DATABASE statements are not dynamic, they are issued at the creation of the database itself."

    I was only asking if there is any penalty to set both database properties at database creation time and only using READ_COMMITED snapshot (since we think that the SNAPSHOT isolation level won't be use frequently, only in particular business cases). This is my most important concern.

    We need to switch to SNAPSHOT isolation level in order to have a consistent read of several tables.

    I read something on your blog that seems to be a limitation for me: "Switching to the snapshot isolation level from any other explicitly set isolation level will cause an exception followed by an immediate rollback of all transactions.".

    Is it true that being in the READ_COMMITED_SNAPSHOT isolation level and switching to the SNAPSHOT isolation level (with : SET TRANSACTION ISOLATION LEVEL READ COMMITED) will raise an exception?

    Best regards.

    Carl

  • Answers inline.

    Carl B. (4/21/2009)


    Hi Matija,

    I don't suggest to set these database settings at connection time. I wrote : "I ask this question because our ALTER DATABASE statements are not dynamic, they are issued at the creation of the database itself."

    Thanks for clarifying that. 🙂

    I was only asking if there is any penalty to set both database properties at database creation time and only using READ_COMMITED snapshot (since we think that the SNAPSHOT isolation level won't be use frequently, only in particular business cases). This is my most important concern.

    I can't imagine the setting having any effect on performance, until tempdb is actually used (i.e. after snapshots have been created).

    Anyway, your best bet would be to just try it out.

    Speaking of tempdb, I suggest you read up on tempdb monitoring.

    E.g.: http://milambda.blogspot.com/2006/09/sql-2005-and-tempdb.html

    We need to switch to SNAPSHOT isolation level in order to have a consistent read of several tables.

    I read something on your blog that seems to be a limitation for me: "Switching to the snapshot isolation level from any other explicitly set isolation level will cause an exception followed by an immediate rollback of all transactions.".

    Is it true that being in the READ_COMMITED_SNAPSHOT isolation level and switching to the SNAPSHOT isolation level (with : SET TRANSACTION ISOLATION LEVEL READ COMMITED) will raise an exception?

    It should. Let me get back to you with this one.

    ML

    ---
    Matija Lah, SQL Server MVP
    http://milambda.blogspot.com

  • Oupps.. instead of this:

    Is it true that being in the READ_COMMITED_SNAPSHOT isolation level and switching to the SNAPSHOT isolation level (with : SET TRANSACTION ISOLATION LEVEL READ COMMITED) will raise an exception?

    I should ha written this:

    Is it true that being in the READ_COMMITED_SNAPSHOT isolation level and switching to the SNAPSHOT isolation level (with : SET TRANSACTION ISOLATION LEVEL SNAPSHOT) will raise an exception?

    Sorry for the mistake and thank's for your help.

    Carl

  • Changing the isolation level of a transaction to SNAPSHOT will cause an exception.

    Repro:

    -- step 1 start

    create databaseSnapshotPromotion

    go

    alter databaseSnapshotPromotion

    setallow_snapshot_isolationon

    go

    alter databaseSnapshotPromotion

    setread_committed_snapshoton

    go

    useSnapshotPromotion

    go

    create tabledbo.test

    (

    cint

    )

    go

    -- step 1 end

    -- step 2 start

    settransaction isolation level read committed

    begin tran

    print'Read committed using snapshots'

    print@@trancount

    insertdbo.test

    (

    c

    )

    select1 as c

    go

    -- step 2 end

    -- step 3 start

    settransaction isolation level snapshot

    print'Snapshot'

    print@@trancount

    insertdbo.test

    (

    c

    )

    select2 as c

    commit tran

    print@@trancount

    go

    select*

    fromdbo.test

    -- step 3 end

    -- step 4 start

    usemaster

    go

    settransaction isolation level read committed

    go

    drop databaseSnapshotPromotion

    go

    -- step 4 end

    Changing the isolation level from SNAPSHOT is supported.

    E.g.:

    -- step 1 start

    create databaseSnapshotPromotion

    go

    alter databaseSnapshotPromotion

    setallow_snapshot_isolationon

    go

    alter databaseSnapshotPromotion

    setread_committed_snapshoton

    go

    useSnapshotPromotion

    go

    create tabledbo.test

    (

    cint

    )

    go

    -- step 1 end

    -- step 2 start

    settransaction isolation level snapshot

    begin tran

    print'Snapshot'

    print@@trancount

    insertdbo.test

    (

    c

    )

    select1 as c

    go

    -- step 2 end

    -- step 3 start

    settransaction isolation level read committed

    print'Read committed using snapshots'

    print@@trancount

    insertdbo.test

    (

    c

    )

    select2 as c

    commit tran

    print@@trancount

    go

    select*

    fromdbo.test

    -- step 3 end

    -- step 4 start

    usemaster

    go

    settransaction isolation level read committed

    go

    drop databaseSnapshotPromotion

    go

    -- step 4 end

    ML

    ---
    Matija Lah, SQL Server MVP
    http://milambda.blogspot.com

  • Hi Matija,

    Ok thank's. I noticed that you were changing the isolation level during (inside a transaction). That's why this exception arise.

    I made some tests changing the isolation level wihtout being in a transaction and it works without any problem. 🙂

    Best regards.

    Carl

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

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