SQL Server optimization----How SQL statements are executed in relation to locks and blocking

One of the reasons for blocking is when different sessions are accessing the same table because of incompatible locks, whether the currently executing SQL statement is blocked (or deadlocked), not only with the existing lock on the current table, but also with the execution of the currently executing SQL statement. Simply speaking, how to access the table , the execution of SQL statements is nothing more than table scan, index Scan, index lookup (clustered index or nonclustered index), etc. if the SQL statement is executed improperly or does not have a reasonable index, it will cause unnecessary blocking, if the logic control is improper, even cause more serious problems, causing the data logic error

Build a test table and test the demo below

CREATE TABLE Testindexandlock (    int identity (1,1),    col2 varchar (100000  

A very common business is to open things in the current session, perform updates or delete a row of data, and then do a series of other operations, before the current thing commits, the exclusive lock remains until the thing commits.

such as the following:

The first session uses things and exclusivity to lock a row of data, and to do business logic, as a matter of principle: the goal is simply to lock the current row of data (id = 6666), perform exclusive operations, and not lock its data rows

　　

Then the second session, query the other unrelated ID, according to the truth, the above session just locked the ID = 6666 line, how the current session Query ID = 7777 of the row will be blocked?

　　

Here is how the current table is accessed, because there is no index on the table currently queried, when the query id = 7777, although there is no lock on the line of id = 7777, why is the query blocked? Here in the execution of the query id = 7777, with a full table scan execution, at this time, encountered ID = 6666 of this row of data, because there is an exclusive lock on this row of data, the current session is blocked can be simply understood as the current session will scan the table rows of data row, In the process of scanning, whether it is the table or the data row, if you encounter a compatible lock, you can normally access, if you encounter an incompatible lock, such as the query here is a shared lock (s lock), encountered in the first session of the ID = 6666 of this row of data, found above is an exclusive lock, at this time, The current session to add to the row of the lock (shared lock) and the row of exclusive lock (x Lock) is incompatible, you can only wait, know id = 6666 This row of data on the rows of the lock release or change the current request of the shared lock compatible with the lock can be executed if the id = 6666 the upper row lock has not been released or changed to a lock type compatible with the shared lock, the current session has been waiting for the current session to be blocked this problem solution is also very simple, similar phenomenon if it is not blocked, or wait until the first session of the exclusive lock release, Either try to create an index on the table's ID column in a different query, but the clustered index is also available to prevent the query from being scanned in full-table mode.

　　

In the second session to execute the above query, query id = 7777 of the data row, you can find that the query can be completed successfully

　　

This is the index to change the execution plan of the query, to avoid the full table scan when there is no row of data on the lock blocking the situation. The index mentioned here is not just for the sake of efficiency, but more importantly, to change the way the query is executed, to avoid encountering locks on the table that have exclusive locks in the case of blocking.

The function of index is not only to improve the efficiency of query, but also to avoid the blocking of locks in the whole table scan, which is helpful to reduce blocking.

SQL Server optimization----How SQL statements are executed in relation to locks and blocking