MySQL transaction and isolation levels

What is a transaction?

a large processing unit that contains multiple operations, involves multiple CPU and IO operations, these operations complete at the same time or not at the same time, that is, the operation of this large processing unit is a whole, only the complete execution, before committing. A SQL statement that typically initiates a transaction, or a statement that specifically initiates a transaction in ODBC

Nature of the transaction (rules to be followed, ACID)

• Atomicity (atomicity): All operations in the whole transaction, either complete or incomplete, cannot be stuck in the middle, and all operations of the whole transaction are indivisible, so if an error occurs during the execution of the transaction, the to the state before the start of the transaction, as if the transaction had never been performed.

• consistency (consistency): Transaction execution is bound to cause database state change, consistent representation,

• Isolation (isolation): When transactions are executed in parallel, Span style= "Line-height:24px;text-indent:28px;background-color:rgb (255,255,255);" > Ensure that every transaction in the system considers that only the transaction is using the system, and that the transactions do not affect each other

• persistence (durability):

transaction Log :

The transaction is logged to the transaction log file, and then to the disk (for example, the delect operation, there are four statements, four statement executions are done in memory and logged to the log, when the entire operation is complete, and then the data file is modified from the log file) so that each transaction is written two times during execution. Over to the log, once to the real file (logging is the operation process, not the operation itself)

Concurrency control is required for transaction concurrency execution, and locking is one way and is an important mechanism

Lock classification

• Read Lock: Shared lock (read while allowing other transactions to read)

• Write Lock: Exclusive lock/row lock (write not allow other transactions to read and write)

lock granularity : Lock granularity is large to small, granularity is finer and concurrency can be achieved, but the implementation mechanism is more complex

(MySQL server supports table-level locks only, row locks need to be completed by the storage engine)

• Table lock

• Row lock

Isolation Level : from low to high, the higher the isolation level, the lower the concurrency capability

• READ UNCOMMITTED: Reads uncommitted: All transactions can see the results of other transactional operations, various chaotic

• Read COMMITTED: Reads commit: Transactions can see the actions of other transactions after they have been committed by another transaction (I read it once, I read it again, it was submitted by someone else, two reads differently)

• Repatable read: Can be reread: what the transaction first read, until the time of submission, even if other transactions in the process of the same content operation and commit, will not change the transaction read the thing (after the submission is found to be 10 rows now become 5 rows, may be modified and submitted by others when you have not submitted it) (MySQL default)

• SERIALIZABLE: Serializable, simply stated, the transaction cannot be executed concurrently

This article is from the "Call Me boxin" blog, so be sure to keep this source http://boxinknown.blog.51cto.com/10435935/1677013

MySQL transaction and isolation levels