"Turn" up posture, several levels of database isolation

Original: 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.

· 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.

For performance reasons, many databases allow the consumer to choose to sacrifice isolation properties for concurrency in exchange for performance gains. SQL defines 4 levels of isolation:

• READ UNCOMMITTED (RU): Reads uncommitted data, which is the lowest isolation level for data that has been modified but not committed by other transactions;
• Read Committed (RC): In a transaction, for the same item, the preceding read may not be the same as the subsequent read result, for example, the modification of another transaction is not committed on the first read, and the second read has been committed;
• Repeatable Read (RR): Repeatable read, in one transaction, for the same item, the preceding read is the same as the subsequent read result;
• 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 levels can result in read-dirty data or transaction execution exceptions, such as:

• Lost Update (LU): Two transactions modify one data item at the same time, but the last transaction fails to exit in the middle, the two modifications to the data item may be lost;
• Dirty Reads (DR): One transaction reads a data item, but another transaction updates this data item without committing it, so that 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, the subsequent modification may invalidate the previous modification;
• Phantom Reads (PR): Also known as Phantom Reading, for example, in the execution of a transaction, because of the previous query and the subsequent query in the period of another transaction to insert data, the results of the subsequent query appear in the previous query results do not appear in the data.

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, and MySQL 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 currently executed centrally serially, that is, serializable, and any write transaction is not visible to other read and write transactions before commit. So the oceanbase is strong and consistent.

"Turn" up posture, several levels of database isolation