Deadlock on Update Statement (NOLOCK)

I have the following updates statements in my stored procedure which caused
a deadlock. Should I take the (NOLOCK) statement out of the update
statements?
Is there some else I can to help resolve this deadlock?
Thanks,
Update SRA_FlowMaster
Set Status = 'I'
From SRA_FlowMaster New
Inner Join SRA_FlowMaster (NoLock)
On New. FlowMasterNo = SRA_FlowMaster. FlowMasterNo
And New.TypeCode = SRA_FlowMaster. TypeCode
WhereNew. FlowMasterID = @.i_FlowMasterID
And SRA_FlowMaster. FlowMasterID <> @.i_FlowMasterID
And SRA_FlowMaster. Status In ('K', 'M')
Update SRA_FlowMaster
Set Status = 'I'
From SRA_FlowMaster New
Inner Join SRA_FlowMaster (NoLock)
On New. FlowMasterNo = SRA_FlowMaster. FlowMaster
And New.TypeCode = SRA_FlowMaster. TypeCode
Where New. FlowMasterID = @.i_FlowMasterID
And SRA_FlowMaster. FlowMasterID <> @.i_FlowMasterID
And SRA_FlowMaster. Status = 'A'
And New. Status In ('A', 'D')Joe,
A deadlock involved two processes requesting a resource being locked by the
other. You have to identify the processes and the statements causing the
deadlock. The table hint you are using is not a hint to prevent deadlocks.
See "Minimizing Deadlocks" and "Troubleshooting Deadlocks" in BOL for more
information.
Tracing Deadlocks
http://www.sqlservercentral.com/col...ngdeadlocks.asp
AMB
"Joe K." wrote:

> I have the following updates statements in my stored procedure which cause
d
> a deadlock. Should I take the (NOLOCK) statement out of the update
> statements?
> Is there some else I can to help resolve this deadlock?
> Thanks,
>
> Update SRA_FlowMaster
> Set Status = 'I'
> From SRA_FlowMaster New
> Inner Join SRA_FlowMaster (NoLock)
> On New. FlowMasterNo = SRA_FlowMaster. FlowMasterNo
> And New.TypeCode = SRA_FlowMaster. TypeCode
> WhereNew. FlowMasterID = @.i_FlowMasterID
> And SRA_FlowMaster. FlowMasterID <> @.i_FlowMasterID
> And SRA_FlowMaster. Status In ('K', 'M')
> Update SRA_FlowMaster
> Set Status = 'I'
> From SRA_FlowMaster New
> Inner Join SRA_FlowMaster (NoLock)
> On New. FlowMasterNo = SRA_FlowMaster. FlowMaster
> And New.TypeCode = SRA_FlowMaster. TypeCode
> Where New. FlowMasterID = @.i_FlowMasterID
> And SRA_FlowMaster. FlowMasterID <> @.i_FlowMasterID
> And SRA_FlowMaster. Status = 'A'
> And New. Status In ('A', 'D')
>|||Hmm. Let me guess.
'Status' has only a handful of values.
You have an index on 'Status'.
Almost all of the values of 'Status' are the same.
This is a pretty common issue. Status fields are really a bad way to
represent and control status of an object, even thought they seem intuitive
at first. The key range locking required on an update makes it almost
impossible to scale to any reasonable level.
You can drop the index on Status but you will probably time out on some
other queries. (NOLOCK) hints won't change the inherent locking required to
do an update. Your problem is architectural and will require adjusting the
schema to fix. I would represent Status as a work queue using another
table. The presence of a pointer to the Primary Key indicates the status.
If there is no entries, then the status is whatever the most common status
(I.E. 'Closed', 'C', 'Paid', depending on the context) actually is.
Geoff N. Hiten
Microsoft SQL Server MVP
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:BED3489D-5AC1-48A7-B785-C9EF6128573B@.microsoft.com...
> I have the following updates statements in my stored procedure which
> caused
> a deadlock. Should I take the (NOLOCK) statement out of the update
> statements?
> Is there some else I can to help resolve this deadlock?
> Thanks,
>
> Update SRA_FlowMaster
> Set Status = 'I'
> From SRA_FlowMaster New
> Inner Join SRA_FlowMaster (NoLock)
> On New. FlowMasterNo = SRA_FlowMaster. FlowMasterNo
> And New.TypeCode = SRA_FlowMaster. TypeCode
> WhereNew. FlowMasterID = @.i_FlowMasterID
> And SRA_FlowMaster. FlowMasterID <> @.i_FlowMasterID
> And SRA_FlowMaster. Status In ('K', 'M')
> Update SRA_FlowMaster
> Set Status = 'I'
> From SRA_FlowMaster New
> Inner Join SRA_FlowMaster (NoLock)
> On New. FlowMasterNo = SRA_FlowMaster. FlowMaster
> And New.TypeCode = SRA_FlowMaster. TypeCode
> Where New. FlowMasterID = @.i_FlowMasterID
> And SRA_FlowMaster. FlowMasterID <> @.i_FlowMasterID
> And SRA_FlowMaster. Status = 'A'
> And New. Status In ('A', 'D')
>