Last committed value in sql

  • Consider when ,userA begins transaction , does 'update','insert','delete' but commit transaction is pending.

    now, whether is it possible for userB to read last committed values ?

  • Yes, if any form of snapshot has been enabled for the database.

    ALTER DATABASE db SET ALLOW_SNAPSHOT_ISOLATION ON

    UserB can now read the the most recently committed database, if the user first issue the command SET TRANSACTION ISOLATION LEVEL SNAPSHOT.

    ALTER DATABASE db SET READ_COMMITTED_SNAPSHOT ON

    In this mode, the default isolation level READ COMMITTED is implemented with help of the snapshot, so userB does not have to submit any extra commands, but "it just works".

    That does not mean that the two cases are equivalent. With true SNAPSHOT isolation, you see the database as it looked like when the transaction started. That is, if user B says:

    SET TRANSACTION ISOLATION LEVEL SNAPSHOT

    BEGIN TRANSACTION

    and ten minutes reads a table to which userA made updates to five minutes after userB's transaction started, userB will not see those changes. But with READ COMMITED reading from the snapshot, userB will see those changes.

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • what if userB had opened connection(with snapshot), after userA ran update query and commit is pending from user A ?

    And

    what if user A runs update query first, then user B opens snapshot connection ?

  • When you use SNAPSHOT isolation, you are completely blind for changes that occurs in the database after the transaction started.  Thus, userB does not see the changes from userA.

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • IT researcher wrote:

    Consider when ,userA begins transaction , does 'update','insert','delete' but commit transaction is pending. now, whether is it possible for userB to read last committed values ?

    Yes, if userB uses WITH (NOLOCK) on the table(s), or READ UNCOMMITTED isolation level, which would automatically apply to every table.  I don't know of any other way for a different user to read not-yet-committed data from another user's active and pending transaction.

    For example, userA does:

    BEGIN TRANSACTION

    UPDATE dbo.table1 SET col1 = 5 /*original value was 2*/ WHERE id = 17

    Then userB does:

    SELECT col1 FROM dbo.table1 WITH (NOLOCK) WHERE id = 17

    userB would get back 5, not 2.

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

  • As a bit of a sidebar, you can easily test all of this.  Set it up and test it.  You'll remember it for a whole lot longer that way.  And, if you save the test, you can run it again (while reading the notes you left yourself) to demonstrate it again.

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Yes, if userB uses WITH (NOLOCK) on the table(s), or READ UNCOMMITTED isolation level

    But Scott, "IT Researcher" asked "is it possible for userB to read last committed values" and the transaction from userA was left uncommitted. You answered a different question: how userB could read the uncommitted values from userA.

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • Erland Sommarskog wrote:

    Yes, if userB uses WITH (NOLOCK) on the table(s), or READ UNCOMMITTED isolation level

    But Scott, "IT Researcher" asked "is it possible for userB to read last committed values" and the transaction from userA was left uncommitted. You answered a different question: how userB could read the uncommitted values from userA.

    OK.  By default you'll always read the last committed value, using standard READ COMMITTED (hence the name, I guess :-)).

    SNAPSHOT isolation actually prevents you from reading the last committed value, it forces you to read the last committed value as of the time the reading SQL statement started.  If something commits one millisecond after that, you won't read that value using SNAPSHOT.

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

  • OK. By default you'll always read the last committed value, using standard READ COMMITTED (hence the name, I guess :-)).

    ...or you will be blocked if RCSI is not enabled.

    Your point about SNAPSHOT is correct, there might be changes committed after the transaction started, that you will not see.

    The same is in fact also true to RCSI. Because of the implementation, you will normally get the database as it was when the statement started. But a row could be updated and committed after the statement started but you come around to read that row. The exception is when then access is through a user-defined function which has not been inlined.

    But I think that at this point we have gone way beyond what "IT Researcher" really wanted to know. 🙂

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • Erland Sommarskog wrote:

    OK. By default you'll always read the last committed value, using standard READ COMMITTED (hence the name, I guess :-)).

    ...or you will be blocked if RCSI is not enabled.

    Your point about SNAPSHOT is correct, there might be changes committed after the transaction started, that you will not see.

    The same is in fact also true to RCSI. Because of the implementation, you will normally get the database as it was when the statement started. But a row could be updated and committed after the statement started but you come around to read that row. The exception is when then access is through a user-defined function which has not been inlined.

    But I think that at this point we have gone way beyond what "IT Researcher" really wanted to know. 🙂

    "you will normally get the database as it was when the statement started. But a row could be updated and committed after the statement started but you come around to read that row"  Any task running as SNAPSHOT will never see a version of the row from after the task's SQL statement started.  That is one of the key "promises" that SNAPSHOT makes to you when you implement it.

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

  • Read committed snapshot mode - can read last committed value,

    even if "begin transaction" is done after uncommitted "update" query - tested.

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

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