Preventing Deadlock

Trace 1204 has narrowed the cause of deadlock to these delete statements.
There are a total of 10 delete statements in this SP.
create procedure del_emp @.empid int
Delete from tbl_employees
where empid in (Select empid from tbl_location where empid = @.empid)
Delete from tbl_management
where mge_ID in (Select mge_ID from tbl_sunmanagement where empid = @.empid)
......
......
Questions:
1. should i add (nolock) within the select statement? ex: Select empid from
tbl_location WITH (NOLOCK)
2. should i add (updlock) within the delete statement? ex: Delete from
tbl_employees WITH (UPDLOCK)
Any suggestions highly appreciated.Hi
You don't say what is being blocked and what it causing the blocking! You
may be missing an index, which would help, check out the query plan to see if
this is so. Will the empid's in tbl_location be anything other than @.empid?
Do you explicitly start a transaction outside this procedure?
John
"morphius" wrote:
> Trace 1204 has narrowed the cause of deadlock to these delete statements.
> There are a total of 10 delete statements in this SP.
> create procedure del_emp @.empid int
> Delete from tbl_employees
> where empid in (Select empid from tbl_location where empid = @.empid)
> Delete from tbl_management
> where mge_ID in (Select mge_ID from tbl_sunmanagement where empid = @.empid)
> ......
> ......
> Questions:
> 1. should i add (nolock) within the select statement? ex: Select empid from
> tbl_location WITH (NOLOCK)
> 2. should i add (updlock) within the delete statement? ex: Delete from
> tbl_employees WITH (UPDLOCK)
> Any suggestions highly appreciated.
>
>|||Hi John,
Thanks for the reply. The empid in tbl_location is not anything other than
the empid. Yes a transaction is explicitly started outside this procedure and
between 100-300 users could be using the transaction simultaneously.
"John Bell" wrote:
> Hi
> You don't say what is being blocked and what it causing the blocking! You
> may be missing an index, which would help, check out the query plan to see if
> this is so. Will the empid's in tbl_location be anything other than @.empid?
> Do you explicitly start a transaction outside this procedure?
> John
> "morphius" wrote:
> > Trace 1204 has narrowed the cause of deadlock to these delete statements.
> > There are a total of 10 delete statements in this SP.
> >
> > create procedure del_emp @.empid int
> >
> > Delete from tbl_employees
> > where empid in (Select empid from tbl_location where empid = @.empid)
> >
> > Delete from tbl_management
> > where mge_ID in (Select mge_ID from tbl_sunmanagement where empid = @.empid)
> >
> > ......
> > ......
> >
> > Questions:
> > 1. should i add (nolock) within the select statement? ex: Select empid from
> > tbl_location WITH (NOLOCK)
> > 2. should i add (updlock) within the delete statement? ex: Delete from
> > tbl_employees WITH (UPDLOCK)
> >
> > Any suggestions highly appreciated.
> >
> >
> >
> >|||Hi
If I understand you correctly, then there is no need to query table location
and you could just use
Delete from tbl_employees
where empid = @.empid
or is was there a typo or misunderstanding?
If no other code is in the transaction other than the stored procedure call
(i.e. two delete statements) then you would need to look at the query plans.
John
"morphius" wrote:
> Hi John,
> Thanks for the reply. The empid in tbl_location is not anything other than
> the empid. Yes a transaction is explicitly started outside this procedure and
> between 100-300 users could be using the transaction simultaneously.
> "John Bell" wrote:
> > Hi
> >
> > You don't say what is being blocked and what it causing the blocking! You
> > may be missing an index, which would help, check out the query plan to see if
> > this is so. Will the empid's in tbl_location be anything other than @.empid?
> > Do you explicitly start a transaction outside this procedure?
> >
> > John
> >
> > "morphius" wrote:
> >
> > > Trace 1204 has narrowed the cause of deadlock to these delete statements.
> > > There are a total of 10 delete statements in this SP.
> > >
> > > create procedure del_emp @.empid int
> > >
> > > Delete from tbl_employees
> > > where empid in (Select empid from tbl_location where empid = @.empid)
> > >
> > > Delete from tbl_management
> > > where mge_ID in (Select mge_ID from tbl_sunmanagement where empid = @.empid)
> > >
> > > ......
> > > ......
> > >
> > > Questions:
> > > 1. should i add (nolock) within the select statement? ex: Select empid from
> > > tbl_location WITH (NOLOCK)
> > > 2. should i add (updlock) within the delete statement? ex: Delete from
> > > tbl_employees WITH (UPDLOCK)
> > >
> > > Any suggestions highly appreciated.
> > >
> > >
> > >
> > >