Contents
What is a blocking transaction?
As simple, blocking happens when one transaction puts a lock on resources then releases it so another transaction can use those resources. Blocking actually prevents conflicts from occurring. It occurs in little time allotments, and it is excessively snappy.
What is a sleeping SPID?
A Sleeping status means that the Engine has completed the command, everything between client and server has completed interaction wise, and the connection is waiting for the next command to come from the client. If the sleeping session has an open transaction, it is always related to code and not SQL Server.
Can a SPID sleep on an open transaction?
To get automatic rollback in this situation transaction abort must be enabled. You now have an open transaction with a SPID sleeping/awaiting command. The situation can be caused by many other variations but it is always a situation where the SQL Server is waiting for the next command from the client.
Why do I have an open transaction with a sleeping / awaiting command?
You now have an open transaction with a SPID sleeping/awaiting command. The situation can be caused by many other variations but it is always a situation where the SQL Server is waiting for the next command from the client. Outside a physical connection problem these are always application design issues.
When does a SPID release a lock on a resource?
Typically, the time frame for which the first SPID locks the resource is small. When the owning session releases the lock, the second connection is then free to acquire its own lock on the resource and continue processing.
When do I use the sleeping / awaiting command?
The question usually arises around a session that is holding locks and its state is sleeping / awaiting command. If the client has an open transaction and the client did not submit a commit or rollback command the state is sleeping / awaiting command. I see this quite often with a procedure that times out.