I tried to find some information about blocking in SQL Server, but I could not find a concise explanation for what it is and how it happens. Could you please enlighten me?
SQL Server Blocking – What Is Blocking and How Does It Happen
lockingsql server
Related Solutions
2) How can I find out the last command of the sleeping process?
You can use Adam Machanic's sp_whoisActive tool to find the lead blocker.
You can also use DMV's (starting sql 2005 and up) to find out what the spid was executing :
-- You can use "most_recent_sql_handle" in sys.dm_exec_connections to see the last statement that was executed.
SELECT c.session_id, t.text,
QUOTENAME(OBJECT_SCHEMA_NAME(t.objectid, t.dbid)) + '.'
+ QUOTENAME(OBJECT_NAME(t.objectid, t.dbid)) proc_name,
c.connect_time,
s.last_request_start_time,
s.last_request_end_time,
s.status
FROM sys.dm_exec_connections c
JOIN sys.dm_exec_sessions s
ON c.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) t
WHERE c.session_id = -- spid for the spid involved in blocking
-- for any OPEN Transactions, you can use `sys.dm_tran_session_transactions` and `sys.dm_tran_active_transactions`
SELECT st.transaction_id,
at.name,
at.transaction_begin_time,
at.transaction_state,
at.transaction_status
FROM sys.dm_tran_session_transactions st
JOIN sys.dm_tran_active_transactions at
ON st.transaction_id = at.transaction_id
WHERE st.session_id = -- spid for the spid involved in blocking
Refer to :
- Understanding and resolving SQL Server blocking problems
- Different Status of a SPID in SQL Server and What do they mean
- Locking in Microsoft SQL Server (Part 4 – How to detect blocking)
Alternatively, you can use a mix of Event Notification or Profiler with Blocked process report to detect blocking on your database server.
How does the output illustrate implicit elevation of isolation level?
Sunil is technically correct, but it does sound a little confusing, I agree.
The output shows the session is blocked waiting to acquire a U
lock. The definition of the READ COMMITTED
isolation level is that the session will only encounter committed data. SQL Server honours this logical requirement under the default pessimistic (locking) implementation of read committed by holding shared locks just long enough to avoid seeing uncommitted data. These shared locks are normally quickly released (usually just before reading the next row).
Under optimistic (row-versioning) read committed (RCSI
) SQL Server avoids reading uncommitted data by reading the last-committed version of the row at the time the statement started instead.
The sense Sunil is trying to convey is that taking U
locks (instead of brief shared locks or reading versions) represents a (technical) escalation of isolation level (though not to any explicitly named level).
The effective isolation level in this case is not quite REPEATABLE READ
because any U
locks taken (and not converted to X
locks) are released at the end of the statement. This is different from the behaviour of the UPDLOCK
hint, which acquires and holds U
locks (at least) until the end of the transaction. In addition, REPEATABLE READ
generally acquires S
locks (though this is strictly just an implementation detail).
Confusingly, the engine also takes U
locks on the access method when identifying rows to update under default (locking) read-committed. This is a convenience to avoid a common deadlocking scenario without having to specify UPDLOCK
explicitly. I apologise that this is so complicated, but there we are.
How to check for real isolation level "jumpings" in context of some statements?
There is nothing explicitly exposed in query plans to identify cases where the engine temporarily increases the effective isolation level. This might change in a future version of SQL Server. There may be indirect evidence in terms of locks taken, but this is rarely a convenient approach.
When to expect them and why do they occur?
Some of the occasions when internal escalation occurs are (somewhat) documented in Books Online. For example, Understanding Row Versioning-Based Isolation Levels says (among other things worth noting):
In a read-committed transaction using row versioning, the selection of rows to update is done using a blocking scan where an update (U) lock is taken on the data row as data values are read.
The general reason for temporary changes in effective isolation level changes is to avoid data corruption. A list of posts identifying some common cases follows:
Blocking Operators
Large Objects
Lookup with Prefetching
Cascading Referential Integrity
Other common cases (not a complete list):
- Shared locks taken when the query processor verifies foreign key relationships.
- Range locks taken when maintaining an indexed view referencing more than one table.
- Range locks taken when maintaining an index with
IGNORE_DUP_KEY
.
Some of these behaviours may be documented in Books Online, somewhere, but there's no convenient single list that I am aware of.
Related Question
- SQL Server 2014 – Blocking Issue with sp_releaseschemalock
- SQL Server – How to Get Information About Sleeping Processes Causing Blocking
- Sql-server – What could be preventing updates and inserts to a table without anything visibly blocking the process
- Sql-server – How to track blocking that happen for less than a second – SQL Server
- Blocking Upsert Procedure in SQL Server – How to Resolve
- SQL Server – Framework to Identify Blocking Queries
Best Answer
Analogies
Sometimes it helps to use analogies away from computers.
Let's say you have a ball and two children. Only one child can have the ball at any one time. However, if one of the children gets the ball and doesn't let go of it because he's distracted (watching TV, for example), then the other child will not get to play with the ball.
The other child is blocked from that resource.
If we compare this to the TV, for example, several children can watch TV at any one point.
Locks
If we move over to the database world, we see that there are different ways to use resources (just like our two examples above). We can perform "reads" or we can perform "writes".
When we want to read the data, there's no reason that other's can't read the data as well--just like two people watching TV. However, if we want to write the data, then we need to make sure that no one else is looking at it. If they are reading it while we're writing it, they will get "dirty" reads. (Meaning, they'll see the data partially written out, which will be invalid.)
In order to insure that these dirty reads never occur, we have two primary types of locks, Read Locks and Exclusive Locks.
Read Lock
You can have several different connections reading from the same datasource at any given time. But to insure that no one changes that data while they're reading it, they take out a Read Lock.
Once a connection has a read lock on a piece of data, all other connections must wait until the Read Lock is released before they can write the data. Others can, however, take out Read Locks of their own on that same piece of data.
Exclusive Lock
If a connection wants to update/insert/delete a piece of data, they have to take out an exclusive lock. This prevents any other connection from also taking out a lock on the data (making the lock exclusive to that connection).
When a connection has an exclusive lock on the data, no other connections may read from the data. This helps prevent dirty reads by insuring that no one can read the data while its being written.
Blocking
"Blocking" is simply a term that means that one connection is holding a lock on a resource when another connection wants to read or write to it. It doesn't necessarily mean that the owner connection won't release it, just that it's currently holding it.
Compare this to the case with a child holding the ball. The child holding the ball is blocking all other children from holding the ball.
Deadlock
I know you didn't ask this, but it's only one more step to get to deadlocks (and it's related very directly to blocking).
Deadlocks can happen when you have two connections that each have a lock, but they want each others resource. In this scenario, it's like two children that each has a ball, but wants the other's ball.
Like children, these connections are not willing to share at all. Each connection needs access to both of the resources in order to continue. However, they are in a state of permanent blocking. In this state, the parent (DBMS) has to come in and choose a loser so that one of the children (connections) can have access to both of the resources.
Once that "winning" connection is done, it releases the resources and then the other ("losing") connection can try again to get to both resources.
So, the concept of a deadlock is where you have two resources that are blocking each other.
Here, you can read more about all the different types of locks SQL Server has to offer and the different resources that can cause blocking/deadlocks. The article is old, but it still applies for SQL Server 2000 through 2008 R2. (There are a few more types of locks added to later versions of SQL Server, but that will give you a starting point.)