Re: can two stored procedures in same transaction cause deadlock

"Benjamin Gufler" wrote
> huanchhspfree_at_yahoo.com wrote:
>> 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.
>
> SP2 should see all the rows that have *not* been deleted by SP1.
>
>> 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.
>
> No, it is not possible. Within a single transaction, you must see the
> changes you have already made. Two or more transactions are required to
> produce a deadlock.
> Are you sure that there is no other transaction? Or maybe the "same"
> transaction (the same sequence of commands) running twice at the same
> time?

I agree with Ben ... there must be another distinct process (one or more) also working over the same table to cause a deadlock.

Frank. Received on Thu Feb 17 2005 - 12:42:30 CET