What is the role of a locks in SQL Server? Four isolation levels of weak Database Consistency

Intention lock

The database engine uses intention locks to protectLock hierarchyTo prevent other transactions from causing damage to the locked resources and improve the lock conflict detection performance. For exampleTableIn the requestPageTransactions before the shared lock (s lock)Table-level request sharing intention lock. This prevents other transactions fromTableAnd modify the entire table. Intention locks can improve performance becauseThe database engine only checks the intention lock at the table level to determine whether the transaction can safely obtain the lock on the table, you do not need to check the locks on each row or page in the table to determine whether the transaction can lock the entire table.

How can we understand the meaning of the above sentence? We will illustrate it with an actual example.

Assume that a student contains 1000 pieces of data. The test data is as follows:

 Create   Table  Student (ID  Int  , Name  Char ( 30 ),  Constraint Pk_id Primary   Key  (ID ))  --  3. Insert 1000 records  Set Nocount On  ;  Go  Declare   @ I   Int  ;  Set   @ I  =   1  ;  While   @ I   <=   1000   Begin    Insert   Into Student Values ( @ I , '  Zhangsan  '  + Cast ( @ I   As   Char  ))  Set   @ I   =   @ I   +   1  ;  End  ;  Go 

Because the ID column is set as the primary key, SQL Server automatically adds a clustered index to it. If we use the following statement to update table data,

Begin Tran UpdateStudentSetName='Zhangsan' WhereID=1000;
 

Then query the current lock status and execute the following tsql

 
--View the statusSelectRequest_session_id, resource_type, resource_associated_entity_id, request_status, request_mode, resource_descriptionFromSYS. dm_tran_locks
 

Shows the query result:

We can see that the IX lock is applied to the object and page, and the X lock is applied to the key. The results of these three types of lock layers are shown in:

So what is the purpose of adding an intention exclusive lock (ix) to a table? If another transaction requires an S lock on the entire table, it must determine that this s lock can be applied to the table.

1. If you do not use the intention lock, you have to traverse the key to check whether there is a lock that conflicts with the S lock. The record that we lock above is exactly the last one, then we have to traverse all the data. Currently, only 1000 pieces of data are fine and the data volume is small. If the data volume is tens of millions or more, the consumption will be very large.
2. If the intention lock is used, we do not need to traverse the data. We find that there is an X lock on the key, so an iX lock will be added to the table, and the IX lock conflicts with the S lock, therefore, the S lock fails, and the result is obtained soon.

Lock compatibility controls whether multiple transactions can simultaneously obtain the locks on the same resource. If the resource has been locked by another transaction, a new lock request is granted only when the request lock mode is compatible with the existing lock mode. If the request lock mode is not compatible with the existing lock mode, the transaction requesting the new lock will be forced to enter the waiting state, and blocking will occur. For example, if a transaction applies for an exclusive lock (x lock) on a resource, before it releases the exclusive lock (x lock, other transactions cannot obtain any type of lock (SHARE, update, or exclusive) for the resource. In another case, if a transaction has obtained a shared lock (s lock) on a resource, even if the first transaction has not been completed, other transactions can also obtain the shared lock or update lock (U Lock) of the item ). However, before the first transaction releases the shared lock, other transactions cannot obtain the exclusive lock.

Table 9-3 shows the compatibility of the most common lock modes.

Table 9-3 most common lock mode compatibility

	Existing authorization Mode
Request Mode	Is	S	U	IX	Six	X
Intention sharing (is) [wx2]	Yes	Yes	Yes	Yes	Yes	No
Share (s)	Yes	Yes	Yes	No	No	No
Update (u)	Yes	Yes	No	No	No	No
Intention exclusive (IX)	Yes	No	No	Yes	No	No
Intention exclusive sharing (six)	Yes	No	No	No	No	No
Exclusive (X)	No	No	No	No	No	No

The lock mode and compatibility are pre-defined by SQL Server and can be modified without any parameters or configurations.. However, you can control the time to apply for and release locks at the isolation level. For the four isolation levels, refer to four isolation levels: weak Database Consistency. However, the granularity of the applied locks can be affected by the database design. If the lock granularity applied by the application is small, the chance of blocking will be small. If a connection requests page-level, table-level, or even database-Level Lock resources,ProgramBlocking may occur.