SQL Server locking

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.

To query the lock timeout time in the current session of SQL Server, run the following command:

Select @ lock_timeout

The default value 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 the example to be limited to the current session, 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 maid = 7 where orderid = 10248
-- Rollback; -- intentionally does 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:

Set lock_timeout 3000
Update orders set maid = 7 where orderid = 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 -------------------------------------------------------------------------------------------

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 will cause a full table scan, therefore, the transaction of session A will be blocked (because this record with orderid = 10248 will be read during full table scan ).

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

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

Select * From SYS. dm_tran_locks

Exec sp_lock also uses the kill command 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. 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.