What is a transactional transaction is a rigorous set of operations in an application, and all operations must be completed successfully, otherwise all changes made in each operation will be undone. That is, transactions are atomic, and a series of operations in a transaction are either successful or not. The end of a transaction has two types, and when all the steps in the transaction are executed successfully, the transaction commits. If one of the steps fails, a rollback operation occurs, and the undo action is undone until the start of the transaction.
The acid transaction of a transaction has four characteristics: atomicity (atomicity), consistency (consistency), isolation (isolation), and persistence (durability). These four properties are referred to as ACID properties. 1. Atomic nature. A transaction is a logical unit of work for a database, and each operation contained in a transaction is either done or not 2 consistent. The result of the transaction execution must be to change the database from one consistent state to another. Therefore, when the database contains only the results of successful transaction commits, the database is said to be in a consistent state. If a database system fails in operation and some transactions have not yet been completed, some of the modifications made to the database have been written to the physical database, and the database is in an incorrect state, or an inconsistent state. 3, isolation. Execution of one transaction cannot interfere with other transactions. That is, the operations within one transaction and the data used are isolated from other concurrent transactions, and the transactions performed concurrently cannot interfere with each other. 4, continuous. Also known as permanence, when a transaction is committed, its changes to the data in the database should be permanent. The next operation or failure should not have any effect on its execution results.
MySQL's four isolation levels the SQL standard defines 4 classes of isolation levels, including specific rules that define which changes within and outside a transaction are visible and which are not. Low-level isolation levels generally support higher concurrency processing and have lower system overhead. READ UNCOMMITTED (Read UNCOMMITTED content) at that isolation level, all transactions can see the execution results of other uncommitted transactions. This isolation level is rarely used in real-world applications because it has no better performance than other levels. Reading uncommitted data is also known as Dirty reading (Dirty read). Read Committed (reads submissions) This is the default isolation level for most database systems (but not MySQL default). It satisfies the simple definition of isolation: A transaction can only see changes that have been submitted to the firm. This isolation level also supports so-called non-repeatable reads (nonrepeatable read), because other instances of the same transaction may have new commits during the instance processing, so the same select may return different results. Repeatable read (can be reread) this is the default transaction isolation level for MySQL, which ensures that multiple instances of the same transaction will see the same rows of data while concurrently reading the data. In theory, however, this can lead to another tricky problem: Phantom Reading (Phantom read). To put it simply, Phantom reads when a user reads a range of data rows, another transaction inserts a new row within that range, and when the user reads the data row of that range, a new phantom row is found. The InnoDB and Falcon storage engines address this issue through a multi-version concurrency control (mvcc,multiversion Concurrency control) mechanism. Serializable (Serializable) This is the highest isolation level, which solves the Phantom reading problem by forcing transactions to sort, making it impossible to collide with each other. In short, it is a shared lock on every data row read. At this level, a large number of timeouts and lock competitions can result.
These four isolation levels are implemented with different lock types, which can be problematic if the same data is being read. For example: Dirty reads (drity read):
A transaction has updated one copy of the data and the other transaction reads the same data at this time, and for some reason the previous rollback operation, the data read by the latter will be incorrect. Non-repeatable read (non-repeatable Read):
The data is inconsistent in the two queries of a transaction, which may be the original data that was inserted in the middle of the two query process for a transaction update. Phantom Read (Phantom Read):
In the two queries of a transaction, the number of data pens is inconsistent, for example, one transaction queried several columns (row) data, while another transaction inserted a new column of data at this time, the previous transaction in the next query, you will find a few columns of data that it did not previously.
In MySQL, these four isolation levels are implemented, each of which can cause problems as follows:
Test the MySQL isolation level below, we will take advantage of the MySQL client program, we test each of these isolation levels. The test database is demo, table is test, table structure: Two command line clients are a, B, changing the isolation level of a, modifying the data at the B end. (i), set the isolation level of a to read uncommitted (unread) A: Start the transaction, the data is the initial state B: Start the transaction, update the data, but do not commit a: Read the data again, the data has been modified, this is called "dirty read" B: ROLLBACK TRANSACTION A: Read the data again , the data is turned back to its original state. After the experiment above, transaction B updates a record, but does not commit, at which point a can query for uncommitted records. Cause dirty reading phenomenon. Non-committed reads are the lowest isolation level. (ii), set the transaction isolation level of client A to read committed (read-committed) A: Start the transaction, the data is the initial State B: Start the transaction, update the data, but do not commit A: Read the data again, the data is not modified B: Commit TRANSACTION A: Read the data again, find that the data has changed , stating that the modification B commits was read by a in the transaction, which is called "non-repeatable reading" After the above experiment can be concluded that the read-committed isolation level resolves the problem of dirty reads, but there is a non-repeatable read problem, that is, transaction a in two query data inconsistency, Because transaction b updates a single piece of data between the two queries. Read Committed only allows read-committed records, but does not require repeatable reads. (iii), set the isolation level of a to repeatable read (REPEATABLE Read) A: Start the transaction, the data is the initial State B: Start the transaction, update the data, but do not commit a: Read the data again, the discovery data is not modified B: Commit TRANSACTION A: Read the data again, the data is still not changed, This can be repeated read the B: Insert a new data, and submit a: Read the data again, found that the data is still unchanged, although it can be repeated reading, but found not the latest data, this is called "Magic read" A: Commit this transaction, read the data again, Found read normal by the above experiment, it can be concluded that the REPEATABLE read isolation level allows only read committed records, and the other transaction updates the record during a transaction that reads one record two times. However, the transaction does not require serialization with other transactions. For example, when a transaction can find a record that is updated by a committed transaction, it may produce a phantom read problem (note that it is possible because the database differs from the implementation of the isolation level). Like the above experiment, there is no problem of data phantom reading. (iv), set the isolation level of a to serializable (Serializable) A: Initiates a transaction, at which point the data is in the initial State B: Discovery B enters the wait state at this point because A's transaction has not yet been committed and can only wait (at this time, b may occur wait timeout) A: Commit TRANSACTION B: Discover that the insert succeeds serializable the full lock field, and if a transaction queries the same data it must wait until the previous transaction is complete and unlocked. Is the complete isolation level, which locks the corresponding data table and therefore has an efficiency problem.
MySQL four isolation levels
