Let's use a classic transfer scenario to illustrate what a transaction is
Now there is a scenario where there is an A account and a B account in the banking system. There are 1,000 yuan in both A and B. Account A wants to transfer 100 yuan to account B. This involves the following two steps:
- 100 yuan deducted from account A
- B account increased by 100 yuan
Ideally, both steps 1 and 2 above are successful. A 100 yuan will be deducted from the account A, which will eventually be 900 yuan, and the B account will be increased by 100 yuan, which will eventually be 1100 yuan. However, there are many failures in real scenarios. For example, step 1 succeeds and step 2 fails. At this time, 100 yuan is deducted from account A, and the final amount is 900 yuan, but account B does not increase by 100 yuan, and it is still 1,000 yuan. The data is inconsistent at this time
What is the purpose of the transaction
The transaction is to solve the above data consistency problem
When we need to perform a series of operations, in theory, each operation may succeed or fail, then the final result will have many possibilities, and the consistency of the data cannot be guaranteed.
In order to solve this data inconsistency problem, we encapsulate multiple operations, which is called a transaction. There are only two execution results of this transaction, the execution is successful or the execution fails, and there is no partial success or partial failure, so that the consistency of the data is guaranteed
Definition of transaction
A transaction is to encapsulate a series of operations (usually expressed as a series of SQL in a database) into an indivisible unit of work. The operations in a transaction are either all executed successfully or all executed fail, and there is no partial success or partial failure.
Four characteristics of business
The reason why transactions appear is to solve the problem of data consistency. For the above transfer scenario, A account transfers 100 yuan to B account, ensuring that A account deducts 100 yuan, and B account increases by 100 yuan. There will be no partial success or partial failure, that is, to ensure data consistency
A transaction is the smallest unit of work. All operations in the transaction either succeed in execution or fail in execution, and there is no partial success or partial failure.
The mysql database innodb storage engine guarantees the atomicity of transactions through undo log.
After the operation of a transaction is submitted successfully, the modification of the data is permanent. Even if the server fails, the data will not be lost after the failure is restored.
The mysql database innodb storage engine guarantees the durability of transactions through redo log.
The execution of a transaction cannot be interfered by other transactions, that is, the execution of the transaction is isolated, and each transaction executed concurrently cannot interfere with each other.
Transaction isolation level
During the execution of transaction A, the uncommitted modified data of transaction B was read.
Transaction B failed to commit later, and the transaction was rolled back. Dirty reads may occur at this time.
During the execution of transaction A, the data that has been submitted and modified by transaction B is read.
During the execution of transaction A, the first read is the modified data a1 of transaction B, and the second read is the modified data a2 of transaction C. At this time, transaction A is read twice before and after The data a may be different, that is, it cannot be read repeatedly.
Transaction A repeatedly reads data a within the transaction. At this time, regardless of whether other transactions update or delete data a, the results of multiple reads of data a within transaction A are consistent.
However, if transaction B performs an insert operation on data a at this time, transaction A can still read the new data of the insert, resulting in inconsistencies in reading data a multiple times. This phenomenon is called phantom reading.
The default isolation level of the mysql database innodb storage engine is repeatable read, and it solves the phantom read.
Transactions are executed serially, solving concurrency problems, solving dirty reads, non-repeatable reads, and phantom reads. However, because it is executed serially, the execution efficiency is very low, which greatly reduces the ability to handle concurrency.
The difference between repeatable reading and phantom reading
Judging from the overall results, both of them appear to be inconsistent with the results of the two readings.
The key points of the two are different:
- Repeatable read: The key is to ensure repeatable read in the case of update and delete
- Phantom reading: the key is to insert, in this case repeatable reading will still appear in phantom reading
From the perspective of locking, the difference between the two is obvious:
- Repeatable read: row lock, just lock the row that meets the condition
- Phantom reading: row lock + gap lock, you need to lock the rows and gaps that meet the conditions
In summary, the biggest difference between repeatable reading and phantom reading is how to solve the problem through the lock mechanism.