Quickly understand the locking and blocking of SQL Server

This post provides two methods to avoid exceptions or long-time blocking caused by SQL Server transaction locks.ProgramIt also waits indefinitely, and even causes the Connection Pooling connection to exceed the capacity.

The so-called "blocking" refers to the transaction in a database Session, which is locking the resources that other session transactions want to read or modify, causing the requests sent by these sessions to enter the waiting state.By default, SQL Server will keep blocked requests waiting for an indefinite period of time.Until the original transaction releases the related lock, or until it times out (according to set lock_timeout, as mentioned later in this article), the server is closed, and the process is killed. In a general system, occasional short-term blocking is normal and reasonable. However, if a program is poorly designed, it may lead to long-term blocking, so that resources are not necessarily locked, it also blocks the need for other sessions to read or update. In this case, you may need to manually exclude the blocking status, and this article will introduce two methods to eliminate blocking.

 

A few days ago, the company's server-side component was suspected that exception-handling was not well done at the time of writing, resulting in rare special exceptions, so that SQL Server transactions were not executed to the cmmit or rollback, some tables or records are locked )」. Later there were a large number of requests to be passed throughCodeAccess to these locked records results in a serious long "blocking". Finally, a large number of processes are in the "Waiting" status on SQL Server.

Because the "transaction isolation level" of SQL Server is read committed by default (which cannot be read during the transaction), and the locking of SQL Server causes blocking, by default, other processes must wait indefinitely (lock_timeout =-1 ). As a result, a large number of client requests will never be committed or rolled back for an indefinite period of time, occupy Resources in the connection pool, and overload the number of Connection Pooling connections.

I checked some books. to query the lock timeout time in the current session of SQL Server, run the following command:

Select @ lock_timeout

The default execution result is-1, indicating that the object or record to be accessed is locked and will wait for an indefinite period of time. To change the value of the current session, run the following command:

Set lock_timeout 3000

The unit of the second 3000 is milliseconds, that is, it waits for three seconds for the locked object. If the transaction has not released the lock, the following error code 1222 will be thrown back, which can be processed by programmers when programming:

Message 1222, level 16, status 51, 3rd rows
The lock request timeout period has been exceeded.

If you set lock_timeout to 0, that is, when the object to be accessed is locked, the error code 1222 will be returned if you do not have to wait. In addition, this set lock_timeout command affects only the current session (process), rather than permanent settings for a table.

Bytes -------------------------------------------------------------------------------------------

Next, we open two sessions (query window) in SSMs for testing. session A creates a transaction process that will cause blocking, and session B accesses the locked records.

 

-- Session
Begin   Tran ;
Update Orders Set Employeeid = 7   Where Orderid = 10248
-- Rollback; -- intentionally not submit or roll back

 

 

-- Session B
Select   *   From Orders Where Orderid = 10248

 

After each execution, session B cannot read the data according to the default values of SQL Server transaction isolation level and lock because the record to be accessed is the same, it will always wait (if you write this code in a real project, you will be scolded by the customer and the boss ).

Bytes -------------------------------------------------------------------------------------------

If set lock_timeout 3000 is added to session B, session B will wait 3 seconds before it throws the "Lock request timed out" error code 1222:

 

--Session B
Set Lock_timeout 3000
Select * FromOrdersWhereOrderid=10248
-- Set lock_timeout-1

 

Execution result:

Message 1222, level 16, status 51, 3rd rows
The lock request timeout period has been exceeded.
The statement has been terminated.

Bytes -------------------------------------------------------------------------------------------

Based on what I wrote earlierArticle「 30 minutes happy learning SQL Performance Tuning:
Http://www.cnblogs.com/WizardWu/archive/2008/10/27/1320055.html

Improper Writing of SQL statements will make the database index unusable, resulting in full table scan or full clustered index scan. For example, improper use of the not, or operator, or directly concatenating two fields with the plus sign as the where condition may cause index failure and change to full table scan. In addition to poor performance, in this case, if this bad SQL statement is the statement of session B mentioned above in this post, it may cause full table scan or clustered index scan, therefore, it will be blocked by session a's transaction (because when scanning the entire table, it will certainly read the record that session a is locking at orderid = 10248 ).

In the SQL statement below, because the orderid field has an index, the "Execution Plan" of 1 isAlgorithmIn the binary search method, you can quickly search for records with orderid = 10250 in the index.

Select * from orders where orderid = 10250

Select * from orders where orderid = 10250 and shipcountry = 'Brazil'

