Shulou(Shulou.com)05/31 Report--

This article will explain in detail the example analysis of transactions and locks in SQL Server. The editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.

An overview

In terms of database, transaction and lock is a major difficulty for non-DBA programmers. In view of this difficulty, this article attempts to use the way of picture and text to discuss with you.

The topic "talking about SQL Server transactions and locks" is divided into two parts. The first part focuses on transactions and transaction consistency, and briefly mentions the types of locks and the control level of locks.

Two affairs

1 what is a transaction

Preview many books, for the definition of transaction, different literature and different authors have slight differences but generally unified, we summarize it abstractly as follows:

Transaction: a single unit of work that encapsulates and performs single or multiple operations. In SqlServer, its definition is represented by both explicit definition and implicit definition.

Based on the above definition, we can split the transaction anatomy into the following points:

(1) the definition that a transaction is a single unit of work makes a transaction have an ACID attribute.

(2) transactions encapsulate operations, such as basic CRUD operations

Transaction Begin Tran SELECT * FROM UserInfo INSERT INTO UserInfo VALUES ('Alan_beijing',35) UPDATE UserInfo SET Age=31 WHERE UserName='Alan_beijing' DELETE UserInfo WHERE UserName='Alan_beijing' Commit Tran

(3) when encapsulating an operation, a transaction can encapsulate a single operation or multiple operations (when encapsulating multiple operations, you should pay attention to the difference from batch processing)

(4) in SqlServer, transaction is defined in two ways: explicit definition and implicit definition.

Display definition: start with Begin Tran, where commit transaction is Commit Tran and rollback transaction is RollBack Tran, for example, we insert two operation statements in a transaction

-- displays the definition transaction Begin TranINSERT INTO UserInfo VALUES ('Alan_shanghai',30) INSERT INTO UserInfo VALUES (' Alan_beijing',35) Commit Tran

Implicit definition: if the definition transaction is not displayed, SQL Server defaults to treat each statement as a transaction (the transaction is automatically committed after each statement is executed)

2 the ACID attribute of the transaction

The definition of a transaction as a single unit of work gives it the ACID attribute, which refers to Atomicity, Consisitency, Isolation, and Durability.

(1) atomicity (Atomicity)

Atomicity means that the transaction must be an atomic unit of work, that is, the encapsulation operation of the transaction is either performed or not performed at all. The following circumstances will cause the transaction to be undone or rolled back.

a. When the system fails or restarts before the transaction commits, SQL Server will undo all operations performed in the transaction

b. SQL Server usually rolls back transactions automatically when errors are encountered in the transaction, but there are a few exceptions

c. Some less serious errors do not cause automatic rollback of transactions, such as primary key conflicts, lock timeouts, etc.

d. You can use error handling code to catch some errors and take appropriate actions, such as recording the errors in the log, and then rolling back the transaction

(2) consistency (Consisitency)

Consistency mainly refers to data consistency, that is, the main object is data. From a macro point of view, it refers to a certain period of time, the data should maintain a consistent state, from a micro point of view, the data should be consistent at a certain point in time. Let's give an example.

If there are two transactions An and B operating on the same table, A writes data to the table, and B reads data into the data table, it can be guessed that there are three kinds of coarse-grained data read by B:

The first possibility is that A has not yet written data to the data table.

The second possibility: a state in which part of the data has been written to the data table but has not yet been written.

The third possibility: a has written the data to the data table

In this way, it causes the inconsistency of the transaction.

With regard to transaction consistency, there may be lost updates, dirty reads, unrepeatable reads, and phantom reads, which are discussed in detail below.

(3) isolation (Isolation)

Isolation means that when two or more transactions operate on the same boundary resource, it is necessary to control the boundary of each transaction and the data access mechanism to ensure that the transaction can only access the data under the desired consistency level.

In SQL Server, the lock mechanism is generally used to control, which we will discuss in detail below.

(4) persistence (Durability)

When we operate on the data table, we usually perform the following two steps in sequence:

Step 1: write the data table operation to the transaction log of the database on disk (persisted to the disk transaction log)

Step 2: after completing the first step, write the data table operation to the data partition of the database on disk (persisted to the database partition on disk)

With regard to the above two steps, let's consider the problems that may arise:

Question1: what will the database engine do if the system fails (such as system exception, system restart) before completing the first step above?

Because the first step is not completed, the commit instruction has not been recorded in the transaction log of the disk, and the transaction is not persisted. After the system failure, SQL Server

The transaction log of each database is checked for recovery processing (recovery processing is generally divided into redo phase and undo phase). At this time, the recovery process is redo phase, that is, the commit instruction has not been recorded in the transaction log on disk.

The database engine undoes all changes made by these transactions, and the process becomes a rollback.

Question 2: after completing the first step but not the second step, if the system fails (such as system exception, system restart), what will the database engine do?

After completing the first step, the commit instruction is recorded in the transaction log of the disk, regardless of whether the data operation is written to the data partition of the disk, the transaction has been persisted, and after the system failure, SQL Server

The transaction log of each database is checked for recovery processing (recovery processing is generally divided into redo phase and undo phase). At this time, the recovery process is redo phase, that is, transactions that have not been applied to data partitions because data modifications have not been applied.

The database engine redoes all the changes made by these transactions, and the process is rolled forward.

The consistency problems caused by the isolation level and isolation level of three transactions

1 unsubmitted read (READ UNCOMMITTED)

Uncommitted READ UNCOMMITTED refers to reading unsubmitted data, which results in data inconsistency, which is called data dirty read.

1.1 Why does an unsubmitted read produce a dirty read of the data?

An uncommitted read is the lowest isolation level. For a transaction running at this isolation level, the read operation does not need to request a shared lock. If the read operation does not require a shared lock, it will not conflict with the transaction operation that holds the exclusive lock.

That is to say, at this transaction isolation level, the read operation and the write operation can be carried out at the same time and are not mutually exclusive, and the read operation can read the uncommitted changes of the write operation, resulting in data inconsistency. In this case, we call

Dirty reading of the data.

1.2 graphical data dirty reading

1.3 SQL presentation data dirty read

2 read submitted (READ COMMITTED)

Committed read (READ COMMITTED) means that you can only read data from committed transactions, which is the minimum isolation level to prevent dirty reading of data. It is also the default isolation level for SQL Server. It requires that the read operation must obtain a shared lock.

In order to operate to prevent reading of uncommitted changes, although committed reads can prevent dirty reads of data, it is inevitable that data consistency problems cannot be repeated.

2.1 Why a read has been submitted to prevent dirty reading of data

Committed reads are only allowed to read the committed data of the transaction, which requires that the read operation must obtain a shared lock in order to operate conscientiously, while the shared lock of the read operation and the exclusive lock of the write operation are mutually exclusive, so the read operation conflicts with each other.

When reading data, you must wait for the write operation to complete before you can obtain the shared lock, and then read the data. At this time, the data read is the data that has been submitted, so the problem of dirty reading of the data is prevented.

2.2 SQL demo submitted for reading

2.3 Why does the submitted read cause the problem of unrepeatable reading?

We know that although committed reads can acquire shared locks, the shared lock on the resource is released as soon as the read operation is complete (the operation does not consistently retain the shared lock for the duration of the transaction), thus creating a problem

That is, between read operations on the same data resource within a transaction, no shared lock will lock the resource, causing other transactions to change the data resource between two read operations, so that the read operation may get a different

Value, this phenomenon is called non-repeatable reading of data.

2.4 diagrams cannot be read repeatedly

3 repeatable read (REPEATABLE READ)

In order to prevent the phenomenon of unrepeatable readings, the isolation level upgrade is adopted in SQL Sever, that is, the submitted reads are upgraded to repeatable reads. At the repeatable read isolation level, read operations in a transaction not only acquire shared locks

