March 27, 2009 at 9:13 am
here is a scenario:
I have a table that is highly transactional, there is a backend process that will update certain columns in a row at a time.( there could be multiple processes updating multiple rows at a time too )
Concurrently, the end users will issue a 'select' and will be 'update'ing the same set of rows that the back end process may be operating on.
I was getting enormous DEADLOCKs before i tried NOLOCK on SELECTs and ROWLOCK on UPDATEs.
The DEADLOCKs considerably reduced. But they started appearing again slowly(may be the data volume now is huge).
How do i handle this scenarios with locks ?
Also, what is the effect of using NOLOCK on updates/how bad is this ?
Thanks in adv. for your suggestions.
March 27, 2009 at 9:41 am
Nolock doesn't really do anything to updates at all. Won't do what you need.
Deadlocks aren't caused by people/systems accessing the same data at the same time. They're caused by situations where processes cannot resolve locks.
For example, process A has to lock table X, and then lock table Y, while process B has to lock table Y, and then table X. If A has a lock on X and wants a lock on Y, but B has a lock on Y and wants a lock on X, neither one can finish, thus a "deadlock" occurs, and SQL kills one of them.
The main way to solve that is to make sure that processes access and lock data in the same sequence as each other. No deadlocks that way.
I'd definitely analyze the deadlocks, find what's causing them, and fix that, rather than just taking the "nolock shotgun" and hoping you hit something with it. Using nolock as a solution for deadlocks is just asking for dirty reads and junked up data. Lots of people are taught to use nolock for this kind of thing, but aren't made aware of the fact that it can result in serious data problems.
For example, user A takes a look at row 3 of table X. Meanwhile, process B updates that row, but user A never sees the update, because of nolock and such. So user A updates the row, overwriting what process B did. Now, is that row of data correct? Process B's update is gone, as if it had never been done, and user A may have wanted a different update than the one he did, if he knew the data was being changed by process B. It can make a big mess.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 27, 2009 at 1:24 pm
GSquared (3/27/2009)
Nolock doesn't really do anything to updates at all. Won't do what you need.For example, user A takes a look at row 3 of table X. Meanwhile, process B updates that row, but user A never sees the update, because of nolock and such. So user A updates the row, overwriting what process B did. Now, is that row of data correct? Process B's update is gone, as if it had never been done, and user A may have wanted a different update than the one he did, if he knew the data was being changed by process B. It can make a big mess.
Thanks for the response.
And yes, i understand what you're saying.
But my case is little different. There would be a dirty read but it doesn't matter.
Say User U updates columns a,b& c of table T, the backend process P updates the column d of the same table T.
Column d makes no sense to the user(say it is for some auditing purpose), it is not shown to the user, nobody other than the process updates it.
In this case, you see a deadlock here, right ?
As i said, i tried a nolock for selects and rowlock for updates.
here is what i did :
1) user reads a row <----- nolock here
2) user updates the row <----- rowlock here
3) backend process updates the same row <----- rowlock here
This still did not resolve the issue completely. Maybe i am not doing it the right way.
Can someone help pls ?
Thanks again.
March 27, 2009 at 2:57 pm
A question gents :
If i place a NOLOCK for an update on a row (Update triggered by the background process) Say Transaction 1
If there is a ROWLOCK for another update on the same row (user updates a column) Say Transaction 2
What would happen now ?
I do not care about dirty reads. My question is : would both the updates go through ?
March 30, 2009 at 8:58 am
Two simultaneous updates to the same row of the same table isn't a deadlock. Would have a block, and resolve the updates sequentially, but it's not a deadlock. Take a look at deadlocks in Books Online, it'll explain it pretty clearly, but it's definitely not what you're describing.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 30, 2009 at 8:59 am
Santa Ana (3/27/2009)
A question gents :If i place a NOLOCK for an update on a row (Update triggered by the background process) Say Transaction 1
If there is a ROWLOCK for another update on the same row (user updates a column) Say Transaction 2
What would happen now ?
I do not care about dirty reads. My question is : would both the updates go through ?
"Nolock" doesn't apply to updates or deletes. Only to selects. If you issue two update commands, one will finish, and then the other will start. Simple as that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 30, 2009 at 12:42 pm
GSquared (3/30/2009)
Santa Ana (3/27/2009)
A question gents :If i place a NOLOCK for an update on a row (Update triggered by the background process) Say Transaction 1
If there is a ROWLOCK for another update on the same row (user updates a column) Say Transaction 2
What would happen now ?
I do not care about dirty reads. My question is : would both the updates go through ?
"Nolock" doesn't apply to updates or deletes. Only to selects. If you issue two update commands, one will finish, and then the other will start. Simple as that.
Thanks for the reply. Sorry for my ignorance i got it right now. Thanks.
Few of my SELECT statements are becoming deadlock victims. (i'm using sql server 2005). I cannot understand why a SELECT statement that has NOLOCK would end up as a dead lock victim.
When it has a NOLOCK, what is it competing with or who is locking this select from reading something ? I have a "NOLOCK" for selects as i said earlier gents.
A deadlock occurs when Tran A holds something that Tran B needs and vice-versa.
When i exclusively force Tran A to lock/hold nothing by using the NOLOCK keyword, how can this be a participant of a DEADLOCK ? <--- I don't understand this scenario in particular.
Can someone throw some light on this please ?
March 30, 2009 at 2:38 pm
Also,
Since i am using the ROWLOCK for updates : what is the effect of say 50 row level locks in a table at a time ?
March 31, 2009 at 7:19 am
What you need to do is trace the deadlocks and find what's competing with what. The deadlocks portion of Books Online has instructions on how to do that. If you have problems with that (BOL isn't always crystal clear on stuff), ask and we'll try to help, but it's a pretty simple process. Once you have the exact data on what it deadlocking with what, you'll have a better idea of what to handle.
A word of warning on all of this: Most of the time, factually almost always, it's better to let SQL Server handle your locking itself rather than forcing the issue with a lot of "hints". Don't let using the locking hints become a standard practice. Lots of developers do, and they almost always make the database worse rather than better. In 9 years, I've used locking hints twice to good effect, and seen them mess things up more times than I can count.
On the question about 50 separate row locks, SQL Server can handle that. If some of them conflict with each other, you'll end up with block/deadlock issues.
On the question about nolock selects being deadlocked, first, they can be deadlocked by something else that's locking resources they need, second, if the select is part of an update/delete/insert, nolock won't actually do what you expect.
Say, for example, that row 1 is being updated, and a query wants to select it. It won't be able to, even if it uses "nolock", until the update is done. What you would need to do to make it read the partially updated row, is set the query to "read uncommitted" isolation. That would work, but is, again, almost always a bad idea.
So, trace the deadlocks, find the exact things that are causing them, and you'll be most of the way to solving this. Let us know if you need help on that. Okay?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 31, 2009 at 8:35 am
GSquared (3/31/2009)
On the question about nolock selects being deadlocked, first, they can be deadlocked by something else that's locking resources they need, second, if the select is part of an update/delete/insert, nolock won't actually do what you expect.
Thank you so much for the detailed reply GSquared. Your answers cleared many of my doubts but one last one 🙂
I was little confused over this, when i say select with nolock, i was really believing that this select statement WILL NEVER BE a participant of a DEADLOCK because the rows that are selected by this transaction are not locked by this select transaction.
I was assuming that a nolock select actually means :- select my data regardless of any type of lock** that is imposed on it(data).
** = Lock for this select/lock for any other update if happening at the same time
but from your reply, i infer
nolock on select = SQL server will not impose locks that it will usually do on selects, period. (a nolock select doesn't mean that the other locks-update/insert/delete will be ignored too)
Is the above true ?
Thanks again!!
April 1, 2009 at 6:54 am
That's correct. To achieve that, you have to set the isolation level to "read uncommitted". It's a related, but slightly different, thing.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply