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

One of the reasons for blocking is that different sessions are caused by incompatible locks when accessing the same table.
Whether the currently executing SQL statement is blocked (or deadlocked) is not only related to the existing lock on the current table, but also to the execution of the currently executing SQL statement
To put it simply, the execution of the SQL statement is nothing more than a table scan, index Scan, index lookup (clustered index or nonclustered index) for table access, etc.
If the SQL statement is executed improperly or does not have a reasonable index, it will cause unnecessary blocking, if the logical control is improper, even cause more serious problems, resulting in data logic errors

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 rows of data rows in the table, in the process of scanning, whether it is a table or data rows, if you encounter a compatible lock, you can access the normal,
If an incompatible lock is encountered, such as the query here is a shared lock (S lock), when the first session of the ID = 6666 of this row of data, found that the above is an exclusive lock,
At this point, the current session to add the lock (shared lock) and the row of the 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 to execute
If id = 6666 above the exclusive lock has not been released or changed to a lock type compatible with the shared lock, the current session has been waiting
Displayed as current session is blocked
The solution to this problem is also very simple, similar phenomenon if it is not blocked, or wait until the first session of the exclusive lock release, or a change in the way of a query
An attempt was made to create an index on the table's ID column, but a 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 Tuning----How SQL statements are executed in relation to locks and blocking