Taobao massive database of three: the acid__ database of transactions

Turn from: http://blog.sina.com.cn/s/blog_3fc85e260100msz3.html

Each transaction shifts the database from a consistent permanent state to a new consistent permanent state, which can be said to be the acid of the transaction (Thetransactional properties of atomicity, consistency, isolation Anddurability) attribute is the soul of a database transaction:

· of Atomic

The atomicity of a transaction is first reflected in the modification of the transaction to the data, either all or none of them, for example, a transfer from a bank account A to account B, and the result must be a deduction from the account of a and an increase in the account of B, not just one of the accounts. However, the atomicity of the transaction does not always guarantee that the modification must be completed or must not be done, for example, on an ATM machine, when the transfer is interrupted or the database host is abnormal, The transfer may or may not have been done: if the database host received the transfer instruction and the subsequent execution was complete before the communication interruption occurred, the transfer was completed successfully; If the transfer instruction did not reach the database host or, if it arrives, the subsequent execution of the exception ( For example, write Commitlog failure or insufficient account balance, then transfer is not carried out. To determine whether a transfer is successful, you need to check the account transaction history or balance after the recovery of the communication or the database host is restored. The atomicity of the transaction is also reflected in the transaction's reading of the data, for example, the result of a transaction being read multiple times for the same data item must be the same.

· Consistency

Transactions need to maintain the correctness of database data, completeness and consistency, sometimes this consistency is guaranteed by the internal rules of the database, such as the type of the data must be correct, the data value must be within the specified range, and so on; other times this consistency is guaranteed by the application, for example, in general, the bank account balance cannot be negative, Credit card consumption cannot exceed the credit limit of the card.

· Isolation of

Many times the database executes multiple transactions concurrently, and each transaction may need to be modified and queried for multiple table items, while more query requests may be in progress. The database needs to ensure that each transaction is not visible to other transactions until all of its modifications are complete, in other words, that no other transaction can see the middle state of the transaction, for example, a transfer from bank account A to account B, and no other transactions (such as account inquiries) Saw that a account has been deducted from a but the B account has not yet increased the status of a.

· Persistence of

When a transaction completes, its effect on the database is permanent, even if there are various exceptions to the system.

For performance reasons, many databases allow users to choose to sacrifice the isolation attribute in exchange for concurrency, thereby improving performance. SQL defines 4 isolation levels: READ UNCOMMITTED (RU): READ UNCOMMITTED data, that is, other transactions have been modified but not committed data, which is the lowest isolation level; Read committed (RC): In a transaction, the same item, The previous read may not be the same as the subsequent read results. For example, the modification of another transaction at the first read has not yet been committed, and the second read has been committed; Repeatable Read (RR): Repeatable read, in a transaction, for the same item, the previous read is the same as the subsequent read result; Serializable (S): Serializable, that is, the transaction of a database is serializable to execute, just as a transaction executes without other transactions executing simultaneously, which is the highest isolation level;

Reduced isolation levels can result in read-dirty data or transaction execution exceptions, such as: Lost update (LU): Two transactions modify one data item at a time, but the second transaction fails to exit halfway, and two modifications to the data item may be lost; Dirty reads (DR) : A transaction reads a data item, but another transaction updates the data item without committing, so all operations may have to be rolled back; Non-repeatable reads (NRR): A transaction may have different results for multiple reads of the same data item; Second lost Updates problem (SLU): A special case that cannot be read repeatedly: Two concurrent transactions read and modify the same data item at the same time, subsequent modifications may invalidate the previous modification; Phantom reads (PR): Also known as Phantom Reading, for example, during the execution of a transaction, Since there is another transaction inserted into the data during the previous query and the subsequent query, the subsequent query results in data that does not appear in the previous query results.

The relationship between isolation levels and read-write exceptions (inconsistencies) is as follows:

	LU	DR	Nrr	Slu	PR
RU	Y	Y	Y	Y	Y
RC	N	N	Y	Y	Y
Rr	N	N	N	N	Y
S	N	N	N	N	N

It is easy to find that at the highest isolation level serializable data does not appear to be inconsistent with reading and writing.

Different databases support varying levels of isolation, for example, Oracle supports only the readcommitted and serializable two levels, and MySQL supports all four levels.

Oceanbase transaction implementation is different from classic relational database, its read transaction is distributed concurrently, and write transaction is now centralized serial execution, namely serializable, and any write transaction is not visible to other read and write transactions before commit. Therefore the oceanbase is strongly consistent.