Re: can two stored procedures in same transaction cause deadlock

Hi.

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?

Benjamin

-- 
Please compose your messages as plaintext:
   http://www.netby.dk/Oest/Europa-Alle/vermeer/plain.html
And do not send MS Office attachments:
   http://www.goldmark.org/netrants/no-word/attach.html