SQL Server transaction Acid

Introduction

 

Acid refers to the four features that transactions should have in a reliable database management system (DBMS): atomicity, consistency, and isolation), durability ). this is what a reliable database should have. the following describes these features one by one.

Understanding atomicity)

Atomicity means that the transaction execution in the database is as an atom. That is, the entire statement can be either executed or not executed.

In SQL Server, each separate statement can be considered to be contained in a transaction by default:

Therefore, each statement is atomic or executed in whole, so that all statements are not executed and there is no intermediate state:

As mentioned above, each T-SQL statement can be considered to be wrapped in a transaction by default, SQL server implements atomicity for each individual statement, however, this atomic granularity is very small. If you want to customize the atomic size, you need to include it in the transaction to constitute the custom atomic granularity:

For users, the custom atomicity implemented by transactions is often related to the business, such as bank transfers, which subtract 100 from account A and increase by 100 in account B, if the two statements do not guarantee atomicity, for example, if a server loses power after a minus 100, the server does not increase by 100 in B's account. although this situation will make the bank very happy, you don't want this result as a developer. in the default transaction, the entire transaction will not be rolled back even if an error occurs. Instead, an exception is thrown for a failed statement, and the correct statement is successfully executed. This will destroy atomicity. Therefore, SQL Server provides some options to ensure the atomicity of transactions.

SQL Server provides two types of methods to ensure the atomicity of custom transactions:

1. Set xact_abort on to ensure that the transaction conforms to the atomicity.

Set the xact_abort option to on to set all transactions as an atomic process. the following example inserts two statements into the database. After the set xact_abort on option is enabled, the transaction is atomic:

2. Perform rollback based on user settings)

This method is more flexible. developers can customize the rollback conditions and use try catch statements and @ error to determine the conditions.

 

Consistency)

Consistency: The integrity constraints of the database are not damaged before and after the transaction starts.

Consistency is divided into two levels

1. database mechanism level

Database-level consistency is that data will comply with the constraints you set (unique constraints, foreign key constraints, check constraints, etc.) and trigger settings before and after a transaction is executed. this is guaranteed by SQL Server.

 

2. Business Layer

At the business level, consistency is to maintain business consistency. this business consistency needs to be ensured by developers. many business consistency can be ensured by transferring to the database mechanism layer. for example, if the product has only two models, you can transfer them to the check constraint so that only the two models can be saved in a column.

Understanding isolation)

Isolation. The execution of a transaction does not interfere with each other. It is impossible for a transaction to see the data at a certain time point in the middle of other transactions.

In Windows, if multiple processes cannot modify the same file, Windows ensures the isolation of different processes in this way:

In SQL Server, database files are managed through SQL Server, so that multiple processes can access the database at the same time:

SQL Server uses locking and blocking to ensure isolation between transactions at different levels.

In general, full isolation is unrealistic. full isolation requires the database to execute only one transaction at a time. Such performance can be imagined. to understand the isolation protection of SQL Server, you must first understand how transactions interfere.

The interaction between transactions can be divided into several types: Dirty read (dirty read), non-repeated read, phantom read

 

Dirty read

Dirty reading means that one transaction reads the uncommitted data of another transaction, and the data may be rolled back:

Here is an example:

For two transactions, transaction a inserts a piece of data but is not committed. During this period, transaction B reads uncommitted data from transaction A, causing dirty reads.

 

Unrepeatable read)

Non-repeated read means that two identical queries within the transaction range in Database Access return different data. This is caused by the commit of other transaction modifications in the system during the query.

Here is an example of non-repeated reading:

Transaction B executes a query twice. When the first execution is complete, transaction a modifies its data. When transaction B queries again, the data changes:

Phantom read)

Phantom read refers to a phenomenon that occurs when a transaction is not executed independently. For example, the first transaction modifies the data in a table, which involves all the data rows in the table. At the same time, the second transaction also modifies the data in this table. This modification inserts a new row of data into the table. In the future, the user who operates the first transaction will find that there are still unmodified data rows in the table, just like an illusion.

Here is an example:

Transaction B updates all the data in the table. During this period, transaction a inserts a piece of data. After transaction B queries again, it finds that there are still unmodified data, which generates Phantom reads:

 

Understanding the isolation level in SQL Server

To avoid the impact between the preceding transactions, SQL Server sets different isolation levels to avoid different levels. Because a high isolation level means more locks at the cost of performance, this option is open to users based on specific needs. However, the default isolation level read commited meets the actual needs of 99%.

The impact of SQL Server isolation between transactions is achieved through locks. This concept is complicated and will not be described in detail in this article. Blocking will prevent the above effect.

SQL Server provides five options to avoid the impact between transactions at different levels.

The isolation levels are as follows:

Read uncommited)

Read commited (non-repeated, phantom read may occur)

Repeatable read (phantom read may occur)

Serializable (with the lowest performance, the range lock will lead to lower concurrency)

Snopshot)

 

In short, different isolation levels are achieved by adding different locks, resulting in blocking. Let's look at an example:

SQL Server blocks dirty reads by blocking them, so maintaining independence is at the cost of performance:

 

Understanding durability)

Durability means that after the transaction is completed, the changes made by the transaction to the database will be permanently stored in the database and will not be rolled back.

Even if any accident occurs, such as power failure, once a transaction is committed, it is stored in the database persistently.

SQL Server uses write-ahead transaction log to ensure durability. Write-ahead transaction log indicates that changes to the database in the transaction are first written to the transaction log before being written to the database. Transaction logs are sorted by lsn ). When the database crashes or the server breakpoint occurs, restart SQL Server. SQL Server first checks the log sequence number and persists some of the changes made to the database to ensure the durability.

 

Summary

This article briefly introduces the concept of Acid and Its Implementation in SQL Server. acid is just a concept, not a specific technology. acid is a prerequisite for reliable databases for robust databases.