Why SQL Server database locks are introduced

Today, we will mainly introduce SQL Server database locks, the reasons for introducing SQL server data locks, and the two methods for classifying locks, the following is a description of the specific content. I hope it will be helpful for your future study.

I. Why should I introduce locks?

When multiple users concurrently perform database operations, the following data inconsistency occurs:

Update loss

A and B read and modify the same data. The Modification result of one user destroys the Modification result of another user, such as the ticket booking system.

Dirty read

User a modified the data, and user B then read the data again. However, user a canceled the data modification and restored the original value for some reason, the data obtained by B is inconsistent with the data in the database.

Non-repeated read

User A reads the data, and user B then reads the data and modifies the data. At this time, user a reads the data again and finds that the values of the first and second times are inconsistent.

The main method of concurrency control is to lock the SQL Server database. The lock is to prohibit users from performing certain operations within a period of time to avoid data inconsistency.

Binary lock Classification

There are two lock types:

1. From the perspective of the database system:

Exclusive locks (exclusive locks), shared locks, and update locks

MS-SQL server uses the following resource lock modes.

Lock mode description

Share (s) is used for operations without changing or updating data (read-only operations), such as select statements.

Update (u) is used in updatable resources. Prevents Common deadlocks when multiple sessions are read, the SQL Server database is locked, and possible subsequent resource updates.

Arrange it (x) for data modification operations, such as insert, update, or delete. Make sure that multiple updates are not performed for the same resource at the same time.

Intention locks are used to establish a lock hierarchy. The intention lock type is: Intention sharing (is), intention ranking (IX), and intention ranking sharing (six ).

The schema lock is used to perform operations dependent on the table schema. The schema lock types are: schema modification (Sch-m) and schema stability (Sch-S ).

Large-capacity Update (BU) is used to copy data to a table in large capacity and specify the tablock prompt.

Shared lock

The share (s) Lock allows concurrent transactions to read (select) a resource. When a shared (s) lock exists on the resource, no other transactions can modify the data. Once the data has been read, the shared (s) lock on the resource is released immediately, unless the transaction isolation level is set to repeated read or higher, or use the lock prompt to keep the share (s) Lock within the transaction lifecycle.

Update lock

Update (u) locks can prevent normal deadlocks. Generally, the update mode is composed of a transaction. The transaction reads the record, obtains the share (s) lock of the resource (page or row), and then modifies the row, this operation requires that the lock be converted to an exclusive (x) Lock. If two transactions obtain the Shared Mode Lock on the resource and attempt to update the data at the same time, a transaction attempts to convert the lock to the lock (X. The conversion from the sharing mode to the exclusive lock must wait for a while, because the exclusive lock of a transaction is incompatible with the Sharing Mode Lock of other transactions; a lock wait occurs. The second transaction attempts to obtain the row lock (x) for update. Because both transactions need to be converted to the (x) lock, and each transaction waits for another transaction to release the SQL Server database lock in the sharing mode, a deadlock occurs.

To avoid this potential deadlock problem, use the update (u) Lock. Only one transaction can obtain the resource Update (u) Lock at a time. If the transaction modifies the resource, the update (u) Lock is converted to the row (x) Lock. Otherwise, the lock is converted to a shared lock.

Exclusive lock

Locking (x) prevents concurrent transactions from accessing resources. Other transactions cannot read or modify the data locked by the lock (X.

Intention lock

The intention lock indicates that SQL server needs to obtain the share (s) lock or arrange it (x) Lock on some underlying resources in the hierarchy. For example, a table-level share intention lock indicates that the transaction intends to place the share (s) lock on the page or row of the table. Setting the intention lock at the table level can prevent another transaction from getting the row lock (X) on the table containing that page. Intention locks can improve performance, because SQL Server only checks intention locks at the table level to determine whether transactions can safely obtain the locks on the table. Instead of checking the locks on each row or page in the table to determine whether the transaction can lock the entire table.

Intention locks include intention sharing (is), intention arranging it (IX), and intention sharing (six ).

Lock mode description

By placing the S lock on each resource, intention sharing (is) indicates that the transaction intends to read some (not all) of the underlying resources in the hierarchy.

By placing the X lock on each resource, the intention of the transaction is to modify some (rather than all) underlying resources in the hierarchy. IX is the superset of is.

By placing an iX lock on each resource, Six shares with the intention to indicate that the transaction intends to read all the underlying resources in the hierarchy and modify some (rather than all) of the underlying resources. Allow concurrent is locks on top-level resources. For example, the table's six lock places a six lock on the table (the concurrency is allowed), and the IX lock on the current modification page (the X lock on the modified row ). Although each resource can have only one six lock for a period of time, to prevent other transactions from updating resources, however, other transactions can read the underlying resources in the hierarchy by obtaining the table-level is lock.

Exclusive lock:

Only the lock operation is allowed by the program. Other operations on the program will not be accepted. When the data update command is executed, SQL Server automatically uses the exclusive lock. An exclusive lock cannot be applied to an object when other locks exist.

Shared lock: the shared lock can be read by other users, but other users cannot modify it. When executing select, SQL Server will apply a shared lock to the object.

Update lock:

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. When other locks exist on the object, it cannot be updated.

2. From the programmer's perspective: Optimistic locks and pessimistic locks.

Optimistic lock: it relies entirely on the database to manage the lock.

Pessimistic lock: programmers manage SQL Server database lock processing on data or objects themselves.

The MS-SQLSERVER uses locks to implement pessimistic concurrency control among users who execute modifications simultaneously in the database