The concept of transactions

Transactions are used to guarantee the integrity of a business operation, either success or failure, with no partial success or partial failure allowed.

The four properties of transactions are ACID

Atomicity: Atomicity

Atomicity means that all operations contained in a transaction either succeed or fail to be rolled back. Therefore, if the operation of a transaction succeeds, it must be fully applied to the database. If the operation fails, it has no impact on the database.

Consistency: Consistency

Consistency means that a transaction must move the database from one consistent state to another, that is, a transaction must be in a consistent state both before and after execution

Isolation: Isolation

Isolation means that when multiple users concurrently access the database, for example, when operating on the same table, the transactions initiated by the database for each user cannot be disturbed by the operations of other transactions, and multiple concurrent transactions must be isolated from each other.

Durability: persistence

Persistence means that once a transaction is committed, the changes made to the data in the database are permanent, and the operation of committing the transaction will not be lost even if the database system encounters a failure.

The isolation level of the transaction

In general, there are only two types of database transactions: read transactions (SELECT) and modify transactions (Update, INSERT). In the absence of transaction isolation control, multiple transactions operating on the same data at the same time may affect the desired results. There are generally four situations:

  1. When two update transactions modify a single data at the same time, this is obviously the most serious situation, and should not happen in the program anyway, because it will cause the loss of updates!
  2. Dirty data is read when one update transaction updates a piece of data while another read transaction reads an update that has not yet been committed.
  3. An unrepeatable read occurs when a read transaction reads a piece of data and another update transaction modifies the data.
  4. When one read transaction reads, another insert transaction inserts a new data, so it is possible to read one more data, resulting in phantom reads.

Read Uncommitted

Read uncommitted, as the name implies, means that one transaction can read data from another uncommitted transaction. Example: The boss wants to pay programmers, and the programmer’s salary is 36,000 yuan/month. However, the boss accidentally pressed the wrong number when the salary was paid, and pressed 39,000 / month. The money had been hit to the programmer’s account, but the transaction had not been submitted. At this moment, the programmer went to check his salary this month, and found that he had increased his salary by 3,000 yuan more than usual. However, the boss noticed the error in time and immediately rolled back the transaction that had almost been committed and changed the number to 36,000.

Read Committed

Read commit, as the name implies, is when a transaction waits for another transaction to commit before it can read data. Example: programmer took the credit card to enjoy life (the card is only 36,000 of course), when he paid the bill (programmer transaction open), the charging system detected that his card has 36,000, at this time!! The programmer’s wife had to transfer all the money out for household use and submit it. When the charging system was preparing to deduct money, it tested the amount of the card again and found that there was no money left (of course, the amount of the second test should wait for the wife to transfer the amount of the transaction submitted). Programmers will be very depressed, clearly the card is rich…

Repeatable Read

Repeatable read, it is in the beginning to read data (transaction open), is no longer allowed to modify the operating example: programmers with a credit card to enjoy life (card, of course, is only 36000), when he pay (affairs open, don’t allow other transactions UPDATE modification operations), charge system in advance his card was detected in 36000. At this point his wife can’t transfer the amount. The next payment system can deduct money.

Serializable

This is the highest isolation level, and it solves the phantom problem by forcing transactions to be ordered so that they cannot conflict with each other. In short, it places a shared lock on each read row. At this level, a lot of timeouts and lock contention can result.

Issues arising from isolation levels

Dirty read

A dirty read refers to reading data from another uncommitted transaction during a transaction. The data in Figure 1 is dirty read

Unrepeatable read

Non-repeatable reads are those that return different values from multiple queries within a transaction scope for a particular data in the database because it was modified and committed by another transaction during the query interval. The data in Figure 2 creates the problem of unrepeatable reads

Phantom read

Unreal and unrepeatable reads both read another transaction that has already been committed (dirty reads are different). The difference is that unrepeatable reads query for the same data item, whereas unreal reads are for a batch of data as a whole (such as the number of data).