July 22, 2010 at 1:24 am
plz let me know small example
July 22, 2010 at 1:37 am
Do not use WITH NOLOCK on rows and tables.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
July 22, 2010 at 3:47 am
2 ways:
/* 1st */
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT * FROM MyTable
/* 2nd */
SELECT * FROM MyTable WITH (NOLOCK)
I hope this is what you're looking for.
July 22, 2010 at 5:45 am
Something along these lines. You have a table with a column, CityName. One row has a value 'Hyderabad.' But that's a bad value. So someone is running a query that looks like this:
BEGIN TRANSACTION
UPDATE MyTable
SET CityName = 'Puducherry'
WHERE CityName = 'Hyderabad'
This is beginning a transaction and updating the value, but right now, it's not committed. Someone else runs a query like this:
SELECT CityName
FROM MyTable WITH (NOLOCK)
What happens is, they get a "dirty" read, they will see the value 'Hyderabad' even though there is a transaction occurring that will make that value into 'Puducherry.' That's a very simple example of a dirty read. It's actually much more complicated than that. A dirty read can also cause you to get duplicate rows, extra rows, or miss rows of data, not just get that uncommitted column value. Dirty reads are quite dangerous if you're working on a system that needs real data integrity for the business (and most do).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 22, 2010 at 6:15 am
Henrico Bekker (7/22/2010)
Do not use row and table locks.
How would not using locks prevent dirty reads? You suggesting page locks only?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 22, 2010 at 6:38 am
GilaMonster (7/22/2010)
Henrico Bekker (7/22/2010)
Do not use row and table locks.How would not using locks prevent dirty reads? You suggesting page locks only?
tsk...I meant WITH NOLOCK...my bad.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
July 22, 2010 at 9:02 am
how can i prevent the dirty reads
July 22, 2010 at 10:01 am
At least use
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
in your transactions. Total guarantee of data accuracy (though it will cause issues with concurrency if the transaction is too long) would be
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
July 22, 2010 at 8:20 pm
Grant Fritchey (7/22/2010)
Something along these lines. You have a table with a column, CityName. One row has a value 'Hyderabad.' But that's a bad value. So someone is running a query that looks like this:
BEGIN TRANSACTIONUPDATE MyTable
SET CityName = 'Puducherry'
WHERE CityName = 'Hyderabad'
This is beginning a transaction and updating the value, but right now, it's not committed. Someone else runs a query like this:
SELECT CityNameFROM MyTable WITH (NOLOCK)
What happens is, they get a "dirty" read, they will see the value 'Hyderabad' even though there is a transaction occurring that will make that value into 'Puducherry.' That's a very simple example of a dirty read. It's actually much more complicated than that. A dirty read can also cause you to get duplicate rows, extra rows, or miss rows of data, not just get that uncommitted column value. Dirty reads are quite dangerous if you're working on a system that needs real data integrity for the business (and most do).
This explanation nails the question on this post. Read Grant's book. It has a chapter on Transactions and Locking. It's a book we DBAs have been waiting for.
July 23, 2010 at 4:51 am
Do not use row and table locks.
July 23, 2010 at 4:51 am
Do not use row and table locks.
July 23, 2010 at 4:57 am
sreedhar1.m (7/23/2010)
Do not use row and table locks.
A couple of questions. How do you go about not using locks? Aren't they pretty much required by the system when it updates data? Is it possible that you meant don't use lock hints?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 23, 2010 at 5:31 am
Grant Fritchey (7/23/2010)
sreedhar1.m (7/23/2010)
Do not use row and table locks.A couple of questions. How do you go about not using locks? Aren't they pretty much required by the system when it updates data? Is it possible that you meant don't use lock hints?
I think he's just repeating what Henrico said earlier in this thread.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply