How SQL Server block queries and kill

This post provides two ways to avoid unhealthy or long-time blocking when SQL Server transactions are locked, allowing users and programs to wait indefinitely, even causing connection pooling connections to exceed capacity.

The so-called "blocking" refers to when a transaction in a database session is locking up resources that other session transactions want to read or modify, causing requests made by these sessions to enter a waiting state. SQL Server defaults to allow blocked requests to wait indefinitely until the original transaction releases the associated lock, or until it times out (according to SET Lock_timeout, later in this article), the server shuts down, and the process is killed. In general systems, occasional short-term blocking is normal and reasonable, but poorly designed programs can cause long-term blocking, which unnecessarily locks up resources and blocks the need for other sessions to be read or updated. In this case, it may be necessary to manually exclude the blocking state, and this article introduces two ways to troubleshoot blocking.

A few days ago, the company Server-side have components, suspected because of the composition of exception-handling do not thoughtful, resulting in rare special exception occurs, let SQL Server transaction did not execute to cmmmit or rollback, causing some tables or records are "locked" (LO CK) ". Then there was a lot of request to access these locked records through code, resulting in a serious long "block" and finally a lot of process (processes) in SQL Server rendering "Waiting (wait)" status.

Because the transaction isolation level of SQL Server is read COMMITTED by default (which cannot be read by others during the transaction), and when the locking of SQL Server causes blocking, the default is that other processes must wait indefinitely (Lock_timeout =-1). As a result, these large client request waits indefinitely for transactions that are never committed or rolled back, and has been occupying resources in the connection pool, resulting in an overload of connection pooling connections.

Check some books, if we want to query the lock timeout in SQL Server's current session, you can use the following command:

SELECT @ @LOCK_TIMEOUT

The execution result defaults to 1, which means that the object or record to be accessed waits indefinitely when it is locked. To change this value for the current session, the following commands are available:

SET Lock_timeout 3000

The next 3000, which is in milliseconds, is waiting for the object to be locked for 3 seconds. If the transaction still does not release the lock, it will throw back the following error message with a code name of 1222, which can be used by programmers to do the related overtime processing:

MSG 1222, Level 16, State 51, line 3rd
The lock request time-out period has been exceeded.

If you set the Lock_timeout to 0, that is, when you want to access the object is locked, completely do not wait to throw back the error message code 1222. In addition, this set LOCK_TIMEOUT command, the impact instance is limited to the current session (process), rather than making permanent settings on a table.

-------------------------------------------------------------------------------------------

Next we do a test in SSMS with two sessions (query window), session A creates a blocking transaction process, and session B accesses the locked record.

-- Session A
BEGIN TRAN;
UPDATE Orders SET EmployeeID=7 WHERE OrderID=10248
-- rollback;--intentionally not committed or rolled back

-- session B
SELECT * from Orders WHERE OrderID=10248

Once executed separately, session B will not be able to read the data because the record you want to access is the same as the default values for the SQL server"transaction isolation level and lock, and will always wait forever (if you write this code in a real project, you are ready to be scolded by the client and boss).

-------------------------------------------------------------------------------------------

If session B is preceded by the set LOCK_TIMEOUT 3000 setting, then session B waits 3 seconds before throwing a "lock request timed out" error message with the code 1222:

-- Session B
SET lock_timeout 3000
SELECT * from Orders WHERE OrderID=10248
--SET lock_timeout-1

Execution Result:

MSG 1222, Level 16, State 51, line 3rd
The lock request time-out period has been exceeded.
Statement has been terminated.

-------------------------------------------------------------------------------------------

According to the article I wrote earlier, "30 minutes happily learning SQL performance tuning", "said:
Http://www.cnblogs.com/WizardWu/archive/2008/10/27/1320055.html

Poorly written SQL statements can make the index of the database unusable, resulting in a full-table or full-clustered index scan. For example, improper: not, or operator use, or directly with the + number to string two fields as a WHERE condition, can cause index invalidation, become a full table scan, in addition to performance change, at this time if this bad SQL statement, this post is the statement of Session B, because it will cause a full table scan or clustered index scan , so it is bound to be blocked by session A's transaction (because when the full table is scanned, it must also read the record that session a is locked in orderid=10248).

The SQL statement below, because the OrderID field has an index, so 1 of the "Execution plan", the algorithm in the "binary lookup" in the index to quickly find ORDERID=10250 records.

SELECT * from Orders WHERE orderid=10250

SELECT * from Orders WHERE orderid=10250 and shipcountry= ' Brazil '

Figure 1 has an SQL statement that correctly uses the index, using the index in a vertical direction. When you use the AND operator, you can benefit from the benefits of an index as long as any one field is indexed, and avoid full-table scanning

At this point, if we use this SQL statement as described in Session B, the session B can execute the SELECT statement properly because it and the orderid=10248 that the session a UPDATE is not the same record, so it is not affected by a transaction that does not roll back.

But if we use session B's SQL statement instead of the following OR operator, because the ShipCountry field is not indexed, it causes a clustered index scan (like a full table scan, which records the entire table as a scan). As a result, in addition to low performance, but also because of the scan at a time, read to the orderid=10248 that is locked in session a record, causing blocking, so that session B will always appear "waiting" state.

SELECT * from Orders WHERE orderid=10250 OR shipcountry= ' Brazil '

Figure 2 An indexed SQL statement is used incorrectly, using the index in the horizontal direction. When using the OR operator, all fields must be indexed to effectively use the index and avoid full table scan

-------------------------------------------------------------------------------------------

When blocking occurs, the following command shows which process session ID is blocking which process session ID, and how many milliseconds (ms) have elapsed. As in 3, session ID = 53 blocks the session ID = 52 process. You can also see the same content through the SQL Server Profiler tool.

SELECT blocking_session_id, Wait_duration_ms, session_id from Sys.dm_os_waiting_tasks

Figure 3 The UPDATE statement (53) of session a above, blocking the SELECT statement for session B (52)

We can also see the lock and block details for the entire database through the following two commands:

SELECT * from sys.dm_tran_locks

EXEC sp_lock

Figure 4 Session ID = 52 process has been waiting for blocking (wait)

Another kill command directly kills the process that caused the blockage, as follows:

KILL 53

-------------------------------------------------------------------------------------------

To solve the problem of waiting indefinitely, in addition to the aforementioned SET lock_timeout command, there is a more convenient approach, such as, in session B of the SQL statement, after the table name with the (NOLOCK) keyword, indicating that SQL Server is required, do not have to consider the locking of the table Status, so you can reduce the chance of a deadlock (dead lock) happening. However, with (NOLOCK), INSERT, UPDATE, DELETE are not available.

SELECT * from Orders with (NOLOCK) WHERE orderid=10248

Similar functionality can be as follows, before the SQL statement, set the "Transaction isolation Level" to "dirty Read (Dirty Read)".

SET TRANSACTION Isolation Level READ UNCOMMITTED
SELECT * from Orders WHERE orderid=10248

The effect of both approaches is similar, so that session B will never have to wait, even if it reads a record that is blocked by a lock, but may read data not submitted by others. While this practice allows session B to not request a shared lock, i.e. never conflict with other transactions, it should consider the actual needs of the project development, if session B is to query the inventory of the original material, or the banking system Key data, it is not suitable for this practice, instead of the first method of the SET Lock_ TIMEOUT command, explicitly let the database thrown back to wait for the error code 1222, and then write the code to do the processing.

-------------------------------------------------------------------------------------------

In the final research, when we are programming, we should avoid writing SQL statements that cause long-time blocking, that is, the possibility of locking contention should be minimized, and here are some suggestions:

• As much as possible to make the transaction light and short, so that the lock time as short as possible, such as to remove unnecessary commands out of the transaction, or a large number of updated transactions, cut into multiple updates less transactions to improve concurrency.
• Put the SQL statements that comprise the transaction into a batch processing to avoid unnecessary delays. These delays are often caused by the BEGIN TRAN ... This is caused by network I/O between the COMMIT TRAN commands.
• Consider writing the SQL statement for a transaction within a stored procedure. In general, a stored procedure executes faster than a batch of SQL statements, and the stored procedure reduces network traffic and I/O, allowing transactions to complete faster.
• Recognize the updates in the cursor as frequently as possible, because the cursor is slow to process, which makes the lock time longer.
• If it is not necessary, use a looser transaction isolation level, such as the preceding with (NOLOCK) and READ uncommitted. Rather than for project development convenience, all use the default READ COMMITTED level.
• Avoid waiting for user feedback or interaction during transaction execution, which can lead to indefinite hold locks, as described in the beginning of this post, resulting in large amounts of blocking and database connection being occupied.
• Avoid data that is queried after the transaction begins TRAN, and may be referenced before the transaction starts.
• Avoid joins of too many tables at query time (this refers to non-essential joins), otherwise it is easy to read tables and fields that are locked or blocked in addition to poor performance.
• It should be noted that on a table with no indexes, excessive "row locks", or some locks using too much memory and system resources, SQL Server attempts to effectively manage these locks by extending the lock to a "table lock" for the entire table, which can easily cause the other process to block and wait during access.

-------------------------------------------------------------------------------------------

This post does not mention deadlocks and other more advanced topics, and so the next time you are free to continue the tea chat.

How SQL Server block queries and kill