Last committed value in sql

Question

Post reply

Last committed value in sql

IT researcher

SSCertifiable

Points: 7567
More actions
July 6, 2021 at 10:15 am

#3903839

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 ?

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

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

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 1

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]

IT researcher SSCertifiable Points: 7567 More actions · Answer 2

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 ?

This reply was modified 2 years, 9 months ago by IT researcher.

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 3

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]

ScottPletcher SSC Guru Points: 100948 More actions · Answer 4

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) A socialist is someone who will give you the shirt off *someone else's* back.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 5

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)

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 6

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]

ScottPletcher SSC Guru Points: 100948 More actions · Answer 7

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) A socialist is someone who will give you the shirt off *someone else's* back.

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 8

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]

ScottPletcher SSC Guru Points: 100948 More actions · Answer 9

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) A socialist is someone who will give you the shirt off *someone else's* back.

IT researcher SSCertifiable Points: 7567 More actions · Answer 10

Read committed snapshot mode - can read last committed value,

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