The lock to be applied for by the DML statement in SQL Server

For select statements:

1. When a table scan is used, the page is directly locked, instead of locking a specific record. Therefore, these locks are available:

A. Database S lock

B. Table IS lock

C. Page S lock

2. When indexes are used to search for data, specific records are locked, so these locks apply:

A. Database S lock

B. IS lock of the page in the Index

C. S lock of the key in the page in the Index

D. Table IS lock

E. Page IS lock

F. S lock of the RID

3. An IS lock will be applied to the pages you have read.

The S lock will be applied to the index, and the IS lock will be applied to the page where the index key IS located.

During the Query Process, the S lock is applied to each read record or key.

If the record is not to be searched, the S lock will be released. If the record is to be returned, it will be released based on the isolation level. If it is read committed, otherwise it will not be released.

For update statements:

1. When a table scan is used, the page is directly locked, instead of locking a specific record. Therefore, these locks are available:

A. Database S lock

B. IX lock of the table

C. Page U Lock or X lock. The difference between the two is that, since the update statement must first find the data before modification, the IU lock will be added to the page, and then the U Lock will be added to the record when you continue viewing the records on the page. If this record is found, it does not need to be updated, the U Lock will be released immediately. If this record is to be updated, the X lock will be added, the record will be updated, and the next record will be viewed.

2. When indexes are used to search for data, specific records are indexed, so these locks apply:

A. Database S lock

B. The IU lock of the page in the Index

C. The U Lock of the key in the page in the index, that is, the key field of the record cannot be modified by other sessions. For example, there are two sessions, one is the name field for 10th records and the other is the key field for 10th records. Obviously, when a session modifies the name field for the same record, it will certainly block another session from modifying the key field. That is to say, different fields of the same record must not be modified at the same time. There is always a sequence, otherwise the data is inconsistent.

D. IX lock of the table

E. The page's IU lock or IX lock. The difference is the same as above. The query uses the IU lock. When a record needs to be modified inside the page is found, will be converted to the IX lock.

F. X lock of the RID

3. For each index used, the U Lock will be applied to the key, and the IU lock will be applied to the page where the index key is located.

Because the update operation also needs to locate the record before it can be updated, during the scanning process, add the IU lock to the page where the record to be scanned is located, and add the U lock to the record on the page, if these records need to be modified, they will be upgraded to the X lock and the page will be upgraded to the IX lock. If this record does not need to be updated, the U Lock will be released. If there are no records to be modified on the whole page, the IU lock will also be released.

In addition, the more indexes you want to modify a column, the more locks you need. For example, if you want to modify the value of the name field and the index containing the name field has two, you also need to lock the page and key of the two indexes when modifying the name field, therefore, the value of the name field in the table will be modified first, and then the key value of the two indexes involved in the name field will be modified. This modification operation is to delete the index first, and then insert the key value, for example, to update three records, apply for the X lock of six keys when the index is last modified. The three are to be deleted and the three are to be inserted.

However, the difference between the update statement and the select statement is that the lock obtained by the update statement will not be released until the transaction ends.

For Insert statements:

A. Database S lock

B. IX lock of the table

C. The page IX lock also has an IX lock for the page for which data is to be inserted.

D. Apply for an X lock for the data to be inserted.

E. If there are multiple indexes in the table, a new data entry will be inserted for each index. An IX lock will apply to the page where the data is to be inserted.

F. The key to be inserted into the index also has an X lock.

Note: IS, IU (this lock IS not found at the table level currently), IX, these three intention locks are at the table level, page level, s, U (not found at the table level), and X can be on multiple levels.