April 12, 2016 at 7:20 pm
I am into a situation where I am seeing of lot of deadlock / blocking . I have inherited a SP in which the whole code is within BEGIN TRAN and COMMIT Tran , I know that this is not a great idea , but then it is there for god knows how long . When this transaction is executing it locks the whole table until it gets committed . There are other SP which are doing a SELECT on the table in question ( on which transaction is running ) , since the SELECT statement is not having WITH NOLOCK it waits till the transaction is completed . This sometime leads to deadlock .
Question is : Since I am having isolation level as "Read committed" , what is downside of using the NO LOCK with the SELECT ?
April 13, 2016 at 2:14 am
himanshu.sinha (4/12/2016)
I am into a situation where I am seeing of lot of deadlock / blocking . I have inherited a SP in which the whole code is within BEGIN TRAN and COMMIT Tran , I know that this is not a great idea , but then it is there for god knows how long . When this transaction is executing it locks the whole table until it gets committed . There are other SP which are doing a SELECT on the table in question ( on which transaction is running ) , since the SELECT statement is not having WITH NOLOCK it waits till the transaction is completed . This sometime leads to deadlock .Question is : Since I am having isolation level as "Read committed" , what is downside of using the NO LOCK with the SELECT ?
First: Why do you think that using a transaction in a stored procedure is a bad practice? It is actually a good practice, unless done incorrectly.
Second: You ask what the downside of using NOLOCK is. The answer is that you can get various incorrect results from it.
The most well known is that you can read uncommitted data - the procedure changes some data, you read the changed data. But what if the procedure then later runs into an error because the data is wrong and the transaction rolls back? Your SELECT will have seen data that logically never existed. Also, if twto rows are modified in the transaction (e.g. because money is transfered from one account to another), you can read the "old" version for one row and the "new" version for the other.
Less known is that you can read some rows twice, or you can skip some rows, even when those rows themselves are not modified at all. This is a side effect of page splits that cause data to move to other locations on the disk.
Very rare but still possible is gettinng a run-time error (I think the error number of 622), caused by data movement while a scan is in progress. This can happen if the SELECT tries to read a page just in the same moment when the modifying procedure unallocates that page. It is very rare, but you will still have to add try .. catch logic to handle the error in your front end code.
Third: You say that the procedure blocks all other access. That is to be expected if it actually changes all rows in the table. If it changes just a single row or a few rows, then with proper indexing it should be possible to make it block only concurrent queries that need that same data. Alternatively, you can also look into the row versioning isolation levels (SNAPSHOT and READ_COMMITTED_SNAPSHOT) that handle concurrency by providing readers with the last committed data instead of blocking.
April 13, 2016 at 2:56 am
himanshu.sinha (4/12/2016)
Question is : Since I am having isolation level as "Read committed" , what is downside of using the NO LOCK with the SELECT ?
In short. Incorrect results. If the users don't care that their data is wrong, it may be fine.
Why are you not considering read committed snapshot? (the advantages of nolock, without the incorrect data problem, for the price of higher usage in TempDB)
If you have deadlocks, diagnose and fix them. https://www.simple-talk.com/sql/performance/sql-server-deadlocks-by-example/
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 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply