Post reply

To much (early) blocking.

  • December 1, 2016 at 8:00 am

    #332282

    Hello All,

    Isolation levels, I am trying with examples how the different isolation levels work.

    Most of the examples I can come up with block early and produce the same result in the end.

    Are there simple examples which produce different results in the end ? (For the examples the blocking is not a problem as long as the results are different).

    Under the signature I have included an example which shows serializable vs snapshot. This is a simple and excelent example of one of the differences.

    I am looking for 'similar' simple examples for the other isolation levels.

    Ben

    I found this excelent example at :

    https://blogs.msdn.microsoft.com/craigfr/2007/05/16/serializable-vs-snapshot-isolation-level/

    --

    -- Example :

    -- Shows that SnapShot isolation does not produce a Serializable result.

    --

    --

    -----------------------------------------------------------------------------------

    -- Parpare the marbles table.

    -----------------------------------------------------------------------------------

    select 1 nr,'black' kleur into marbles

    insert into marbles values

    (2, 'white')

    ,(3, 'white')

    ,(4, 'black')

    ,(5, 'white')

    ,(6, 'white')

    ,(7, 'white')

    ,(8, 'black')

    ,(9, 'black')

    ,(10, 'white')

    ,(11, 'white')

    ,(12, 'black')

    -----------------------------------------------------------------------------------

    -- script for transaction 1 and transaction 2.

    -- Pause the transactions before the commit.

    -----------------------------------------------------------------------------------

    SET TRANSACTION ISOLATION LEVEL

    -- READ UNCOMMITTED

    -- READ COMMITTED

    -- REPEATABLE READ

    SNAPSHOT

    -- SERIALIZABLE

    Begin tran sarbnetfets

    --

    -- Transaction 1

    --

    Update marbles set kleur = 'black' where kleur = 'white'

    --

    -- Transaction 2

    --

    -- Update marbles set kleur = 'white' where kleur = 'black'

    Commit tran sarbnetfets

    select * from marbles

    -- drop table marbles

  • December 1, 2016 at 8:15 am

    #1915430

    Keep in mind that the isolation levels affect the behaviour of *read* queries, not data modifications for the most part.

    Repeatable read vs read committed: can two identical selects within a transaction return different data for rows due to data modifications elsewhere

    Repeatable read vs serialisable: can two identical selects within a transaction return different numbers of rows due to inserts elsewhere

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 1, 2016 at 8:36 am

    #1915438

    GilaMonster (12/1/2016)

    Keep in mind that the isolation levels affect the behaviour of *read* queries, not data modifications for the most part.

    Repeatable read vs read committed: can two identical selects within a transaction return different data for rows due to data modifications elsewhere

    Repeatable read vs serialisable: can two identical selects within a transaction return different numbers of rows due to inserts elsewhere

    Thank you for your remarks,

    I would like simple examples which show this.

    Ben

  • December 1, 2016 at 8:42 am

    #1915445

    They're not hard to do.

    Multiple identical selects in a transaction (maybe with a waitfor between them) and updates, inserts and deletes running from elsewhere that would affect rows returned by that query.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 1, 2016 at 10:32 am

    #1915514

    I wrote a small series on isolation levels a while back, so I could understand how they worked.

    Start at http://wp.me/p3Vxvi-8R and see if they help.

    Steve Hall
    Linkedin
    Blog Site

  • December 2, 2016 at 1:59 am

    #1915631

    BrainDonor (12/1/2016)

    I wrote a small series on isolation levels a while back, so I could understand how they worked.

    Start at http://wp.me/p3Vxvi-8R and see if they help.

    Yes this does help. Thanks.

    Although I was looking for something more simple. 😀

    Comming up with good and simple examples is hard to do.

    The marbles (see reference) example is simple and very good.

    But maybe I am looking for something which does not exist.

    Thanks,

    Ben

  • December 2, 2016 at 2:49 am

    #1915642

    ben.brugman (12/2/2016)

    The marbles (see reference) example is simple and very good.

    Except that it won't show isolation level differences except for lock-based vs rowversion-based, because they're data modifications and hence aren't affected by the differences between read uncommitted, read committed, repeatable read and serializable (or between read committed snapshot and snapshot)

    It is, as the author says, for showing serializable vs snapshot.

    But maybe I am looking for something which does not exist.

    I'm sure they do exist, but it'll probably be quicker to write your own than search. I mean, a transaction with two selects where you change the isolation level and a second connection with insert, update, delete in a while loop

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

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

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