Mayank Khatri (4/8/2009)
Above transaction will result in Deadlocks.
Why do you say that? Locks and blocking it will definitely cause, but with only one update, it's unlikely to deadlock, unless there's more to it than we've been shown.
This current SPID will allways be in a running state until commited or killed.
Actually not. Once the last of the selects has finished, the state of the connection will be sleeping. Only connections that are currently running queries have a running state. A connection can be sleeping with open transactions.
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)SQL In The Wild
: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass