SQL server lock, exclusive lock, shared lock, update lock, optimistic lock, pessimistic lock

There are two lock classification methods.
(1) from the perspective of the Database System
There are three types of locks:

• Exclusive Lock)
A resource with an exclusive lock can only be used by locked programs. Other operations on the resource are not accepted. SQL Server automatically uses an exclusive lock when executing the data UPDATE command, namely the INSERT, UPDATE, or DELETE command. However, an exclusive lock cannot be applied to an object when other locks exist. The exclusive lock cannot be released until the transaction ends.

• Shared Lock)
A shared lock can be read by other users, but cannot be modified by other users. When the SELECT command is executed, SQL Server usually locks the object through a shared lock. Generally, after the data page with the shared lock is read, the shared lock will be released immediately.

• Update Lock)
Update locks are created to prevent deadlocks. When SQL Server is preparing to update data, it first locks the data object so that the data cannot be modified but can be read. When SQL Server determines that it wants to update data, it will automatically replace the update lock with an exclusive lock. However, when other locks exist on the object, the update locks cannot be locked.

(2) From the programmer's perspective
There are two types of locks:

• Optimistic Lock)
Optimistic locks assume that when processing data, you can directly lock the record without doing anything in the application code, that is, you can rely entirely on the database to manage the lock. Generally, when a transaction is processed, SQL Server automatically locks the table updated within the transaction processing range.

• Pessimistic Lock)
Pessimistic locks do not catch a cold in the Automatic Management of database systems. Programmers need to directly manage data or lock processing on objects and acquire, share, and discard any locks on the data being used.