relational database acid

relational database acid

I. Transactions

Definition: The so-called transaction, which is a sequence of operations that are either executed or not executed, is an inseparable unit of work.

Preparation: To illustrate the acid principle of transactions, we use bank accounts and money management cases (ATMs) for analysis.

2. Create a database
4. Create Table account (
6. Idint Primary key not null,
8. Namevarchar (40),
10. Moneydouble
12. );
16. There are two of people who open accounts and money.
18. Insert into account values (1,' A ', 1000);
20. Insert into account values (2,' B ', 1000);

Two. ACID

ACID, refers to the four characteristics that a transaction (transaction) should have in a reliable database management system (DBMS): atomicity (atomicity), consistency (consistency), isolation (isolation), Persistence (durability). This is a few of the features that a reliable database should have. Here are some of the features to be explained individually.

Three. atomicity

Atomicity means that a transaction is a non-divisible unit of work, and the operations in the transaction either occur or do not occur.

1. Case studies

A to B transfer 100 Yuan Money

[SQL]View PlainCopyprint?

1. Begin Transaction
2. Update Account set money= money-100where name=' A ';
3. Update Account set money= money +100where name=' B ';
4. If Error Then
5. rollback
6. Else
7. Commit

2. Analysis

The deductions and the two statements in the transaction are either executed or not executed. Otherwise, if only the execution of the deduction statement, on the submission, at this time if the sudden power outage, a account has been deducted, B account has not received a bonus, in life will cause disputes.

3. Workaround

In a database management system (DBMS), a single SQL is a separate transaction by default, and transactions are automatically committed. Only the explicit use of START transaction opens a transaction in order to place a block of code in the transaction execution. It is the responsibility of database management system to guarantee the atomicity of transaction, so many data sources adopt log mechanism. For example, SQL Server uses a pre-written transaction log, which is written on the transaction log before committing the data to the actual data page.

Four. Consistency

Consistency means that the integrity constraints of a database are not compromised until the start of the transaction and after the transaction has ended. This means that database transactions do not break the integrity of relational data and the consistency of business logic.

1. Case studies

For bank transfer transactions, whether the transaction succeeds or fails, it should be ensured that the total deposit of AAA and BBB in the Account table after the transaction has ended is 2000 yuan.

2. Workaround

Guarantee the consistency of the transaction, can start from the following two levels

2.1 Database mechanism level

Database-level consistency is that before and after a transaction executes, the data conforms to the constraints you set (UNIQUE constraints, foreign KEY constraints, check constraints, and so on) and trigger settings. This is guaranteed by SQL Server. For example, you can use a check to constrain the sum of two accounts equal to 2000来 to achieve consistency.

2.2 Business Level

For the business dimension, consistency is what keeps business consistent. This business consistency needs to be guaranteed by the developer. Of course, many aspects of business consistency can also be ensured by moving to a database mechanism.

Five. Isolation

When multiple transactions are concurrently accessed, the transactions are isolated, and one transaction should not affect the performance of other transactions.

This means that in a concurrent environment, each transaction has its own full data space when different transactions manipulate the same data at the same time. Modifications made by a concurrent transaction must be isolated from modifications made by any other concurrent transaction. When a transaction views the data update, the data is in the state it was in before the other transaction modifies it, or the state after the other transaction modifies it, and the transaction does not view the data in the middle state.

In Windows, if multiple processes are not allowed to modify the same file, Windows ensures the isolation of different processes in this way:

In enterprise development, the most complex issues of transactions are caused by transaction isolation. When multiple transactions are concurrent, SQL Server uses lock and block to ensure different levels of isolation between transactions. In general, complete isolation is unrealistic, and full isolation requires that the database execute only one transaction at a time, which can severely affect performance. To understand the security of isolation in SQL Server, first understand how concurrent transactions are interfering.

1. Interaction between transactions

The interactions between transactions are divided into several types: dirty read, non-repeatable read, Phantom Read, missing update

1.1 Dirty Reads

Dirty reads mean that one transaction reads uncommitted data from another transaction, and this data is likely to be rolled back; In this case, if transaction 1 is rolled back, the B account will have a loss.

1.2 Non-REPEATABLE READ