Moreover, the acquired shared lock is maintained until the transaction is completed, and it is impossible for other transactions to obtain an exclusive lock to modify this data before the transaction is completed. In this way, repeatable reading is realized and non-repeatable reading is prevented.

The data of Cheng is inconsistent. Repeatable reading can not only solve the problem of non-repeatable read data inconsistency, but also solve the problem of missing updates. However, there are also some problems with repeatable reading, such as deadlock and phantom reading.

3.1 SQL demo can be read repeatedly

3.2 what is missing updates?

At an isolation level lower than repeatable readability, two transactions no longer hold any locks on the resource after reading the data, and both transactions can update this value

As a result, the value of the final transaction update overrides the value of the previous transaction update, resulting in the loss of data, which is called lost update.

3.3 graphic missing updates

4 serializable (SERIALIZABLE)

4.1 what is phantom reading?

We know that at the repeatable read isolation level, the read transaction holds a shared lock until the transaction is completed, but the transaction locks only those data resources (e.g., rows) found when the query is run for the first time.

Other rows outside the scope of the query results are not locked (in fact, when controlling transactions, there are database schema levels, tables, pages, rows, etc.). So, before making a second read in the same transaction, if anything else

Be sure to insert new rows, and the new rows meet the query filtering criteria for the read operation, then these new rows will also appear in the results returned by the second read operation. These new rows are called phantom shadows, also known as phantom reads.

4.2 graphic illusions

4.3 how to solve phantom reading?

In SQL SERVER, a higher level of serialization (SERIALIZABLE) can solve this problem.

4.4 what is serialization (SERIALIZABLE)?

Most of the time, SERIALIZABLE isolation levels are handled in a similar way to repeatable isolation levels, except that serializable isolation levels are SERIALIZABLE

A new content has been added-logically, this isolation level allows the read operation to lock the entire range of keys that meet the query search criteria, which means that the read operation not only locks the query search

The existing rows of the condition also lock rows that may meet the query search criteria in the future.

5 SNAPSHOT

A little.

Summary of isolation levels of four transactions

The following table summarizes each isolation level and logical consistency issues, the relationship between detection conflicts and row versioning

Five locks

1 two concurrency control models

There are mainly two kinds of concurrency control models, namely pessimistic control model and optimistic control model.

(1) pessimistic control model: this model assumes that there are always multiple transactions operating on the same resource (read / write), that is, it is assumed that conflicts will always occur. In SQL Server, adopt transactions

Control by isolation level (also known as lock control). Control is usually carried out before a conflict occurs, which is also called prior control.

(2) optimistic control model: this model is opposed to the pessimistic control model, that is, the model always assumes that there are no or fewer transactions in the system operating on the same resource (read / write).

That is, it is assumed that conflicts will not occur or rarely occur. In SQL Server, row versioning is used to deal with it. Control is usually carried out after a conflict, also known as after the event.

Control

2 what is locking and the kind of locking

2.1 what is locking

Locking refers to a means used to ensure the consistency of data in concurrent operations. In SQL Server, lock mechanism and transaction isolation level are used to control data consistency.

2.2 types of locks

Four types of locks are commonly used: shared lock, intention lock, update lock and exclusive lock.

(1) shared lock: in SQL SERVER, when a transaction wants to read data, it needs to acquire a shared lock.

(2) intention lock: in SQL SERVER, exactly, the intention lock is not an independent lock, its main function is to obtain the control granularity of the lock (e.g., page, table, row, etc.).

(3) Update lock: in SQL SERVER, update lock is not exactly an independent lock, but a hybrid lock composed of shared lock and exclusive lock with higher isolation level than shared lock.

Lower than exclusive locks, updated locks can prevent deadlocks caused by lock escalation.

(4) exclusive lock: in SQL SERVER, when a transaction wants to write data, fine data, and delete data, it needs to acquire exclusive lock.

3 control granularity of lock

In SQL SERVER, locks can control resources such as tables, pages, and rows.

This is the end of this article on "sample analysis of transactions and locks in SQL Server". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

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.