Simple analysis of SQL Server lock, exclusive lock, shared lock, update lock, optimistic lock, pessimistic lock _mssql

There are two ways to classify locks.
(1) From the point of view of database system
Locks are grouped into the following three categories:

• Exclusive Lock (Exclusive lock)
A resource with exclusive lock locks is allowed only by programs that are locked, and no other action is accepted. When you perform a data Update command, which is the INSERT, UPDATE, or delete command, SQL Server automatically uses exclusive locks. However, you cannot add exclusive locks to an object when there are other locks on it. Exclusive locks are not released until the end of the transaction.

• Share lock (Shared lock)
A resource with a shared lock lock can be read by another user, but other users cannot modify it. When the SELECT command executes, SQL Server typically shares lock locks on the object. The shared lock is immediately freed when a data page with a shared lock is usually read.

• Updating locks (update lock)
Update locks are created to prevent deadlocks. When SQL Server prepares to update data, it first updates lock locks on the data object, so that the data cannot be modified but can be read. When SQL Server determines that the update data operation is to be performed, it automatically wraps the update lock to an exclusive lock. However, you cannot update lock locks on objects when there are other locks on them.

(2) from the programmer's point of view
Locks are grouped into the following two categories:

• Optimistic lock (optimistic lock)
Optimistic locks assume that when working with data, you do not need to do anything in your application's code to lock the record directly, that is, relying entirely on the database to manage the lock. Typically, SQL Server locks the table that is updated in the transaction scope automatically when the transaction is performed.

• Pessimistic lock (pessimistic lock)
Pessimistic lock for the automatic management of database system not a cold, requires the programmer to directly manage data or object lock processing, and is responsible for acquiring, sharing and discarding any locks on the data being used.