Getting started with database transactions

Scenes

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:

  1. 100 yuan deducted from account A
  2. 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.

Insert picture description here


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

Insert picture description here

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

consistency

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

Atomicity

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.

Persistence

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.

Isolation

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

Read uncommitted

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.

Insert picture description here

Read submitted

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.

Insert picture description here

Repeatable

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.

Insert picture description here

Serialization

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.