Shulou(Shulou.com)06/03 Report--

Today, I will talk to you about the nature and function of the SQLServer lock in the SQLServer database, which may not be well understood by many people. In order to make you understand better, the editor has summarized the following contents for you. I hope you can get something according to this article.

1. The essence of SQL Server lock

Definition of lock: lock is mainly used in multi-user environment to ensure database integrity and consistency.

Lock interpretation: when multiple users access data concurrently, multiple transactions will occur in the database to access the same data at the same time. If there is no control over concurrent operations, incorrect data may be read and stored, thus destroying the integrity and consistency of the database. When a transaction makes a request to the system and locks it before operating on a data object. After locking, the transaction has some control over the data object.

II. Classification of SQL Server locks

The classification of locks is described in two dimensions in textbooks and on the Internet. One dimension is divided according to the function of the lock, and the other is divided by concept.

1), by concept (from the programmer's point of view)

Pessimistic lock (Pessimistic Lock)

Optimistic lock (Optimistic Lock)

2), divided according to the function of the lock (from the point of view of the database system)

A shared lock (S) (Shared (S) Locks) is used for operations that do not change or update data (read-only operations), such as SELECT statements.

The update lock (U) (Update (U) Locks) is used in updatable resources. Prevents a common form of deadlock when multiple sessions are reading, locking, and possible subsequent resource updates.

Exclusive locks (X) (Exclusive (X) Locks) are used for data modification operations, such as INSERT, UPDATE, or DELETE. Ensure that multiple updates are not made to the same resource at the same time.

The intention lock (I) (Intent Locks) is used to establish the hierarchy of the lock. The types of intention locks are intention sharing (IS), intention exclusive sharing (IX), and intention exclusive sharing (SIX).

Third, understand the SQL Server database lock

1), shared lock (S)

A shared lock (S) (Shared (S) Locks) is used for operations that do not change or update data (read-only operations), such as SELECT statements.

2), update lock (U)

The update lock (U) (Update (U) Locks) is used in updatable resources. Prevents a common form of deadlock when multiple sessions are reading, locking, and possible subsequent resource updates.

3), exclusive lock (X)

Exclusive locks (X) (Exclusive (X) Locks) are used for data modification operations, such as INSERT, UPDATE, or DELETE. Ensure that multiple updates are not made to the same resource at the same time.

4), intention lock (I)

The intention lock (I) (Intent Locks) is used to establish the hierarchy of the lock. The types of intention locks are intention sharing (IS), intention exclusive sharing (IX), and intention exclusive sharing (SIX).

Fourth, how to avoid lock upgrade

1. The simplest and safest way to prevent lock escalation is to keep the transaction short and reduce the lock footprint of expensive queries so that the lock escalation threshold is not exceeded. There are several ways to achieve this goal. Break down a large number of operations into several smaller operations. When deleting a large amount of data, you can delete only 500 at a time and execute multiple times, which can significantly reduce the number of locks accumulated per transaction and prevent lock escalation.

2. Create an index to make the query as efficient as possible to reduce the locking space of the query. Without an index, a table scan may increase the likelihood of lock escalation, and even more frightening, it increases the likelihood of deadlocks and often has a negative impact on concurrency and performance. Create an appropriate index according to the query conditions to maximize the efficiency of the index lookup. One goal of this optimization is to make the index lookup return as few rows as possible to minimize the cost of the query.

The essence of deadlock

Deadlock (Dead Lock)

A deadlock is a state in which processes permanently block each other and may involve two or more processes. Deadlock refers to a permanent waiting state in which each process in a group of processes occupies resources that will not be released, but is in a permanent waiting state because of mutual applications for resources that will not be released by other processes.

How to prevent deadlock

First of all, it is necessary to understand that deadlocks are inevitable in a multi-concurrent environment, and deadlocks can only be reduced as much as possible through reasonable database design, good indexes, appropriate query statements and isolation levels.

At the beginning, the four necessary conditions for deadlock are listed, and deadlock can be avoided as long as any one or more conditions are broken. The following methods help minimize deadlocks:

A) access objects in the same order

B) avoid user interaction in transactions, that is, steps that do not include user interaction during transaction execution

C) keep the transaction short and in a batch

D) SELECT statement plus WITH (NOLOCK) prompt

SELECT * FROM TableName WITH (NOLOCK)

How to use locks to solve the problems caused by concurrency

1. What are the specific problems caused by concurrency?

In a multi-user environment, multiple users may update the same record at the same time, which can lead to conflicts. This is the famous concurrency problem.

1) dirty read: when one thing reads a record that completes half of the transaction, a dirty read occurs.

Example: user An and user B both see a value of 5, user B changes the value to 2, and user A still sees a value of 5, and then a dirty read occurs.

2), non-repeatable reading: if you get different values each time you read the data, it indicates that you have a problem of non-repeatable reading.

For example: the value seen by user An is 5, user B changes the value to 2, and the value seen by user An is still 5 after refreshing. At this time, non-repeatable reading occurs.

3), illusory rows: if the update and delect SQL statements do not affect the data, you are likely to encounter an illusory row problem.

For example: user A changes all values from 5 to 2, user B inserts a new record with a value of 2, and user A queries all records with a value of 2, but cannot find the newly added record, which is called illusory row.

4), update loss: the update of one transaction overrides the update result of other transactions, which is the so-called update loss.

Example: user A changes all values from 5 to 2, user B changes all values from 2 to 5, and user A loses his update.

2. How to solve the problem of concurrency

In order to solve the problems caused by concurrency. We need to introduce a concurrency control mechanism.

Pessimistic lock (Pessimistic Lock)-application scenario: write more

As the name implies, it is pessimistic. Every time I go to get the data, I think that someone else will modify the data, so it will be locked every time I get the data, so that other people will block until it gets the lock if they want to get the data.

Optimistic Lock (Optimistic Lock)-Application scenario: read more

As the name implies, I am optimistic that every time I go to get the data, I think that others will not change it, so I will not lock it, but when I update it, I will judge whether others have updated the data during this period.

1. Optimistic locks are suitable for multi-read applications, which can improve throughput.

2. Optimistic locks are generally implemented with a timestamp field (or a custom version number field).

In the actual production environment, if the concurrency is small and dirty reading is not allowed, pessimistic locks can be used to solve the concurrency problem; but if the concurrency of the system is very large, pessimistic locking will bring great performance problems, so we have to choose the optimistic locking method.

VIII. Thinking and summarizing

Optimistic lock complex transaction control

Optimistic locking: most are implemented based on the data version (version) recording mechanism. That is, to add a version identification to the data. In the version solution based on the database table, it is generally achieved by adding a "version" field to the database table to read out the data, the version number will be read out at the same time, and then when updated, add 1 to this version number. At this point, the version number of the submitted data is compared with the current version number of the corresponding record in the database table. If the submitted data version number is greater than the current version number of the database, it will be updated, otherwise it is considered to be out-of-date data.

After reading the above, do you have any further understanding of the nature and function of SQLServer locks in SQLServer databases? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.