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 locks: Resources locked by exclusive locks can only be used by lock operations, other operations on it will not be 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 resources 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 is set up 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 locks. Optimistic locks assume that when processing data, you can lock the record directly without doing anything in the application code, that is, you can manage the lock completely by relying on the database. 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 Automatic Management of database systems. Programmers need to directly manage data or lock processing on objects, and obtains, shares, and waives any lock on the data being used.