Locks to be requested by DML statements in SQL Server

For SELECT statements:

1, when the table scan, the page will be locked directly, instead of locking a specific record, so there will be these locks:

A, database S lock

B, the table is lock

C, the page s lock

2. When the index is used to find the data, the specific records are locked, so there are these locks:

A, database S lock

B, the page in the index is lock

C, the S lock of the key in the page in the index

D, table is lock

E, page is lock

F, the S-lock of the RID

3, for read the page, will add an IS lock.

For the index used, the key is added with an S lock, and an IS lock is added to the page where the index key is located.

In the query process, each read to the record or key plus s lock.

If the record is not what we are looking for, then the S lock is released, and if the record is to be returned, it will be released, if it is read committed, if it is not, otherwise it will not be released.

For the UPDATE statement:

1, when the table scan, the page will be locked directly, instead of locking a specific record, so there will be these locks:

A, database S lock

B, the IX lock of the table

C, the page of the U lock or x lock, the difference is that because the UPDATE statement is also to find the data before you can modify, so when checking the data, the page with IU Lock, and then continue to view the records in the page, the record will be added to the U lock, if you find this record, do not need to update, Then the U lock is released immediately, and if the record is found to be updated, then an X lock is added, then the record is update and the next record is viewed.

2. When the index is used to find the data, the specific records are indexed, so there are these locks:

A, database S lock

B, the page in the index IU lock

C, the index in the page of the key of the U lock, that is, do not want other sessions to modify the key field of this record, such as now have 2 sessions, 1 to modify the 10th record of the Name field, and the other is to modify the 10th record of the key field, obviously, When a session modifies the name field of the same record, it is bound to block another session from modifying the key field. That is, the same record of the different fields, must not be modified at the same time, there is always a succession, otherwise the data is inconsistent.

D, the IX lock of the table

E, the page's IU lock or IX lock, the difference is the same as above, the query is the IU lock, when the page found that there is a need to modify the record, will be converted to IX lock.

F, the x lock of the RID

3, for each use of the index, the key plus U lock, the index key is located on the page will be added IU lock.

Because update also needs to find records before updating, so during the scanning process, the records to be scanned on the page, plus IU lock, the pages of the record plus U lock, if these records to modify, then upgrade to the X lock, the page will be upgraded to IX lock. If the record does not need to be updated, the U Lock is released and the IU Lock is released if the entire page has no records to modify.

Also, the more indexes you use to modify a column, the more locks will be. For example, to modify the value of the Name field now, and the index containing the name field is 2, then when you modify the name, you also need to lock the pages and keys of the 2 indexes, so the value of the Name field in the table is modified before you modify the key value of the 2 indexes that involve the name field. This modification is to delete, and then insert the key value, for example, to update 3 records, then the last time you modify the index, will apply for 6 key x lock, 3 are to be deleted, 3 are to be inserted.

However, it is important to note that the difference between the UPDATE statement and the SELECT statement is that the lock acquired by the UPDATE statement is not released until the end of the transaction.

For INSERT statements:

A, database S lock

B, the IX lock of the table

C, the IX lock on the page, and an IX lock for the page for which you want to insert the new data.

D, RID, for the newly inserted data, an X lock will be applied.

E, if there are multiple indexes in the table, a new data is inserted on each index, and there is an IX lock on the page where the data is to be inserted.

F, to insert the key in the index, there will also be an X lock.

Note: is, IU (not found at the table level), IX these 3 intent locks, are at the table level, page level, and S, U ( not at the moment there is no lock at the table level), X can be at multiple levels.