Related Concepts of database transactions

The four characteristics of database transactions ( acid) are:

(1) atomicity (atomicity)

The atomicity of a transaction is that the program contained in the transaction acts as a logical unit of work for the database, which either executes all of the data modification operations or does not execute at all. This characteristic is called atomicity.

For example, a bank withdrawal transaction is divided into 2 steps (1) passbook reduction (2) to withdraw cash. It is impossible to reduce money in a passbook without withdrawing cash. 2 steps must be completed or not completed at the same time.

(2) Consistency (consistency)

Transactional consistency means that the database must be in a consistent state before and after a transaction is executed. This attribute is called transactional consistency. If the state of the database satisfies all the integrity constraints, the database is said to be consistent.

For example, integrity constraint a+b=10, a transaction changes A, then B should change as well.

(3) separation (also known as independence isolation)

Separation refers to the concurrency of transactions that are isolated from each other. That is, the operations within a transaction and the data being manipulated must be blocked from being seen by other transactions attempting to modify it. The execution of multiple concurrent transactions that manipulate the same shared object can cause an exception if there is no control over the concurrent cross-executing transactions.

(4) persistence (durability)

Persistence means that updates to committed transactions cannot be lost when a system or media failure occurs. That is, once a transaction commits, the DBMS guarantees that its changes to the data in the database should be permanent, that is, the update to the committed transaction can be restored. Persistence is ensured through database backup and recovery.

2. What is the role of database transactions?

The role of things for the database is a series of operations on the data, either all succeed, or all fail, to prevent the presence of intermediate state, to ensure that the data in the database is always in the correct and harmonious state.

3. What is the isolation level of database transactions?

There are 4 isolation levels for database transactions, from low to high, READ UNCOMMITTED,Read Committed,Repeatable read, andSerializable, which can be resolved individually by each of the four levels Some problems such as dirty reading, non-repetition reading and phantom reading

< Span class= "Importance" >read UNCOMMITTED READ UNCOMMITTED

The company paid, the leader of the 5000 yuan to the Singo account, but the transaction did not submit, and Singo just to check the account, found that the salary has been to the account, is 5000 yuan whole, very happy. Unfortunately, the leadership found that the amount of wages issued to Singo is not correct, is 2000 yuan, and then quickly rolled back to business, modify the amount, the transaction will be submitted, and finally singo the actual salary of only 2000 yuan, Singo empty joy a game.

The above situation, that is what we call dirty Read, two concurrent transactions, "transaction A: lead to Singo payroll", "Transaction B:singo query Payroll account", transaction B read the transaction A has not yet committed data.

When the isolation level is set to READ UNCOMMITTED, dirty reads can occur and how to avoid dirty reads, see the next isolation Level

Read Committed reading Commit

Singo take the payroll card to spend, the system read to Cary really have 2000 yuan, and at this time her wife also just in the online transfer, the Singo Pay card of 2000 yuan to another account, and before Singo submitted the business, when Singo deduction, System Check to Singo's payroll card has no money, deduction failure, Singo very puzzled, obviously card money, why ...

The above situation, that is what we call non-repeatable read, two concurrent transactions, "transaction A:singo consumption", "Transaction B:singo wife online transfer", transaction A in advance read the data, transaction B immediately updated the data, and committed the transaction, and transaction a read the data again, The data has changed.

When the isolation level is set to Read Committed, dirty reads are avoided, but may cause non-repeatable reads.

The default level for most databases is read committed, such as SQL Server, Oracle. How to solve the problem of non-repeatable reading, see next isolation Level

REPEATABLE READ repeat

you can avoid non-repeatable reads when the isolation level is set to repeatable read. When Singo took the payroll card to spend, once the system began to read the Payroll card information (that is, the start of the transaction), Singo's wife could not change the record, that is Singo wife can not be transferred at this time.

Although repeatable read avoids non-repeatable reads, it is possible to have Phantom reads.

Singo's wife works in the banking department, and she often views Singo's credit card consumption records through the internal banking system. One day, she was inquiring into the total consumption amount of credit card in Singo month (select SUM (amount) from transaction where month = this month) was $80, and Singo at this time was good to eat outside the sea plug at the cashier to pay, spend 1000 yuan , which adds a $1000 consumption record (insert transaction ... ), and submitted a transaction, then Singo's wife will singo the current month credit card consumption details printed to A4 paper, but found that the total consumption of 1080 yuan, Singo wife is very surprised, thought there was an illusion, the illusion of such a generation.

Note: The default isolation level for MySQL is repeatable read.

Serialization of Serializable

Serializable is the highest transaction isolation level, with the highest cost and low performance, which is rarely used at this level, where the transaction sequence executes not only to avoid dirty reads, non-repeatable reads, but also to avoid Phantom reads.

Related Concepts of database transactions