Isolation Level SERIALIZABLE

  • Uwe Ricken

    Hall of Fame

    Points: 3098

    Comments posted to this topic are about the item Isolation Level SERIALIZABLE

    Microsoft Certified Master: SQL Server 2008
    MVP - Data Platform (2013 - ...)
    my blog: http://www.sqlmaster.de (german only!)

  • Stewart "Arturius" Campbell

    SSC Guru

    Points: 71483

    Great question, thanks Uwe
    trying to explain this to developers who insist setting the isolation level to SERIALIZABLE in their code then wondering why the application causes blocking makes for interesting discussions...

    ____________________________________________
    Space, the final frontier? not any more...
    All limits henceforth are self-imposed.
    β€œlibera tute vulgaris ex”

  • Terje Hermanseter

    Hall of Fame

    Points: 3846

    Uwe Ricken - Tuesday, March 7, 2017 9:03 PM

    Comments posted to this topic are about the item Isolation Level SERIALIZABLE

    I sat up a test database for this question and rigged a test running the two queries from two different machines. I was able to read the data in query 2 without any problems. Based on that I obviously answered the question wrong πŸ™‚

    I guess the second query has has to be run at the exact same time as the first query for the blocking to occur? I did put in a sleep for two minutes before I closed the the first query.

    My test database was a SQL Server 2012 standard edition. On the test database which I called "Snapshot" I ran this:
    ALTER DATABASE Snapshot SET READ_COMMITTED_SNAPSHOT ON
    GO
    I then started this as the first query:
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    GO
    BEGIN TRANSACTION;
    GO
    UPDATE dbo.CustomerOrderDetails SET Price = Price * 1.10
    WHERE Order_Id = 1001;
    GO
    WAITFOR DELAY '00:02'
    COMMIT

    During the two minutes I ran this query on the other machine:
    SELECT * FROM dbo.CustomerOrderDetails WHERE Order_Id = 1002;

    It constantly returned the result for me (I also tried with other Order_id values, even 1001).

    I probably did something wrong, but what?

    .

  • BillLudlow

    SSCertifiable

    Points: 6160

    My understanding of READ COMMITTED SNAPSHOT isolation was obviously wrong I thought the opposite, that it prevented locking errors.  Doh!

  • Mighty

    SSCrazy Eights

    Points: 8485

    Technically the answer is incorrect.This is because there UPDATE query will fail, because Price is not a field in the table, so the second query will just run normally.
    Maybe the question should be corrected to include the Price field.

  • Terje Hermanseter

    Hall of Fame

    Points: 3846

    Mighty - Wednesday, March 8, 2017 3:30 AM

    Technically the answer is incorrect.This is because there UPDATE query will fail, because Price is not a field in the table, so the second query will just run normally.
    Maybe the question should be corrected to include the Price field.

    I assumed he forgot to include a price field in the table, so I just  included a price column in my test database.

    But yes, the question should be corrected.

  • Marek Grzymala

    SSCommitted

    Points: 1890

    Terje Hermanseter - Wednesday, March 8, 2017 12:24 AM

    Uwe Ricken - Tuesday, March 7, 2017 9:03 PM

    Comments posted to this topic are about the item Isolation Level SERIALIZABLE

    I sat up a test database for this question and rigged a test running the two queries from two different machines. I was able to read the data in query 2 without any problems. Based on that I obviously answered the question wrong πŸ™‚

    I guess the second query has has to be run at the exact same time as the first query for the blocking to occur? I did put in a sleep for two minutes before I closed the the first query.

    My test database was a SQL Server 2012 standard edition. On the test database which I called "Snapshot" I ran this:
    ALTER DATABASE Snapshot SET READ_COMMITTED_SNAPSHOT ON
    GO
    I then started this as the first query:
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    GO
    BEGIN TRANSACTION;
    GO
    UPDATE dbo.CustomerOrderDetails SET Price = Price * 1.10
    WHERE Order_Id = 1001;
    GO
    WAITFOR DELAY '00:02'
    COMMIT

    During the two minutes I ran this query on the other machine:
    SELECT * FROM dbo.CustomerOrderDetails WHERE Order_Id = 1002;

    It constantly returned the result for me (I also tried with other Order_id values, even 1001).

    I probably did something wrong, but what?

    .

    I reproduced the steps and am getting exactly same results as Terje (ie. the select is returning the result of both Order_Id 1002 and 1001, in spite of the RangeX-X locks)

  • Gazareth

    One Orange Chip

    Points: 27737

    Marek Grzymala - Wednesday, March 8, 2017 6:41 AM

    Terje Hermanseter - Wednesday, March 8, 2017 12:24 AM

    Uwe Ricken - Tuesday, March 7, 2017 9:03 PM

    Comments posted to this topic are about the item Isolation Level SERIALIZABLE

    I sat up a test database for this question and rigged a test running the two queries from two different machines. I was able to read the data in query 2 without any problems. Based on that I obviously answered the question wrong πŸ™‚

    I guess the second query has has to be run at the exact same time as the first query for the blocking to occur? I did put in a sleep for two minutes before I closed the the first query.

    My test database was a SQL Server 2012 standard edition. On the test database which I called "Snapshot" I ran this:
    ALTER DATABASE Snapshot SET READ_COMMITTED_SNAPSHOT ON
    GO
    I then started this as the first query:
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    GO
    BEGIN TRANSACTION;
    GO
    UPDATE dbo.CustomerOrderDetails SET Price = Price * 1.10
    WHERE Order_Id = 1001;
    GO
    WAITFOR DELAY '00:02'
    COMMIT

    During the two minutes I ran this query on the other machine:
    SELECT * FROM dbo.CustomerOrderDetails WHERE Order_Id = 1002;

    It constantly returned the result for me (I also tried with other Order_id values, even 1001).

    I probably did something wrong, but what?

    .

    I reproduced the steps and am getting exactly same results as Terje (ie. the select is returning the result of both Order_Id 1002 and 1001, in spite of the RangeX-X locks)

    Same for me - with the queries run as posted I can see the RangeX-X Key locks on 1001 & 1002 but can happily read the rows (or the entire table) in another (read committed) session.
    Tried with & without the waitfor (with no commit) and it behaves the same.

    Edit: remove bad information

  • MattF

    SSCrazy

    Points: 2285

    It looks as though the rows should be readable as the range key locks prevent phantom reads, and inserts, updates & deletes - from https://technet.microsoft.com/en-us/library/ms191272.aspx  "This lock blocks any attempt to insert, update, or delete any row with a key value that falls in the range because those operations would first have to acquire a lock on the index"
    From the same post: "Key-range locking prevents phantom reads"  (reading the data changed by uncommitted transactions).
    So the Select statement is reading committed data unaffected by the first transaction.

  • BillLudlow

    SSCertifiable

    Points: 6160

    But isn't that what  READ COMMITTED SNAPSHOT Isolation  is supposed to allow, i.e. read committed data without locking it?

  • MattF

    SSCrazy

    Points: 2285

    BillLudlow - Thursday, March 9, 2017 1:24 AM

    But isn't that what  READ COMMITTED SNAPSHOT Isolation  is supposed to allow, i.e. read committed data without locking it?

    Yes. It looks as though the question was not framed very well - I understood it to mean will the Select statement succeed as it occurs after the update transaction. The answer's explanation does not concur with this: "The isolation level SERIALIZABLE overwrites the RCSI for the session and prevents other processes to insert new positions for the order with the ID = 1001."

  • TomThomson

    SSC Guru

    Points: 104772

    This appears to me to be just plain wrong.

    Since the first (serialized isolation level) query is using a lock which prevents updates and inserts, and a RANGES-S lock will not do that, it requires a RANGES-U lock.  But since the second query is doing a simple read action, it requires only some S locks which iare compatible not only with the RANGE-S-S lock which the explanation wrongly states will be used but also with the more restrictive RANGES-U lock that will actually be used.   If the second query were serialisable it would require a RANGES-S lock, but that would still be compatible.  Maybe the question author intended the serialisable transaction to be read only while the the second transaction was an update (if that's how he was thinking it's also why he thought the serializable case would use a RANGES-S lock).   Perhaps what's happened is a bit of a muddle in copying and pasting the question from where it was developed for delivery to Steve - I have personal experience of getting that wrong, so I expect other people to do so too.

    The necessity to lock a point that is not in the range to cope with the multi-column range lock where all values for the first "irrelevant" index colum have to be exluded is something that I had hoped Microsoft would have overcome by now anyway.  The situation that requires it crops up quite often, but maybe the cost of avoiding the reduction in concurrency is inevitably greater than the benefit, so perhaps it was a forlorn hope.

    Tom

  • Jacob Wilkins

    One Orange Chip

    Points: 27839

    This appears to me to be just plain wrong.

    +1

    Catching up after a lengthy unintended hiatus from the site, so just got to this QotD.

    I too thought maybe the code blocks were just switched, but the Order_IDs mentioned line up with the code blocks as they are, so probably something else going on as well.

    Either way, as it stands, the answer/explanation for this QotD is incorrect.

    Still a helpful topic to force people to think about, of course πŸ™‚

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

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