Re: can two stored procedures in same transaction cause deadlock

huanchhspfree_at_yahoo.com wrote:
> Hi,
>
> We are experiencing a deadlock issue using MS SQL 2000 that's
> generating some debate in our office. We have two stored procedures
> SP1 and SP2 running in the same transaction along with couple other
> stored procedures, SP1 does a deletion on one table, then later SP2
> does a select on the same table. The isolation level is set to be
> Read_Commited. I believe the deadlock is caused by the fact that
one
> cannot update and then read a table in the same transaction if
> isolation is Read_Commited based on my limited knowledge about
> databases, however I need a second opinion on this if possible.
>
> Any help will be greatly appreciated, thanks in advance.

yes, it can happen. depends on how SqlServer implements SP. post on that group for better info.

here's how it can happen: SP1 executes in its own context, grabbing locks on the way. these locks are associated to the context, but persist
on exit, since they are wrapped by the 'transaction' which called it. then SP2 executes, also in its own context. it sees rows locked by SP1 (they haven't been released since the 'transaction' is active), but they're not owned by SP2. deadlock. whether this is what's happening (logically it explains observation) depends on the SqlServer engine.

robert Received on Sat Feb 19 2005 - 23:51:05 CET