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.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability