July 6, 2021 at 10:15 am
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 ?
July 6, 2021 at 10:22 am
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]
July 6, 2021 at 12:06 pm
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 ?
July 6, 2021 at 12:10 pm
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]
July 6, 2021 at 2:57 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 6, 2021 at 3:26 pm
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
Change is inevitable... Change for the better is not.
July 6, 2021 at 6:16 pm
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]
July 6, 2021 at 6:24 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 6, 2021 at 6:31 pm
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]
July 6, 2021 at 10:31 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 9, 2021 at 5:42 am
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 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply