Four major features of transactions: atomicity, consistency, isolation and durability (ACID), four major acid

Four major features of transactions: atomicity, consistency, isolation and durability (ACID), four major acid

1. Transactions

Definition: a transaction is a sequence of operations. These operations are either executed or not executed. It is an inseparable unit of work.

Preparation: To illustrate the ACID principle of the transaction, we use the case of bank account and fund management for analysis.

[SQL]View plaincopyprint?

1. // Create a database
2. Create table account (
3. Idint primary key not null,
4. Namevarchar (40 ),
5. Moneydouble
6. );
8. // Two people open an account and save money
9. Insert into account values (1, 'a, 1000 );
10. Insert into account values (2, 'B', 1000 );

Ii. ACID

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.

Iii. atomicity

Atomicity means that a transaction is an inseparable unit of work. Operations in a transaction either occur or do not occur.

1. Case studies

A Transfers 100 yuan to B

 

[SQL]View plaincopyprint?

1. Begin transaction
2. Update account set money = money-100 where name = 'a ';
3. Update account set money = money + 100 where name = 'B ';
4. If Error then
5. Rollback
6. Else
7. Commit

 

2. Analysis

The deduction and addition of two statements in the transaction are either executed or not executed. Otherwise, if you only execute the deduction statement, you will submit the statement. If A sudden power failure occurs, account A has already made the deduction, but account B has not received the fee, causing A dispute in life.

 

3. Solution

In the database management system (DBMS), the next SQL statement is a separate transaction by default, and the transaction is automatically committed. Only when start transaction is explicitly used to start a transaction can a code block be executed in the transaction. Ensuring the atomicity of transactions is the responsibility of the database management system. Therefore, many data sources adopt the log mechanism. For example, SQL Server uses a pre-write transaction log to write the data on the transaction log before submitting the data to the actual data page.

Iv. Consistency

Consistency means that the integrity constraints of the database are not damaged before and after the transaction starts. This means that database transactions cannot undermine the integrity of relational data and the consistency of business logic.

1. Case studies

For bank transfers, no matter whether the transaction succeeds or fails, you should ensure that the total deposits for aaa and bbb in the ACCOUNT table after the transaction ends is 2000 yuan.

 

2. Solution

To ensure transaction consistency, we can start from the following two aspects:

2.1 database mechanism level

Database-level consistency is that data meets 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. For example, you can use CHECK to restrict the sum of the two accounts to 2000 to achieve consistency.

2.2 Business level

At the business level, consistency is to maintain business consistency. This business consistency needs to be ensured by developers. Of course, the consistency of many businesses can also be ensured by transferring to the database mechanism layer.

5. Isolation

When multiple transactions are accessed concurrently, transactions are isolated. One transaction should not affect the running effect of other transactions.

This means that in a concurrent environment, when different transactions manipulate the same data at the same time, each transaction has its own complete data space. Modifications made by a concurrent firm must be isolated from those made by any other concurrent firm. When a transaction views data updates, the status of the data is either the status before another transaction modifies it, or the status after another transaction modifies it, transactions do not view data in the intermediate state.

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

In enterprise development, the most complex issue of transactions is caused by transaction isolation. When multiple transactions are concurrent, 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, which seriously affects performance. To understand the isolation protection of SQL Server, you must first understand how concurrent transactions interfere.

1. Interaction between transactions

The interaction between transactions can be divided into several types: dirty reads, non-repeated reads, Phantom reads, and loss of updates.

 

1.1 dirty reads

Dirty reading means that a transaction reads the uncommitted data of another transaction, and the data may be rolled back. In the following case, if transaction 1 is rolled back, b's account is bound to suffer losses.

 

1.2 non-repeated 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. In the following case, transaction 1 is bound to become confused and unknown.

 

1.3 phantom read (Virtual 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.

 

1.4 update loss

Two transactions read the same record at the same time. A modifies the record first and B modifies the record (B does not know that A has modified the record ), after B submits data, B's Modification result overwrites the Modification result of.

 

2. Understanding the isolation level in SQL SERVER

Transaction isolation level is a basic concept in a database. Why is there a transaction isolation level? What transaction isolation levels are implemented on SQL Server? The premise of transaction isolation level is a multi-user, multi-process, multi-thread concurrent system. To ensure data consistency and integrity, we introduce the concept of transaction isolation level, this problem does not exist for a single-user or single-thread application.

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. Therefore, this option is available for users to set based on their specific needs. However, the default isolation level Read Commited meets the actual needs of the majority.

 

Isolation level	Dirty read	Update loss	Non-repeated read	Phantom read	Concurrency Model	Update Conflict Detection
Uncommitted Read: Read Uncommited	Yes	Yes	Yes	Yes	Pessimistic	No
Committed Read: Read commited	No	Yes	Yes	Yes	Pessimistic	No
Repeatable Read	No	No	No	Yes	Pessimistic	No
Serializable read: Serializable	No	No	No	No	Pessimistic	No

The impact of SQL Server isolation between transactions is achieved through locks, blocking to prevent the above impact. Different isolation levels are implemented by adding different locks, resulting in blocking. Therefore, performance is the price. The higher the security level, the lower the processing efficiency. The lower the security level, high efficiency.

 

Usage:SET TRANSACTIONISOLATION LEVEL REPEATABLE READ

 

Uncommitted read:No lock is checked or used during data reading. Therefore, the uncommitted data may be read at this isolation level.

Committed read:Read Only committed data and wait for other transactions to release the exclusive lock. The shared lock of read data is released immediately after the read operation is completed. Committed read is the default isolation level of SQL Server.

Repeatable read:Reads data as if it had already been committed, but will keep the shared lock until the transaction ends.

Serializable read:The operation method is similar to repeated read. However, it not only locks the affected data, but also locks the range. This prevents the scope involved in the new data insertion query.

6. 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. SQLSERVER first checks the log sequence number, and persists some of the changes that have been made to the database, thus ensuring the durability.

VII. Summary

The ACID feature of transactions is implemented by relational database management systems (RDBMS. The database management system uses logs to ensure the atomicity, consistency, and durability of transactions. The log records the updates made by the transaction to the database. If an error occurs during the execution of a transaction, you can cancel the updates made to the database by the transaction according to the log, returns the database to the initial state before the transaction is executed.

The database management system uses a locking mechanism to isolate transactions. When multiple transactions update the same data in the database at the same time, only the transaction holding the lock can update the data. Other transactions must wait until the lock is released for the previous transaction, other transactions have the opportunity to update the data.