Dead Lock

We are using SQLserver 2000 SP3 Standard Version. We are having dead lock and
following is log about deadlock
2006-04-12 10:57:05.31 spid4 Wait-for graph
2006-04-12 10:57:05.31 spid4
2006-04-12 10:57:05.31 spid4 Node:1
2006-04-12 10:57:05.31 spid4 KEY: 8:1221032277:53 (480260485b26)
CleanCnt:1 Mode: S Flags: 0x0
2006-04-12 10:57:05.31 spid4 Grant List 1::
2006-04-12 10:57:05.31 spid4 Owner:0x66a25520 Mode: S Flg:0x0
Ref:0 Life:00000001 SPID:528 ECID:0
2006-04-12 10:57:05.31 spid4 SPID: 528 ECID: 0 Statement Type: SELECT
Line #: 38
2006-04-12 10:57:05.31 spid4 Input Buf: Language Event: Exec
dbo.VB_ConflictsAppointmentsByDate
@.StartDate='04/12/2006',@.EndDate='04/12/2006',@.Discipline='',
@.Location=,@.ResourceID='000150',@.S_Start=156,@.S_End=168
2006-04-12 10:57:05.31 spid4 Requested By:
2006-04-12 10:57:05.31 spid4 ResType:LockOwner Stype:'OR' Mode: X
SPID:137 ECID:0 Ec:(0x49C09530) Value:0x60540ac0 Cost:(0/BC)
2006-04-12 10:57:05.31 spid4
2006-04-12 10:57:05.31 spid4 Node:2
2006-04-12 10:57:05.31 spid4 KEY: 8:1221032277:1 (b3016336158a)
CleanCnt:1 Mode: X Flags: 0x0
2006-04-12 10:57:05.31 spid4 Grant List 2::
2006-04-12 10:57:05.31 spid4 Owner:0x60541880 Mode: X Flg:0x0
Ref:0 Life:02000000 SPID:137 ECID:0
2006-04-12 10:57:05.31 spid4 SPID: 137 ECID: 0 Statement Type: UPDATE
Line #: 434
2006-04-12 10:57:05.31 spid4 Input Buf: RPC Event: sp_prepexec;1
2006-04-12 10:57:05.31 spid4 Requested By:
2006-04-12 10:57:05.31 spid4 ResType:LockOwner Stype:'OR' Mode: S
SPID:528 ECID:0 Ec:(0x7E0EB508) Value:0x675dfc20 Cost:(0/0)
2006-04-12 10:57:05.31 spid4 Victim Resource Owner:
2006-04-12 10:57:05.31 spid4 ResType:LockOwner Stype:'OR' Mode: S
SPID:528 ECID:0 Ec:(0x7E0EB508) Value:0x675dfc20 Cost:(0/0)
SPID 137 is updating the row which SPID 528 truying to access (This SP is
only selecting the rows no update) I specified the UPDLOCK lock hint in
update statment with no improvement.
Any help is appreciated.
--
Farhan"Farhan Soomro" <FarhanSoomro@.discussions.microsoft.com> wrote in message
news:E43DE237-5437-4BE0-AA3D-80B14FD67B9C@.microsoft.com...
> We are using SQLserver 2000 SP3 Standard Version. We are having dead lock
> and
> following is log about deadlock
> 2006-04-12 10:57:05.31 spid4 Wait-for graph
> 2006-04-12 10:57:05.31 spid4
> 2006-04-12 10:57:05.31 spid4 Node:1
> 2006-04-12 10:57:05.31 spid4 KEY: 8:1221032277:53 (480260485b26)
> CleanCnt:1 Mode: S Flags: 0x0
> 2006-04-12 10:57:05.31 spid4 Grant List 1::
> 2006-04-12 10:57:05.31 spid4 Owner:0x66a25520 Mode: S
> Flg:0x0
> Ref:0 Life:00000001 SPID:528 ECID:0
> 2006-04-12 10:57:05.31 spid4 SPID: 528 ECID: 0 Statement Type:
> SELECT
> Line #: 38
> 2006-04-12 10:57:05.31 spid4 Input Buf: Language Event: Exec
> dbo.VB_ConflictsAppointmentsByDate
> @.StartDate='04/12/2006',@.EndDate='04/12/2006',@.Discipline='',
> @.Location=,@.ResourceID='000150',@.S_Start=156,@.S_End=168
> 2006-04-12 10:57:05.31 spid4 Requested By:
> 2006-04-12 10:57:05.31 spid4 ResType:LockOwner Stype:'OR' Mode: X
> SPID:137 ECID:0 Ec:(0x49C09530) Value:0x60540ac0 Cost:(0/BC)
> 2006-04-12 10:57:05.31 spid4
> 2006-04-12 10:57:05.31 spid4 Node:2
> 2006-04-12 10:57:05.31 spid4 KEY: 8:1221032277:1 (b3016336158a)
> CleanCnt:1 Mode: X Flags: 0x0
> 2006-04-12 10:57:05.31 spid4 Grant List 2::
> 2006-04-12 10:57:05.31 spid4 Owner:0x60541880 Mode: X
> Flg:0x0
> Ref:0 Life:02000000 SPID:137 ECID:0
> 2006-04-12 10:57:05.31 spid4 SPID: 137 ECID: 0 Statement Type:
> UPDATE
> Line #: 434
> 2006-04-12 10:57:05.31 spid4 Input Buf: RPC Event: sp_prepexec;1
> 2006-04-12 10:57:05.31 spid4 Requested By:
> 2006-04-12 10:57:05.31 spid4 ResType:LockOwner Stype:'OR' Mode: S
> SPID:528 ECID:0 Ec:(0x7E0EB508) Value:0x675dfc20 Cost:(0/0)
> 2006-04-12 10:57:05.31 spid4 Victim Resource Owner:
> 2006-04-12 10:57:05.31 spid4 ResType:LockOwner Stype:'OR' Mode: S
> SPID:528 ECID:0 Ec:(0x7E0EB508) Value:0x675dfc20 Cost:(0/0)
> SPID 137 is updating the row which SPID 528 truying to access (This SP is
> only selecting the rows no update) I specified the UPDLOCK lock hint in
> update statment with no improvement.
> Any help is appreciated.
> --
It's hard to suggest a change that resolves teh deadlock with minimal
degradation of concurrency or correctness without a complete picture of the
database structures, transactions and concurreny requirements of the
application.
However as a general matter, if you escalate the locks taken by one of the
transactions to a table lock, the deadlock should will go away. Again this
may cause an unacceptable degradation in the concurrency of the applciation.
Another aproach to simply optimize the SELECT. If it can read less, lock
less, or use a different index, etc you might resolve the deadlock and
improve appliction performance to boot.
David|||Thanks David.
I was thinking on same line, update sp is very complex and hard to adjust
versus select.
Appreciated.
--
Farhan
"David Browne" wrote:
> "Farhan Soomro" <FarhanSoomro@.discussions.microsoft.com> wrote in message
> news:E43DE237-5437-4BE0-AA3D-80B14FD67B9C@.microsoft.com...
> > We are using SQLserver 2000 SP3 Standard Version. We are having dead lock
> > and
> > following is log about deadlock
> >
> > 2006-04-12 10:57:05.31 spid4 Wait-for graph
> > 2006-04-12 10:57:05.31 spid4
> > 2006-04-12 10:57:05.31 spid4 Node:1
> > 2006-04-12 10:57:05.31 spid4 KEY: 8:1221032277:53 (480260485b26)
> > CleanCnt:1 Mode: S Flags: 0x0
> > 2006-04-12 10:57:05.31 spid4 Grant List 1::
> > 2006-04-12 10:57:05.31 spid4 Owner:0x66a25520 Mode: S
> > Flg:0x0
> > Ref:0 Life:00000001 SPID:528 ECID:0
> > 2006-04-12 10:57:05.31 spid4 SPID: 528 ECID: 0 Statement Type:
> > SELECT
> > Line #: 38
> > 2006-04-12 10:57:05.31 spid4 Input Buf: Language Event: Exec
> > dbo.VB_ConflictsAppointmentsByDate
> > @.StartDate='04/12/2006',@.EndDate='04/12/2006',@.Discipline='',
> > @.Location=,@.ResourceID='000150',@.S_Start=156,@.S_End=168
> > 2006-04-12 10:57:05.31 spid4 Requested By:
> > 2006-04-12 10:57:05.31 spid4 ResType:LockOwner Stype:'OR' Mode: X
> > SPID:137 ECID:0 Ec:(0x49C09530) Value:0x60540ac0 Cost:(0/BC)
> > 2006-04-12 10:57:05.31 spid4
> > 2006-04-12 10:57:05.31 spid4 Node:2
> > 2006-04-12 10:57:05.31 spid4 KEY: 8:1221032277:1 (b3016336158a)
> > CleanCnt:1 Mode: X Flags: 0x0
> > 2006-04-12 10:57:05.31 spid4 Grant List 2::
> > 2006-04-12 10:57:05.31 spid4 Owner:0x60541880 Mode: X
> > Flg:0x0
> > Ref:0 Life:02000000 SPID:137 ECID:0
> > 2006-04-12 10:57:05.31 spid4 SPID: 137 ECID: 0 Statement Type:
> > UPDATE
> > Line #: 434
> > 2006-04-12 10:57:05.31 spid4 Input Buf: RPC Event: sp_prepexec;1
> > 2006-04-12 10:57:05.31 spid4 Requested By:
> > 2006-04-12 10:57:05.31 spid4 ResType:LockOwner Stype:'OR' Mode: S
> > SPID:528 ECID:0 Ec:(0x7E0EB508) Value:0x675dfc20 Cost:(0/0)
> > 2006-04-12 10:57:05.31 spid4 Victim Resource Owner:
> > 2006-04-12 10:57:05.31 spid4 ResType:LockOwner Stype:'OR' Mode: S
> > SPID:528 ECID:0 Ec:(0x7E0EB508) Value:0x675dfc20 Cost:(0/0)
> >
> > SPID 137 is updating the row which SPID 528 truying to access (This SP is
> > only selecting the rows no update) I specified the UPDLOCK lock hint in
> > update statment with no improvement.
> > Any help is appreciated.
> > --
> It's hard to suggest a change that resolves teh deadlock with minimal
> degradation of concurrency or correctness without a complete picture of the
> database structures, transactions and concurreny requirements of the
> application.
> However as a general matter, if you escalate the locks taken by one of the
> transactions to a table lock, the deadlock should will go away. Again this
> may cause an unacceptable degradation in the concurrency of the applciation.
> Another aproach to simply optimize the SELECT. If it can read less, lock
> less, or use a different index, etc you might resolve the deadlock and
> improve appliction performance to boot.
> David
>
>