March 7, 2017 at 9:03 pm
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!)
March 7, 2017 at 11:26 pm
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”
March 8, 2017 at 12:24 am
Uwe Ricken - Tuesday, March 7, 2017 9:03 PMComments 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?
.
March 8, 2017 at 1:33 am
My understanding of READ COMMITTED SNAPSHOT isolation was obviously wrong I thought the opposite, that it prevented locking errors. Doh!
March 8, 2017 at 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.
March 8, 2017 at 3:35 am
Mighty - Wednesday, March 8, 2017 3:30 AMTechnically 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.
March 8, 2017 at 6:41 am
Terje Hermanseter - Wednesday, March 8, 2017 12:24 AMUwe Ricken - Tuesday, March 7, 2017 9:03 PMComments posted to this topic are about the item Isolation Level SERIALIZABLEI 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'
COMMITDuring 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)
March 8, 2017 at 7:17 am
Marek Grzymala - Wednesday, March 8, 2017 6:41 AMTerje Hermanseter - Wednesday, March 8, 2017 12:24 AMUwe Ricken - Tuesday, March 7, 2017 9:03 PMComments posted to this topic are about the item Isolation Level SERIALIZABLEI 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'
COMMITDuring 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
March 8, 2017 at 8:02 am
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.
MattF
March 9, 2017 at 1:24 am
But isn't that what READ COMMITTED SNAPSHOT Isolation is supposed to allow, i.e. read committed data without locking it?
March 9, 2017 at 9:24 am
BillLudlow - Thursday, March 9, 2017 1:24 AMBut 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."
MattF
March 24, 2017 at 1:57 pm
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
April 27, 2017 at 12:11 pm
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 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply