Database High Score Note 01: Transaction acid

ARCCOSXY Reprint Please specify the Source: http://www.cnblogs.com/arccosxy/

A transaction is a set of atomic SQL queries, or a separate unit of work. If the database engine is able to successfully apply all the statements of a reorganization query to the database, name executes the set of queries. If any of these statements cannot be executed because of a crash or other reason, all statements are not executed. That is, the statements within a transaction are either executed successfully or all failed.

Banking applications are a classic example of the need to explain transactions. Suppose a bank's database has two tables: a check (checking) table and a storage (savings) table. Now to transfer $200 from the user Jane's chequing account to her storage account, it will take at least three steps:

1. Check the balance of the checking account above $200;
2. Subtract $200 from the balance of the checking account;
3. Add $200 to your storage account balance.

The three steps above must be packaged in one transaction, and any one step fails, you must roll back all the steps. The transaction SQL is as follows:

1. START TRANSACTION;
2. SELECT balance from checking WHERE custom_id = 10233276;
3. UPDATE checking SET balance = balance-200.00 WHERE custom_id = 10233276;
4. UPDATE savings SET balance = balance + 200.00 WHERE custom_id = 10233276;
5. COMMIT;

The simple concept of affairs is not the whole story. acid represents atomicity (acomicity), consistency (consistency), isolation (isolation), and persistence (durability). A well-functioning transaction processing system must have these standard features.

• atomicity : A thing must be considered as an inseparable minimal unit of work, all operations in the whole transaction either commit successfully or all fail back, and for a thing it is impossible to perform only one part of the operation, which is the atomicity of the transaction.
• consistency : The database is always transformed from one consistent state to another. In the previous example, consistency ensured that, even when the system crashed between the third and fourth statements, there was no loss of $200 in the chequing account because the transaction was eventually not committed, so the changes made in the transaction were not saved to the database.
• isolation : In general, changes to a thing are not visible to other transactions until the final commit. In the previous example, when the third statement was executed and the fourth statement did not begin, another account summary program started executing, and the balance of the checking account that it saw was not subtracted by $200. The transaction isolation level is divided into: uncommitted read, read-committed, Repeatable read, serializable.
• Persistence : Once a transaction commits, its modifications are persisted to the database. When the system crashes, the modified data is not lost. Persistence is a somewhat vague concept, because there are actually many different levels of persistence. Some persistence strategies can provide very strong security, while others may not. And there is no way to have a guaranteed 100% persistence strategy.

The acid nature of the transaction ensures that the bank will not lose your money. In the application logic, it is very difficult to achieve this, and it can even be said to be impossible to complete the task. An acid-implementing database system that requires more additional work than a database that does not implement acid, typically requires more CPU processing power, more memory, and more disk space.

Database High Score Note 01: Transaction acid