Database Fragmentation Learning: The third of Taobao's massive database: The acid of a transaction

First, the Baidu Encyclopedia to the interpretation of database transactions: Database transactions, refers to the Transaction as a single logical unit of work performed by a series of operations, either fully executed, or completely do not execute. Transaction processing ensures that data-oriented resources are not permanently updated unless all operations within the transactional unit are completed successfully. By combining a set of related actions into a single unit that either succeeds or all fails, you can simplify error recovery and make your application more reliable.

>>>>>>>>>>>>>>>>>>>>>>>>>>> >>>>>>> Gorgeous split Line >>>>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>>>>>>>>>>>> >>>>>>>>>>>>lol>>>>>>>>>>>>>> >>>>>>>>>>>>

From: http://blog.sina.com.cn/s/blog_3fc85e260100msz3.html

Each transaction allows the database to be atomically transferred from a consistent permanent state to a new consistent permanent state, which can be said, the acid of the transaction (the transactional properties of atomicity, consistency, isolation and durability) property is the soul of a database transaction:

· Atomic Nature

The atomicity of a transaction is first reflected in the modification of the data by the transaction, that is, either all executed, or none, for example, a transfer from bank account A to account B, the result must be deducted from the account of a and an increase of money A on the account B, not just one of the account changes. However, the atomicity of the transaction does not always guarantee that the modification must be completed or must not be performed, such as the transfer on the ATM machine, the communication interruption after the transfer instruction is committed, or the database host exception. The transfer may or may not have occurred: if the database host fully received the transfer instruction before the communication interruption and the subsequent execution is normal, then the transfer is completed successfully, if the transfer instruction does not reach the database host, or if it arrives but then executes the exception (such as writing a commit Log fails or the account balance is insufficient), the transfer is not performed. To determine whether the transfer was successful, you need to wait for a communications recovery or a database host to query the account transaction history or balance. The atomicity of a transaction is also reflected in the transaction's reading of the data, for example, the result of multiple reads of a transaction on the same data item must be the same.

· Consistency

Transactions need to maintain the correctness, completeness and consistency of database data, sometimes this consistency is ensured by the internal rules of the database, such as the type of data must be correct, the data value must be within the specified scope, etc., 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.

(Add your own explanation from Baidu Encyclopedia: When a transaction is complete, all data must be kept in a consistent state.) In a related database, all rules must be applied to transaction modifications to maintain the integrity of all data. At the end of the transaction, all internal data structures, such as B-tree indexes or doubly linked lists, must be correct. Some of the responsibility for maintaining consistency is borne by application developers who must ensure that all known integrity constraints are enforced by the application. For example, when developing an application for transfer, avoid arbitrarily moving the decimal point during the transfer process. )

· Isolation of

Many times the database executes multiple transactions concurrently, and each transaction may need to modify and query multiple table items, while more query requests may be in progress. The database needs to ensure that every transaction is not visible to other transactions until all its modifications have been completed, in other words, it is not possible for other transactions to see the middle state of the transaction, for example, a transfer from bank account A to account B, and no other transactions (such as an account query) See that a account has been deducted from a but B account has not yet increased the status of the money A.

· Durability

After the transaction is complete, its effect on the database is permanent, even if there are various exceptions to the system.

(from Baidu Encyclopedia: Database System One obvious feature is that multiple users share database resources, especially multiple users can access the same data at the same time. Concurrency control can be used, and transactions are the basic unit of concurrency control, and ensuring that the performance of the transaction acid is an important task of transaction processing, while concurrent operations can disrupt its acid characteristics.

The responsibility of DBMS concurrency control mechanism is to dispatch the concurrency operation correctly, ensure the isolation of the transaction more generally, and ensure the consistency of the database.

If there is no lock and multiple users access a database at the same time, problems may occur when their transactions use the same data at the same time. Data inconsistencies due to concurrency include loss of data modifications, reading of "dirty" data (dirty reads), non-repeatable reads, and phantom data generation. ）

For performance reasons, many databases allow the consumer to choose to sacrifice isolation properties for concurrency in Exchange for performance gains. SQL defines 4 isolation levels: READ UNCOMMITTED: Reads uncommitted data, that is, data that has been modified but not committed by other transactions, which is the lowest isolation level; Read committed (RC): In a transaction, for the same item, The previous read may not be the same as the subsequent read results, such as the first read when another transaction is not committed, the second read has been committed; Repeatable Read (RR): Repeatable read, in a transaction, the same item, the preceding read the same as the subsequent read results; Serializable (S): Serializable, that is, the transaction of the database is serializable, just as there is no other transaction executing at the same time as a transaction executes, which is the highest isolation level;

A decrease in isolation level can result in read-dirty data or transaction execution exceptions, such as: Lost update (LU): Two transactions modifying one data item at the same time, but two modifications to the data item may be lost if the last transaction fails to exit; Dirty Reads (DR) : One transaction reads a data item, but another transaction updates this data item without committing it, so all operations may have to be rolled back; Non-repeatable Reads (NRR): Multiple reads of a transaction on the same data item may result in different results; 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, Because the previous query and the subsequent query have another transaction inserting the data, the result of the query results in the data that did 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 see that, at the highest isolation level serializable, the data does not appear inconsistent with read and write.

Different databases support varying levels of isolation, such as Oracle only supports read committed and serializable two levels, andMySQL supports all four levels.

The transaction implementation of Oceanbase is different from the classical relational database, and its read transaction is basically distributed and concurrently executed, and the write transaction is executed centrally serially, namely serializable, And any write transaction is not visible to other read and write transactions before commit, so the oceanbase is strongly consistent.