MS SQL Basics Tutorial: Locks in a database

The concept of 11.5.1 lock

Lock is a limitation of access to resources in a multiuser environment. Mechanism when a data source is locked, the data source has a certain access limit. We call this data source "locked". In SQL Server, you can lock on the following objects:

Data row (ROW): a single row of data in a data page; index row (key): a single row of data in an index page, which is the key value of an index; page (page): A basic unit of SQL Server Access data with a size of 8KB; Panel (Extent): A disk area consists of 8 consecutive pages; Tables (table), database. Categories of 11.5.2 Locks

In SQL Server, 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)