May 22, 2012 at 4:58 pm
I have a stored procedure that sets transaction isolation level read uncommitted. Is it possible to know if the results contain a dirty/phantom read?
May 22, 2012 at 6:24 pm
I don't believe this is possible. When a transaction is executing under the READ UNCOMMITTED transaction isolation level, it basically just doesn't issue shared locks. I don't think there is a tried and true method to detecting concurrency anomalies like dirty reads, phantom reads, and non repeatable reads.
May 22, 2012 at 11:30 pm
In read committed and repeatable read a demo to highlight dirty reads is shown below. It is costly as two scans are required to retrieve the result.
-- User 1
create database LockingTest;
go
use LockingTest;
go
create table dbo.LockingRows(
ID int primary key,
Name varchar(30)
)
go
insert into dbo.LockingRows values (1,'John')
insert into dbo.LockingRows values (2,'Eric')
insert into dbo.LockingRows values (3,'Paul')
insert into dbo.LockingRows values (4,'Simon')
insert into dbo.LockingRows values (5,'Charles')
select * from dbo.LockingRows
-- create block
begin tran
update dbo.LockingRows
set Name = 'Simone'
where ID = 4
insert into LockingRows values (6,'Xavier')
User 2:
-- Show "dirty" read status
select case when RP.ID IS NULL then 'Dirty' else '' end as Status,NL.*
from LockingRows (nolock) as NL
left join LockingRows (readpast) as RP
on NL.ID = Rp.ID
This uses the table under both the readpast and readuncommitted locks. The difference between the two sets would be the dirty rows. So performing a left join on the primary key would allow the result you want.
Unfortunately, if you your connection is using read uncommitted isolation level by default then an error will occur on the above code.
Msg 650, Level 16, State1, Line1
You can only specify the READPAST lock in the READ COMMITTED and REPEATABLE READ isolation levels.
Fitz
May 22, 2012 at 11:52 pm
Thank you very much for your reply Fitz. Will test this tomorrow, didnt know about this optimizer hint.
From your experience, is there any overhead for the server when using readpast?
May 22, 2012 at 11:58 pm
The readpast hint is not costly in itself as ignores rows with locks. As stated before though in does not work if you have set the transaction isolation level to read uncommitted.
Fitz
May 23, 2012 at 12:03 am
Thank you, this wont be an issue in this case. The app just calls the SP and the isolation level is set inside of it.
May 23, 2012 at 12:36 am
Alexander-449406 (5/22/2012)
I have a stored procedure that sets transaction isolation level read uncommitted. Is it possible to know if the results contain a dirty/phantom read?
No. The engine delivers the transaction semantics you ask for. If you don't want dirty reads, don't use READ UNCOMMITTED. If you don't want phantoms, use SERIALIZABLE or SNAPSHOT.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 23, 2012 at 12:39 am
Mark Fitzgerald-331224 (5/22/2012)
The difference between the two sets would be the dirty rows.
This works as a demo, but it is not a practical dirty-read detection solution.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 23, 2012 at 2:02 am
SQL Kiwi (5/23/2012)
Mark Fitzgerald-331224 (5/22/2012)
The difference between the two sets would be the dirty rows.This works as a demo, but it is not a practical dirty-read detection solution.
Agreed. In the first post I put that this is only a demo that customers sometimes ask for. Getting the isolation level correct is the way to go. Personally, I did not know before I created this demo that readpast was restricted to only read committed and repeatable read.
It is a possible if not practical solution to reading the data from a table and noting the locked / dirty read rows. Using the execution plan a double table scan is shown, and this would seriously hamper the practicality of this is larger tables. The OP did not state the reason for using the read uncommitted (original post) and I assumed that the isolation level was not changeable in his application.
Fitz
May 23, 2012 at 4:53 am
this is something that itzik ben-gan demos at some of conferences he talks at
he's doesn't say don't use read uncommitted, just know when and where to use it and be aware of the risk (albeit a risk that is very rarely actually hits anyone)
if you are interested in the subject then there is lots of material
http://michaeljswart.com/tag/nolock-read-uncommitted/
http://www.windowsitpro.com/article/news2/beware-the-nolock-hint
http://www.sqlmag.com/home/search.aspx?query=uncommitted&ctype=articles
MVDBA
May 23, 2012 at 4:58 am
michael vessey (5/23/2012)
this is something that itzik ben-gan demos at some of conferences he talks athe's doesn't say don't use read uncommitted, just know when and where to use it and be aware of the risk (albeit a risk that is very rarely actually hits anyone)
if you are interested in the subject then there is lots of material
http://michaeljswart.com/tag/nolock-read-uncommitted/
http://www.windowsitpro.com/article/news2/beware-the-nolock-hint
http://www.sqlmag.com/home/search.aspx?query=uncommitted&ctype=articles
Love to attend one of those somewhen. (Boss man - hint hint).
Fitz
May 23, 2012 at 10:40 am
Mark Fitzgerald-331224 (5/23/2012)
It is a possible if not practical solution to reading the data from a table and noting the locked / dirty read rows. Using the execution plan a double table scan is shown, and this would seriously hamper the practicality of this is larger tables.
Yes; my point is that the locks held and row contents may not be the same when each of the two scans touches the same row (concurrency is a tricky thing).
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply