SQL Server transactions and locks (in the previous article)

An overview

In the database aspect, for the non-DBA programmer, the transaction and the lock is a big difficulty, according to this difficulty, this article attempts to use the picture and text way to discuss with everybody.

"Talking about SQL Server transaction and lock" This topic is divided into two articles, the last one on the issue of transactional and transactional consistency, and briefly mentions the type of lock and the level of control of the lock.

The next section lectures on locking mechanisms in SQL Server, lock control levels, and several issues with deadlocks.

Two-business

1 What is a transaction

Previewing a number of books, the definition of a transaction, the different authors of different literature, although the nuances are roughly uniform, we generalize it as follows:

Transactions: A single unit of work that encapsulates and performs single or multiple operations, in SQL Server, whose definition appears as both a display definition and an implicit definition.

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

(1) A transaction is a single unit of work, a definition that makes a transaction have acid properties

(2) Transactions are encapsulated operations, such as encapsulation of basic CRUD operations

1  --Transactions2 Begin Tran3 SELECT *  fromUserInfo4 INSERT  intoUserInfoVALUES('alan_beijing', *)5 UPDATEUserInfoSETAge= to WHEREUserName='alan_beijing'6 DELETEUserInfoWHEREUserName='alan_beijing'7 Commit Tran

(3) Transactions can encapsulate a single operation or encapsulate multiple operations when encapsulating operations (when encapsulating multiple operations, be aware of the difference between batching)

(4) In SQL Server, the definition of a transaction is divided into a display definition and an implicit definition of two ways

Display definition: Begin with begin Tran, where COMMIT transaction is commit Tran, ROLLBACK TRANSACTION is rollback Tran, as we insert two action statement in a transaction

1 --Show definition Transactions2 Begin Tran3 INSERT  intoUserInfoVALUES('Alan_shanghai', -)4 INSERT  intoUserInfoVALUES('alan_beijing', *)5 Commit Tran

Implicit definition: If a defined transaction is not displayed, SQL Server defaults to processing each statement as a transaction (commits the transaction automatically after executing each statement)

2 ACID properties of a transaction

A transaction acts as a single unit of work, which has an acid property, an acid attribute that is atomic (atomicity), consistency (consisitency), isolation (isolation), and persistence (durability).

(1) atomicity (atomicity)

Atomicity means that a transaction must be an atomic unit of work, that is, the encapsulation operation of a transaction, either all or none of the execution. The following conditions can cause a transaction to be undone or rolled back ...

A. Before the transaction commits, the system fails or restarts, and SQL Server revokes all operations in the transaction;

B. When an error is encountered in a transaction, SQL Server usually rolls back the transaction automatically, but with a few exceptions;

C. Some less severe errors do not cause automatic rollback of transactions, such as primary key collisions, lock timeouts, etc.;

D. Error handling code can be used to catch some errors, and take appropriate action, such as logging errors in the log, and then rolling back the transaction, etc.;

(2) Consistency (consisitency)

Consistency mainly refers to data consistency, that is, the primary object is data. From the macroscopic point of view, refers to a certain period of time, the data to maintain a consistent state, from the microscopic, some time points of data to maintain a consistent state, we give an example,

If two transactions A and B operate on the same table, a writes the data to the table, and B reads the data into the data table, it is possible to guess that the data read by B is roughly three coarse-grained:

The first possibility: A has not yet written to the data table the state of the data;

The second possibility: A has written some data to the data table, but has not finished the state;

A third possibility: A has finished writing data to the data table;

This creates inconsistencies in the transaction.

With respect to transactional consistency, there may be issues such as missing updates, dirty reads, non-repeatable reads, and Phantom reads, which are discussed in detail in these transactional consistency issues.

(3) Isolation (isolation)

Isolation means that when two or more transactions operate on the same boundary resource, control the boundaries of each transaction, control the data access mechanism, and ensure that the transaction can only access data at the desired level of conformance.

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

(4) Persistence (durability)

When we operate on a data table, we typically perform the following two steps in order of sequence:

The first step is to write the data table operation to the transaction log of the database on disk (persisted to the disk transaction log);

Second step: After completing the first step, the data table operation is then written to the data partition of the database on disk (persisted to the database partition on disk);

As for the previous two steps, let's consider what might be the problem:

Issue 1: What does the database engine do if the system fails (such as system exceptions, System restarts) before the first step is completed?

Because the first step is not completed, the commit instruction is not logged to the disk's transaction log, and the transaction is not persisted, and after the system fails, the SQL Server

The transaction log for each database is checked for recovery processing (recovery processing is generally divided into redo and undo phases), at which point the recovery process is done in the redo phase, where the commit instruction is not logged to the disk's transaction log.

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

Issue 2: Complete as of the first step but not yet completed the second step, the system fails (such as system exception, system restart), what will the database engine do?

After the first step, the commit instruction is logged to the disk's transaction log, regardless of whether the data operation is written to the disk's data partition, when the transaction is persisted, the system fails, and the SQL Server

The transaction log for each database is checked for recovery processing (recovery processing is generally divided into redo and undo phases), at which point the recovery process is done at the same time, that is, because the data modification has not yet applied to the data partition of the transaction,

The database engine will redo all the modifications made by these transactions, and the process will be rolled forward.

Consistency issues arising from isolation levels and isolation levels for three transactions

1 uncommitted read (READ UNCOMMITTED)

Uncommitted read (read UNCOMMITTED) refers to reading uncommitted data, at which time the resulting data inconsistency, which we call data dirty read.

1.1 Uncommitted reads why data dirty reads are generated

Uncommitted reads are the lowest level of isolation, transactions that run at this isolation level, read operations do not require a shared lock, and if a read operation does not require a shared lock, there is no conflict with the transaction operation that holds the exclusive lock.

So that is, at this transaction isolation level, the read operation can be performed concurrently with the write operation, and the read operation can read the uncommitted modifications of the write operation, resulting in inconsistencies in the data, in which case we call

Data dirty read.

1.2 Schematic data Dirty read

1.3 SQL Demo Data dirty read

2 committed read (read COMMITTED)

Read COMMITTED refers to data that can read only committed transactions, is the lowest level of isolation to prevent data from being dirty, and is the default isolation level for SQL Server, which requires that read operations must obtain a shared lock

Can be manipulated to prevent read uncommitted modifications, although read-committed prevents data from being dirty-read, but is inevitably not repeatable read data consistency issues.

2.1 Why Read committed prevents data from being read dirty

Read Committed only allows reading the data committed by the transaction, it requires that the read operation must obtain a shared lock in order to operate, while the read operation of the shared lock and the write operation exclusive lock is mutually exclusive, the two mutually exclusive conflicts, so the read operation

When reading data, you must wait for the write operation to complete before you can acquire a shared lock before you can read the data, which is the data that has been committed at the end of the commit, thus preventing the data from being dirty read.

2.2 SQL Demo submitted read

2.3 Why a read submitted will produce non-repeatable read issues

We know that although read-committed reads can obtain a shared lock, a shared lock on the resource is immediately freed when the read operation is complete (the operation does not consistently retain the shared lock during the duration of the transaction), thus creating a problem

That is, there is no shared lock that locks the resource between reads of the same data resource within a transaction, causing other transactions to change the data resource between two read operations, which may thus get different

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

2.4 Graphical non-repeatable reading

3 REPEATABLE READ (REPEATABLE Read)

To prevent non-repeatable reads, an isolation level upgrade is used in SQL sever to escalate read-committed reads to repeatable reads. Under the REPEATABLE READ isolation level, read operations in transactions can not only acquire shared locks,

And the acquired shared lock remains until the transaction is complete, and no other transaction can obtain an exclusive lock to modify the data until the transaction is complete, thus enabling repeatable reads, preventing non-repeatable reads

Data inconsistencies. Repeatable reading not only solves the problem of non-repeatable read data inconsistency, but also resolves the problem of missing updates. However, there are problems with repeatable reading, which is deadlock and phantom reading.

