1. What is business
A transaction is a series of rigorous operations in an application. All operations must be completed successfully, otherwise all changes made in each operation will be undone. It can be understood as: the program execution unit that updates various data items in the database. For example: grasshoppers on the same rope should live and die together. Transactions in mybatis are committed and rolled back through the commit and rollback methods of sqlsession.
2. Four characteristics of transaction acid
All operations in a transaction are indivisible atomic units. All operations in a transaction either succeed or fail.
After the transaction is executed, the database state is consistent with other business rules. For example, the sum of the balances of the two accounts involved in the transfer should remain unchanged regardless of whether the transaction is successful or not.
Isolation means that in concurrent operations, different transactions should be isolated so that each concurrent transaction will not interfere with each other.
Once the transaction is committed successfully, all data operations in the transaction must be persisted to the database. Even if the database crashes after the transaction is committed, it must be able to recover the data through some mechanism when the database is restarted
3. There are three problems that arise when isolation is not considered
3.1 dirty reading
Dirty read refers to reading data from another uncommitted transaction during one transaction.
3.2 non repeatable reading
When a transaction reads the data of the same row twice, it gets the results of different states. In the middle, another transaction updates the data. The two results are different and cannot be trusted. Generally speaking, transaction T1 reads a certain data, and transaction T2 immediately modifies the data and submits the transaction to the database. Transaction T1 reads the data again and gets different results and sends non repeatable reads.
3.3 unreal reading
A transaction performs two queries, and the second result set contains data that is not included in the first query or some rows have been deleted, resulting in inconsistent results between the two queries. Only another transaction inserts or deletes data between the two queries. Generally speaking, for example, transaction T1 modifies a data item of all rows in a table from “1” to “2”. At this time, transaction T2 inserts a row of data item into the table, and the value of this data item is still “1” and submitted to the database. If the user who operates transaction T1 looks at the data that has just been modified, he will find that there is another line that has not been modified. In fact, this line is added from transaction T2, which is like hallucination. This is called phantom reading.
4. Solution: four isolation levels
4.1 read uncommitted
Read uncommitted, as the name suggests, is that one transaction can read data from another uncommitted transaction. However, read uncommitted results in dirty read, which can be solved by read commit
4.2 read committed
Read commit, as the name implies, is that a transaction can read data only after another transaction has been committed. Read commit: if there is a transaction to update the data, the read transaction must wait for the update transaction to read the data, which can solve the dirty read problem. But in this case, two identical queries within the scope of a transaction return different data, which is non repeatable read. However, two queries submitted by reading will produce different query results, which will lead to the problem of non repeatable reading, which can be solved by repeated reading.
4.3 repeatable read
Repeat read means that the modification operation is no longer allowed when reading data (transaction is on). Repeated reading can solve the problem of non repeatable reading. It should be understood that non repeatable reading corresponds to the modification, that is, the update operation. But there may also be phantom reading problems. Because the phantom read problem corresponds to an insert operation, not an update operation. Serializable can solve the problem of magic reading
Serializable is the highest level of transaction isolation. At this level, transactions are serialized in order to avoid dirty read, non repeatable read and phantom read. However, this kind of transaction isolation level is inefficient, consumes database performance, and is generally not used.