Are you using WITH (NOLOCK) as a hint for performance or avoid deadlock in select statements all the times?
This is not the first time this old timer has hit me, I can recall another blunder. We were writing a flight planning module which used to help training pilots for touch down. Everyday pilots used to update their system in both simulator and in actual flight system from main database. One day a guy thought it would be fun update both simulator and flight system along with history update so that he can save time. But his fun went away when he open the flight system in during flight to bring back his plan since his monitor was displaying two overlapping path to bring it back , it was his first officer by his side who bring back the plan safely and make his day and eventually our project went into jeopardy . The postmortem reveals something calls “Dirty read” and once again with (No lock) was the culprit.
So does with (no lock) is really that bad. Look into following example.
Run the following code snippet in Query Analyzer:
create table posts (id int identity primary key, [content] varchar(7000), [group] int, date_changed datetime)
create index idx_date_changed on posts ([group], date_changed)
insert posts values ('post contents', 1, getdate())
declare @i int
set @i = 1
while @i < 5000
begin
insert posts values ('post contents', @i, getdate())
set @i = @i + 1
end
Open two Query Analyzer windows, in the first type:
set nocount on
declare @i int
set @i = 1
while @i < 500000
begin
-- use a temp table to avoid filling the query analyzer window with results
select * into #t from posts
where [group] = 1
drop table #t
end
In the second one:
update posts
set [date_changed] = getdate()
where id = 2
Start the first query. Execute the second query a few times (it may be once, may be 20 times). Look at your messages on the SELECT loop, you should see something like the following:
Msg 1205, Level 13, State 51, Line 7
Transaction (Process ID 57) was deadlocked on lock resources with
another process and has been chosen as the deadlock victim.
Rerun the transaction.
So, you may ask yourself whats going on.
First lets look at the execution plans:
select * from posts where [group] = 1
The execution plan for the SELECT statement reveals that we will first look up some records in the idx_date_changed index and then look up more data in the clustered index and join it up. The key here is order we are looking up data in the two indexes.
Next we should look at the execution plan for the UPDATE statement:
update posts set [date_changed] = getdate() where id = 2
The execution plan for the UPDATE statement reveals we first perform a clustered index seek on the primary key and then an index update on the idx_date_changed index.
Notice that both queries look up the data in the indexes in the reverse order, which gives us more chance of having deadlocks.
A handy trick when debugging deadlock issues is determining which locks each statement acquires. To do this you can hold the locks and wrap it in an open transaction
begin tran
update posts with (holdlock) set [date_changed] = getdate() where id = 2
Then in a second window you can execute sp_lock which returns the list of active locks.
Remember to commit that transaction later on…
In real life the first step is determining the two statement that deadlock.
What can happen if you use the NOLOCK hint
Well the worst case scenario is that you may, once in a while see phantom data, duplicate data or have a bunch of missing rows. In a most web apps this can be acceptable, but it can look very unprofessional.
If you used the NOLOCK hack in a banking application you would probably get fired on the spot.
A real example could be some database maintenance that requires a big fat transaction that say removes all the rows from a table and then adds the rows back in. In this case, users may get a blank front page on your website. There are lots of less subtle issues that may pop up in the e-commerce world such as: billing a person twice, billing the wrong amount, adding a wrongly priced item to a basket etc…
The internet is full of recommendation of using no lock in scenarios where update and select come one to one . I think this is almost always the wrong conclusion. I think that in practice it is very dangerous.
Why SELECT statements sometimes deadlock against UPDATE statements
The theoretical explanation is this: regardless of explicit transactions SQL Server tries to stay Atomic, Consistent, Isolated and Durable. The default isolation level for all SELECT statements is READ COMMITTED. To achieve this SQL Server uses “locks”:
http://msdn.microsoft.com/en-us/library/aa213039(SQL.80).aspx .
SELECT statements acquire shared locks on various resources. These resources may be pages in the database where the data is stored or keys in indexes and so forth. These locks may or may not be held for entire duration of the SELECT statement depending on various circumstances. Most of the time MSSql needs to acquire more than one lock to proceed with a portion of the SELECT statement. For example, it may need a shared lock on an index AND a lock on a page in the database to return results. In such cases MSSql determines the order of locks it needs to acquire and acquires them in a what appears to be linear fashion.
And this is where all the trouble starts. Say we have 2 database connections:
# Connection 1 : UPDATE statement acquire an exclusive lock on a key on index #1 # Connection 2 : SELECT statement acquires a shared lock on page #1 in the database # Connection 1 : UPDATE statement attempts to acquire an exclusive lock on page #1, since someone else is already holding a shared lock, it starts waiting. # Connection 2 : SELECT statement tries to acquire a shared lock on index #1, since someone else is already holding an exclusive lock, it start waiting. # MSSql figures out that we have a deadlock, kills the SELECT statement and raises an error message.
If not no lock then what
The cowardly yet honest answer to this is: “it depends”. First things first. Reproduce the problem in an isolated test harness so you have something to work with. Without this you will be shooting in the dark.
Here are a few approaches that may help.
§ Optimizing Queries
Are you joining to an unnecessary table? Are you returning too much data (too many columns or rows)? Is your query performing table scans? Can you restructure the query in such a way that it no longer deadlocks? Are you using a left join where you should be using a join? Have you reviewed your NOT IN clauses?
§ Optimizing Indexes
This ties in to the previous point, having too many, too few or wrong indexes can hurt performance. Are there any missing or superfluous covering indexes? Are there any missing indexes? Are there any duplicate indexes?
§ Handling the deadlocks
You may not be able or willing to resolve every deadlock you have. In such cases consider handling the deadlocks in the app, logging the fact it happened and retrying. These logs will be a goldmine when you go about designing your next version.
§ Caching
Perhaps you are executing lots of the same queries very frequently. Cutting down the number of times that query executes will reduce the chances of deadlocks occurring. This may or may not create issues with you serving out stale data depending on the caching architecture. The caching can be implemented either in the database (using temp tables or tables) or in the application; in-memory or on disk. It really depends on the problem. In web apps you may sometimes want to cache whole pages on disk or in memory.
§ Application architecture review
An application architecture review may point out that a query that is deadlocking is being called 10 times per request when it should only be called once. It may reveal that the data is not really required or that the same feature is implemented efficiently in a different code branch.
Conclusion
Feel free to disagree but In my opinion when writing select queries never use no lock by default .It should be used minimum and only if it’s actually required. There are no specifications from vendors to use it as a performance tool. Use it and be ready for some “Ichhadhari Bug” and sleepless nights.
This is not the first time this old timer has hit me, I can recall another blunder. We were writing a flight planning module which used to help training pilots for touch down. Everyday pilots used to update their system in both simulator and in actual flight system from main database. One day a guy thought it would be fun update both simulator and flight system along with history update so that he can save time. But his fun went away when he open the flight system in during flight to bring back his plan since his monitor was displaying two overlapping path to bring it back , it was his first officer by his side who bring back the plan safely and make his day and eventually our project went into jeopardy . The postmortem reveals something calls “Dirty read” and once again with (No lock) was the culprit.
So does with (no lock) is really that bad. Look into following example.
Run the following code snippet in Query Analyzer:
create table posts (id int identity primary key, [content] varchar(7000), [group] int, date_changed datetime)
create index idx_date_changed on posts ([group], date_changed)
insert posts values ('post contents', 1, getdate())
declare @i int
set @i = 1
while @i < 5000
begin
insert posts values ('post contents', @i, getdate())
set @i = @i + 1
end
Open two Query Analyzer windows, in the first type:
set nocount on
declare @i int
set @i = 1
while @i < 500000
begin
-- use a temp table to avoid filling the query analyzer window with results
select * into #t from posts
where [group] = 1
drop table #t
end
In the second one:
update posts
set [date_changed] = getdate()
where id = 2
Start the first query. Execute the second query a few times (it may be once, may be 20 times). Look at your messages on the SELECT loop, you should see something like the following:
Msg 1205, Level 13, State 51, Line 7
Transaction (Process ID 57) was deadlocked on lock resources with
another process and has been chosen as the deadlock victim.
Rerun the transaction.
So, you may ask yourself whats going on.
First lets look at the execution plans:
select * from posts where [group] = 1
The execution plan for the SELECT statement reveals that we will first look up some records in the idx_date_changed index and then look up more data in the clustered index and join it up. The key here is order we are looking up data in the two indexes.
Next we should look at the execution plan for the UPDATE statement:
update posts set [date_changed] = getdate() where id = 2
The execution plan for the UPDATE statement reveals we first perform a clustered index seek on the primary key and then an index update on the idx_date_changed index.
Notice that both queries look up the data in the indexes in the reverse order, which gives us more chance of having deadlocks.
A handy trick when debugging deadlock issues is determining which locks each statement acquires. To do this you can hold the locks and wrap it in an open transaction
begin tran
update posts with (holdlock) set [date_changed] = getdate() where id = 2
Then in a second window you can execute sp_lock which returns the list of active locks.
Remember to commit that transaction later on…
In real life the first step is determining the two statement that deadlock.
What can happen if you use the NOLOCK hint
Well the worst case scenario is that you may, once in a while see phantom data, duplicate data or have a bunch of missing rows. In a most web apps this can be acceptable, but it can look very unprofessional.
If you used the NOLOCK hack in a banking application you would probably get fired on the spot.
A real example could be some database maintenance that requires a big fat transaction that say removes all the rows from a table and then adds the rows back in. In this case, users may get a blank front page on your website. There are lots of less subtle issues that may pop up in the e-commerce world such as: billing a person twice, billing the wrong amount, adding a wrongly priced item to a basket etc…
The internet is full of recommendation of using no lock in scenarios where update and select come one to one . I think this is almost always the wrong conclusion. I think that in practice it is very dangerous.
Why SELECT statements sometimes deadlock against UPDATE statements
The theoretical explanation is this: regardless of explicit transactions SQL Server tries to stay Atomic, Consistent, Isolated and Durable. The default isolation level for all SELECT statements is READ COMMITTED. To achieve this SQL Server uses “locks”:
http://msdn.microsoft.com/en-us/library/aa213039(SQL.80).aspx .
SELECT statements acquire shared locks on various resources. These resources may be pages in the database where the data is stored or keys in indexes and so forth. These locks may or may not be held for entire duration of the SELECT statement depending on various circumstances. Most of the time MSSql needs to acquire more than one lock to proceed with a portion of the SELECT statement. For example, it may need a shared lock on an index AND a lock on a page in the database to return results. In such cases MSSql determines the order of locks it needs to acquire and acquires them in a what appears to be linear fashion.
And this is where all the trouble starts. Say we have 2 database connections:
# Connection 1 : UPDATE statement acquire an exclusive lock on a key on index #1 # Connection 2 : SELECT statement acquires a shared lock on page #1 in the database # Connection 1 : UPDATE statement attempts to acquire an exclusive lock on page #1, since someone else is already holding a shared lock, it starts waiting. # Connection 2 : SELECT statement tries to acquire a shared lock on index #1, since someone else is already holding an exclusive lock, it start waiting. # MSSql figures out that we have a deadlock, kills the SELECT statement and raises an error message.
If not no lock then what
The cowardly yet honest answer to this is: “it depends”. First things first. Reproduce the problem in an isolated test harness so you have something to work with. Without this you will be shooting in the dark.
Here are a few approaches that may help.
§ Optimizing Queries
Are you joining to an unnecessary table? Are you returning too much data (too many columns or rows)? Is your query performing table scans? Can you restructure the query in such a way that it no longer deadlocks? Are you using a left join where you should be using a join? Have you reviewed your NOT IN clauses?
§ Optimizing Indexes
This ties in to the previous point, having too many, too few or wrong indexes can hurt performance. Are there any missing or superfluous covering indexes? Are there any missing indexes? Are there any duplicate indexes?
§ Handling the deadlocks
You may not be able or willing to resolve every deadlock you have. In such cases consider handling the deadlocks in the app, logging the fact it happened and retrying. These logs will be a goldmine when you go about designing your next version.
§ Caching
Perhaps you are executing lots of the same queries very frequently. Cutting down the number of times that query executes will reduce the chances of deadlocks occurring. This may or may not create issues with you serving out stale data depending on the caching architecture. The caching can be implemented either in the database (using temp tables or tables) or in the application; in-memory or on disk. It really depends on the problem. In web apps you may sometimes want to cache whole pages on disk or in memory.
§ Application architecture review
An application architecture review may point out that a query that is deadlocking is being called 10 times per request when it should only be called once. It may reveal that the data is not really required or that the same feature is implemented efficiently in a different code branch.
Conclusion
Feel free to disagree but In my opinion when writing select queries never use no lock by default .It should be used minimum and only if it’s actually required. There are no specifications from vendors to use it as a performance tool. Use it and be ready for some “Ichhadhari Bug” and sleepless nights.
No comments:
Post a Comment