Understanding the acid and isolation levels of transactions

A

Transaction (Transaction) is a program execution unit (units) that accesses and potentially updates various data items in a database, and a transaction is a database concept. But I understand that the database is also a software, just follow the database SQL standards, understand the transaction, first look at the logical structure of the database software, this is the logical structure of MySQL:

can be seen MySQL support multiple client access, Corresponding to MySQL inside is multi-threaded (usually a client corresponds to a processing thread within MySQL), the underlying storage engine is responsible for reading and writing data to persistent media (hard disk, storage array), which is clearly shared among internal threads, and requires attention to thread-safety issues, lifting chestnuts, There is a data table car, which has 2 fields, respectively, cars_beijing and Cars_shanghai, indicating the number of cars in Beijing and the number of cars in Shanghai, now one thread to do is to complete the move of a car from Beijing to Shanghai, then, it is obvious that the Cars_ beijing–,cars_shanghai++, represented by 2 SQL statements:
Update car set cars_beijing=cars_beijing-1 where id = 1;
Update car set cars_shanghai=cars_shanghai-1 where id = 1;

Now use this example to understand acid,
1. atomicity (atomicity), which means that this transaction either does not execute, or executes all, is the above 2 statements, not allowed to execute only one without executing the second day statement.
2. Consistency (consistency), which means that the database is transferred from one consistency state to another, how do you understand that atomicity means that it does not allow the execution of only one and no second article, So consistency means that either the first article succeeds (the so-called success is the impact on the database persisted data), or fails on the first article two (none of which has an impact on the database persisted data), does not allow a single success, a failed case.
3. Isolation (isolation) understands isolation by isolating the operation of another thread (transaction), such as thread a executing the transaction cars_beijing–,cars_shanghai++, while thread B is querying cars_beijing and Cars_ Shanghai value, isolation to ensure that thread B can only query to the transaction is completely not executed or completely successful execution of the value, do not allow thread B query to only execute cars_beijing– without executing cars_shanghai++ value
4. Persistence (durability), the better understanding is that once a transaction is committed, the modified data is persisted, even if the power is not lost.

Isolation is an intermediate value that prevents other threads from reading the transaction, but in practice this is divided into situations because there are many different intermediate values that correspond to the 4 isolation levels in the SQL standard.
Before you understand the isolation level, two issues are clear.
The first is to achieve the acid of the transaction, in fact, and multithreaded programming in the common resources of the principle of thread security, how to ensure that this acid, of course, is the public resources of each thread lock, the thread to access public resources, you need to obtain the public resources of the lock, The public resource here is the data inside the database, we know that the data in relational database is divided by table-row, here I can choose whether to lock or lock the table, or simply lock the whole database. This is the level of the lock.
The second is that the operation of the public data by multiple threads can actually be divided into many different situations, such as thread A when executing cars_beijing–,cars_shanghai++ this transaction, transaction B may be reading the cars_beijing value of the corresponding field. You can also read the cars_beijing value of the corresponding field more than once, and it is possible to count the number of records in the car table or to add records, and so on.
Knowing the combination of locks and other threads on the common data table, we can then understand the 4 isolation levels in the SQL standard, which is easy.
1. Uncommitted read (read UNCOMMITTED), that is, without isolation control, can read "dirty data", such as the above cars_beijing–,cars_shanghai++, this isolation level allows other threads to read only the cars_beijing– Instead of doing cars_shanghai++ values. Obviously this isolation level doesn't make much sense, and no one in reality will use it unless the application is read only and there is no writing.
2. Submit read (Read Committed), submit read is not allowed to read transactions did not submit data, simply said, is the above cars_beijing–,cars_shanghai++, not allowed to read only to do the cars_beijing–, Without making a cars_shanghai++ record. This isolation level is the default isolation level for most databases (except MySQL).
3. Repeatable READ (Repeatable read), what is not repeatable read, that is, transaction A to do cars_beijing–,cars_shanghai++ before, transaction B started, read the first transaction A to modify the value, this time transaction a modified the record, However, transaction B reads the same record value after transaction A has been modified, which obviously causes the same read operation of transaction B to read different values, which is not repeatable reading. Repeatable Read is to prohibit this situation, such as the need to modify the data and exclusive locks, transaction B needs to read this record, then the entire transaction B is not completed before, allow transaction A to start. The isolation level for repeatable reads is the MySQL default isolation level.
4. Serializable (serialzable), that is, multiple threads (transactions) completely without concurrency, serial execution, of course, there will be no isolation problems, the obvious efficiency is also the lowest, generally do not use

There is also the question of what the problem can be serialized can be solved and repeatable read can not be solved. That's the question of Phantom reading, which is called Phantom Reading, for example, the first transaction modifies the data in a table, such as "All rows of data" in the table. At the same time, the second transaction modifies the data in this table by inserting "one row of new data" into the table. So, it's going to happen later. The user of the first transaction discovers that there are no modified data rows in the table, as if there were hallucinations. Repeatable read because only the records that need to be modified are locked, if the task of a transaction is to add a record, the transaction is not blocked by the modified transaction, and all repeatable isolation levels are not able to resolve the Phantom read problem.