Figure 1 shows an SQL statement that correctly uses the index and uses the index vertically. When using the and operator, as long as any field has an Index added, it will benefit from the index and avoid full table scanning.

At this point, if we use this SQL statement as the preceding statement of session B, because it is not the same record as the orderid = 10248 updated by session, therefore, it is not affected by the non-rollback of session A. Session B can normally execute the SELECT statement.

However, if we change the SQL statement of session B to the "or" operator below, because the shipcountry field does not have an index, this will cause clustered index scanning (the same as full table scan, scan the entire table one by one ). In this way, in addition to low performance, it will also block the reading of the orderid = 10248 record locked in session a during one-by-one scanning, make session B always in the "Waiting" status.

Select * from orders where orderid = 10250 or shipcountry = 'Brazil'

Figure 2 SQL statements that do not correctly use indexes horizontally. When using the OR operator, the "all" fields must be indexed to effectively use the index and avoid full table scanning.

Bytes -------------------------------------------------------------------------------------------

When a blocking occurs, you can see through the following command which process session ID is blocked, which process session ID is blocked, and how many "milliseconds (MS)" has elapsed )」. For example, session ID = 53 in 3 blocks the process with session ID = 52. In addition, the same content can be seen 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 in this post blocks the SELECT statement (52) of session B)

The following two commands show the locking and blocking details of the entire database:

Select * From SYS. dm_tran_locks

Exec sp_lock

Figure 4 Process with session ID = 52 is in the waiting state due to blocking (wait)

In addition, the kill command can be used to directly kill the blocked process, as shown below:

Kill 53

Bytes -------------------------------------------------------------------------------------------

To solve the problem of waiting for an indefinite period, in addition to the aforementioned set lock_timeout command, there are also more convenient practices, as shown below, in the SQL statement of session B, add with (nolock) after the table name) keyword, indicating that SQL server is required. You do not have to consider the locking status of the table. Therefore, you can reduce the chance of "Dead Lock. However, with (nolock) does not apply to insert, update, and delete.

Select * from orders with (nolock) Where orderid = 10248

Similar functions can also be used as follows. Before an SQL statement, set the transaction isolation level to dirty read )」.

SET transaction isolation level read uncommitted
Select * from orders where orderid = 10248

The effects of the two methods are similar, so that session B never has to wait even if it reads the lock-blocking record, but may read data not submitted by others. Although session B does not need to request a shared lock, that is, it will never conflict with other transactions, but the actual needs of project development should be considered. If session B needs to query the inventory of raw materials, or the key data of the banking system is not suitable for this practice. Instead, you should use the set lock_timeout command in the first practice to explicitly let the database throw the error code 1222 when waiting for the timeout, write your own code for processing.

Bytes -------------------------------------------------------------------------------------------

In the final analysis, when programming, we should avoid writing SQL statements that will cause long blocking, that is, we should minimize the possibility of locking in contention. The following are some suggestions:

• Minimize transactions and minimize lock time, such as removing unnecessary commands from the transaction or splitting a large number of updated transactions into transactions with Fewer updates, to improve concurrency.
• Place the SQL statement that makes up the transaction into a batch to avoid unnecessary latency. These latencies are often caused by network I/O between the tran in Tran... commit Tran commands.
• Write the SQL statement of the transaction in a stored procedure. In general, the execution speed of a stored procedure is faster than that of a batch SQL statement, and the stored procedure can reduce network traffic and I/O, so that transactions can be completed more quickly.
• The updates in the cursor should be recognized as frequently as possible, because the processing speed of the cursor is slow and the lock takes a long time.
• Use loose transaction isolation levels, such as with (nolock) and read uncommitted. Instead of facilitating project development, use the default read committed level.
• Avoid waiting for user feedback or interaction during transaction execution, which may result in an indefinite hold lock, as mentioned at the beginning of this post, at last, a large number of blocking and database connections are occupied.
• Avoid data queried after the transaction starts in TRAN and may be referenced before the transaction starts.
• Avoid joining too many tables during query (this refers to a non-essential join). Otherwise, in addition to poor performance, it is easy to read tables and fields that are being locked or blocked.
• Note that in order to effectively manage these locks, the SQL server requires excessive "Row locks" on an index-free table, or when some locks use excessive memory and system resources, attempts to extend the lock to the "Table lock" of the entire table, which may easily cause access blocking and waiting for other processes.

Bytes -------------------------------------------------------------------------------------------

This post has not mentioned deadlocks and other more advanced topics. We will continue to make tea and chat later.