3.1 SQL Demo Repeatable read

3.2 What is missing update?

In isolation levels that are lower than repeatable reads, two transactions no longer hold any locks on the resource after reading the data, at which point two transactions can update the value.

Thus, the value of the last transaction update overwrites the value of the previous transaction update, resulting in data loss, which is known as missing updates.

3.3 Graphical missing updates

4 Serializable (SERIALIZABLE)

4.1 What is phantom reading?

We know that under the repeatable read isolation level, shared locks held by read transactions persist until the transaction completes, but the transaction locks only those data resources (such as rows) that were found when the query was first run.

Instead of locking out rows other than the query result range (in fact, there are database schema levels, tables, pages, rows, and so on when controlling transactions). So, before a second read in the same transaction, if anything else

New rows, and the new row satisfies the query filter for the read operation, the new rows also appear in the results returned by the second read operation, which is called Phantom Shadows, also called Phantom reads.

4.2 Graphical Phantom Reading

4.3 How to solve phantom reading?

In SQL Server, a higher level of serializable (SERIALIZABLE) resolves the problem.

4.4 What is serializable (SERIALIZABLE)?

Most of the time, the serializable (SERIALIZABLE) isolation level is handled in a similar way and repeatable, except that the serializable (SERIALIZABLE) Isolation level

Added a new content-logically, this isolation level causes the read operation to lock the entire range of keys that satisfy the query's search criteria, which means that the read operation not only locks the query that satisfies the search

The existing rows of the condition also lock the rows that might satisfy the query search criteria in the future.

5 SNAPSHOT

Slightly.

Summary of isolation levels for four transactions

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

Five lock

12 types of concurrency control models

There are two main types of concurrency control model, namely pessimistic control model and optimistic control model.

(1) Pessimistic control model: The model assumes that there are always multiple transactions for the same resource operation (read/write), that is, the assumption that conflicts always occur. In SQL Server, the use of transactional

The isolation level to control (also known as the use of locks to control). Generally in the event of a conflict before the control, also known as pre-control;

(2) Optimistic control model: The model is antagonistic to the pessimistic control model, that is, the model always assumes that the system does not exist or there are fewer transactions on the same resource operation (read/write)

, that is, the assumption that the conflict will not occur or seldom occur. In SQL Server, row versioning is used for processing. is usually controlled after a transaction conflict, also known as an afterthought

Control

2 What is the type of lock and lock

2.1 What is a lock

Locking refers to the means by which data consistency is ensured during concurrent operations. In SQL Server, the locking mechanism and the transaction isolation level are used to control the consistency of the data.

2.2 Types of Locks

Commonly used four types of locks include: Shared, intent, update and exclusive locks.

(1) Shared Lock: In SQL Server, you need to acquire a shared lock when the transaction is reading data.

(2) Intent Lock: In SQL Server, the intent lock is not an independent lock, and its main function is to obtain the control granularity of the lock (for example, page, table, row, etc.).

(3) Update lock: In SQL Server, the update lock is not an independent lock, but a hybrid lock consisting of a shared and exclusive lock with a higher isolation level than a shared lock.

Below an exclusive lock, the update lock prevents deadlock caused by lock escalation.

(4) Exclusive locks: In SQL Server, you need to acquire an exclusive lock when the transaction is writing data, finer data, and deleting data.

3 control granularity of the lock

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

Vi. references

"01" Microsoft SQL Server 2008 Insider: T-SQL language Basics

"02" Microsoft SQL Server 2008 Tech Insider: T-SQL query

Seven copyright areas

• Thank you for your reading, if there are shortcomings, welcome advice, common learning and common progress.
• Bo main website: http://www.cnblogs.com/wangjiming/.
• A very small number of articles using reading, reference, reference, copying, copying and pasting into a variety of ways, most of the original.
• If you like, please recommend, if you have new ideas, welcome, email: [Email protected].
• The blog can be reproduced, but must be well-known from the blog source.

SQL Server transactions and locks (in the previous article)