Non-repeatable reading means that in database access, two identical queries within a transaction range return different data. This is caused by the commit of the other transaction modifications in the system during the query. In the following case, transaction 1 is bound to get confused and not know what happened.

1.3 Phantom Read (virtual Read)

Phantom reading is a phenomenon that occurs when a transaction is not executed independently, such as when the first transaction modifies data in a table, which involves all rows of data in the table. At the same time, the second transaction modifies the data in the table by inserting a new row of data into the table. Then the user who will be working on the first transaction in the future finds that there are no modified rows of data in the table, as if the illusion had occurred.

1.4 Missing updates

Two transactions read the same record at the same time, a first modify the record, B also modifies the record (B is not aware of a modified), b after the submission of data, the result of B is covered by a modification results.

2. Understanding the isolation level in SQL Server

The transaction isolation level of the database (TRANSACTION isolation levels) is a very basic concept on a database. Why are there transaction isolation levels, and what transaction isolation levels are implemented on SQL Server? Transaction ISOLATION level is a multi-user, multi-process, multi-threaded concurrency system, in order to ensure data consistency and integrity, we introduced the concept of transaction isolation level, for a single user, single-threaded application does not exist this problem.

To avoid the effects of the above-mentioned transactions, SQL Server avoids varying degrees by setting different isolation levels. Because a high isolation level means more locks, sacrificing performance. So this option is open for the user to set according to the specific requirements. However, the default isolation level, read commited, meets most of the actual requirements.

Isolation level	Dirty Read	Missing updates	Non-REPEATABLE READ	Phantom reading	Concurrency model	Update conflict Detection
UNCOMMITTED read: Read uncommited	Is	Is	Is	Is	Pessimistic	Whether
Submitted read: Read commited	Whether	Is	Is	Is	Pessimistic	Whether
REPEATABLE READ: Repeatable READ	Whether	Whether	Whether	Is	Pessimistic	Whether
Serializable read: Serializable	Whether	Whether	Whether	Whether	Pessimistic	Whether

The effect of SQL Server isolated transactions is achieved through locks, blocking to prevent these effects. Different isolation levels are implemented by adding different locks that cause blocking, so it pays for performance, and the higher the security level, the lower the processing efficiency; the lower the security level, the higher the efficiency.

How to use: SET transactionisolation level repeatable READ

READ UNCOMMITTED: No locks are checked or used when reading data. Therefore, data that is not committed may be read in this isolation level.

Read Committed: read only committed data and wait for other transactions to release exclusive locks. Shared locks for read data are released as soon as the read operation is complete. Read Committed is the default isolation level for SQL Server.

repeatable reads: reads data as read-committed, but retains shared locks until the end of the transaction.

Serializable reads: works like repeatable reads. But it will not only lock the affected data, but also lock the range. This prevents the scope in which the new data is inserted into the query.

Six. Persistence

Persistence means that after the transaction is completed, the changes made to the database by the firm are persisted in the database and are not rolled back.

Even if there is an accident such as a power outage, the transaction is persisted in the database once it is committed.

SQL Server guarantees persistence through Write-ahead transaction log. Write-ahead transaction log means that changes to the database in a transaction are written to the transaction log first before being written to the database. The transaction log is automatic arranging (LSN) in order. When a database crashes or a server breakpoint is restarted, the restart SQL Server,sqlserver first checks the log sequence number, persisting the portion of the database that should have been changed but not done to the database, thereby guaranteeing durability.

Seven. Summary

The (ACID) nature of a transaction is implemented by a relational database management system (RDBMS, database System). The database management system uses logs to ensure the atomicity, consistency and persistence of transactions. The log records the updates that the transaction makes to the database, and if a transaction has an error during execution, it can undo the updates that the transaction has made to the database based on the log, returning the database to the initial state before the transaction was executed.

The database management system adopts the lock mechanism to realize the isolation of the transaction. When multiple transactions update the same data in the database at the same time, only transactions that hold the lock are allowed to update the data, and other transactions must wait until the previous transaction releases the lock, and other transactions have the opportunity to update the data.

Reprinted from: http://blog.csdn.net/shuaihj/article/details/14163713

relational database acid