Locks in SQL Server can be grouped into the following categories

From a large class, locks in SQL Server can be categorized as follows:

Shared lock (S lock): used to read the lock added by the resource. A resource that has a shared lock cannot be modified. Shared locks are read by default and are released immediately. For example, if I read 100 data, I can imagine reading the first one, releasing the first one immediately, then locking the second data, releasing the second one, and locking the third one. And so on until the 100th article. This is why my query in Figures 9 and 10 requires that the isolation level be set to repeatable read, and that the S lock will last until the end of the transaction if you have set the level of isolation that can be read repeatedly or when using hints. In fact, you can add countless s locks on the same resource .

Exclusive lock (x Lock): incompatible with any other lock, including other exclusive locks. Exclusive locks are used for data modification, and when an exclusive lock is added to a resource, other transactions that request to read or modify the resource are blocked until the exclusive lock is released.

Update lock (U lock): U lock can be seen as a combination of S-lock and X-lock, used to update the data, update the data first need to find the updated data, at this time can be understood as the data found on the S lock. When the data that needs to be modified is found, the X lock on the resource being modified is required. SQL Server avoids deadlock problems with u locks. Because S-Lock and S-Lock are compatible, the U-Lock and S-lock are compatible to make the update lookup not affect the data lookup, and the U-Lock and U-Lock are not compatible, thus reducing the possibility of deadlock.

Source: >  

Locks in SQL Server can be grouped into the following categories