Key locks and Deadlocks

I don't know if this is the best place to post this, if not, please
advise...
I have a SQL 2000 production db that the users of a VB 6.0 app occasionally
see deadlock errors. Like so many times, it's not reproducible, but it
happens more when they are really busy (Thanks, Murf!).
In running the performance Monitor, I see that a significant number (over
10,000) key locks get generated during some queries. I have run the Sql
Analyyzer and cannot find any specific transaction or sequence of events
that results in the deadlock.
My theory is the deadlocking is coming from page locks, and each user
contends just a little too much for some pages.
The other possibility is that the key locks, being so numerous could be
blocking and causing the deadlock victims transaction to roll back.
So, my question is two-fold:
1) can Key lock contention cause deadlocks? If so, how do I reduce keylock
use?
2) Is there a way to control or force row locking when using ADO, VB6 style;
specifically when using the .Update method on an ADO recordset (I realize I
could re-write all the updates to be via striaght SQL, so I could insert my
own hints regarding rowlocking, and yes, I already have a post in
public.data.ado message group on this).
Thanks!
Steve
Steve Byrne wrote:
> I don't know if this is the best place to post this, if not, please
> advise...
> I have a SQL 2000 production db that the users of a VB 6.0 app
> occasionally see deadlock errors. Like so many times, it's not
> reproducible, but it happens more when they are really busy (Thanks,
> Murf!).
> In running the performance Monitor, I see that a significant number
> (over 10,000) key locks get generated during some queries. I have run
> the Sql Analyyzer and cannot find any specific transaction or
> sequence of events that results in the deadlock.
> My theory is the deadlocking is coming from page locks, and each user
> contends just a little too much for some pages.
> The other possibility is that the key locks, being so numerous could
> be blocking and causing the deadlock victims transaction to roll back.
> So, my question is two-fold:
> 1) can Key lock contention cause deadlocks? If so, how do I reduce
> keylock use?
> 2) Is there a way to control or force row locking when using ADO, VB6
> style; specifically when using the .Update method on an ADO recordset
> (I realize I could re-write all the updates to be via striaght SQL,
> so I could insert my own hints regarding rowlocking, and yes, I
> already have a post in public.data.ado message group on this).
> Thanks!
> Steve
See this page about identifying and resolving deadlocks:
http://support.microsoft.com/?kbid=832524
The first step is to see what transactions are responsible for the
deadlocks. To resolve the deadlock, you should:
- Make sure all transactions involved are fully optimized
- Access objects in the same order in all transactions
- Keep the transactions short - (fetch all result set data immediately
and avoid leaving locks on the server)
- Use the lowest level isolation level possible (READ COMMITTED, READ
UNCOMMITTED, REPEATABLE READ, and then SERIALIZABLE)
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Also try with (nolock) for dirty reads.
Helps greatly on a heavy OLTP server and you don't need "to the second"
accuracy.
Mike
"Steve Byrne" <steveb@.ssninc.com> wrote in message
news:OJ9SZn9sFHA.2348@.tk2msftngp13.phx.gbl...
I don't know if this is the best place to post this, if not, please
advise...
I have a SQL 2000 production db that the users of a VB 6.0 app occasionally
see deadlock errors. Like so many times, it's not reproducible, but it
happens more when they are really busy (Thanks, Murf!).
In running the performance Monitor, I see that a significant number (over
10,000) key locks get generated during some queries. I have run the Sql
Analyyzer and cannot find any specific transaction or sequence of events
that results in the deadlock.
My theory is the deadlocking is coming from page locks, and each user
contends just a little too much for some pages.
The other possibility is that the key locks, being so numerous could be
blocking and causing the deadlock victims transaction to roll back.
So, my question is two-fold:
1) can Key lock contention cause deadlocks? If so, how do I reduce keylock
use?
2) Is there a way to control or force row locking when using ADO, VB6 style;
specifically when using the .Update method on an ADO recordset (I realize I
could re-write all the updates to be via striaght SQL, so I could insert my
own hints regarding rowlocking, and yes, I already have a post in
public.data.ado message group on this).
Thanks!
Steve
|||Mike Perino wrote:
> Also try with (nolock) for dirty reads.
> Helps greatly on a heavy OLTP server and you don't need "to the
> second" accuracy.
Or you can handle reading data that exists now, but is rolled back
afterwards. Essentially, data that never existed. I agree, though, that
any application that does not require accurate data be returned for a
query should consider using this locking hint. For example, a query that
returns estimated totals sales for the month.
David Gugick
Quest Software
www.imceda.com
www.quest.com