MySQL transactions, locks and applications (1)

1. What is a transaction?

A transaction is a combination of one or more database operation statements and has four ACID characteristics:

• Atomicity ( Atomicity)

Either all succeed or all are cancelled.

• Consistency (Consistency)

After the database changes state correctly, the consistency constraints of the database are not destroyed.

• Isolation

Transactions are independent of each other and do not interfere with each other.

• Durability (Durability)

The submission results of the transaction will be persisted in the database.

2. Problems caused by transaction concurrency

If there is no transaction isolation, it may occur:

• Dirty read

Dirty reading means that when a transaction reads a piece of data and modifies the data, but has not yet submitted it, another transaction also reads the piece of data and then uses the data. Article data. For example: the user's account amount is 100, T1 modifies it to 200, but does not submit it. At the same time, T2 reads that the user's account amount is 200, and then T1 rolls back abnormally, and the 200 read by T2 is dirty data.

• Non-repeatable read

Non-repeatable read means that for a certain piece of data in the database, multiple queries within a transaction return different values. , this is because it was modified and committed by another transaction during the query interval.

• Phantom reading

The number of data rows in the two queries of a transaction is inconsistent. For example, one transaction queries several columns of data, and another transaction inserts several new columns of data at this time. In the next query, the first transaction will find that there are several columns of data that it did not have before, as if it was an illusion. Same, this is a phantom reading occurring.

3. Transaction isolation level

What concurrency problems does the transaction isolation level solve? What other concurrency problems exist?

1. Read Uncommitted (read uncommitted content)

This is the lowest isolation level of a transaction, which allows another transaction to see this Transaction uncommitted data. There is no way to avoid any problems with concurrency.

2. Read Committed (read submission content)

Ensure that the data modified by one transaction can only be read by another transaction, that is, another transaction A transaction cannot read data that has not been committed by the transaction. Dirty reads can be avoided, but non-repeatable reads and phantom reads may occur.

3. Repeatable Read (rereadable)

This is the default transaction isolation level of MySQL, which ensures that multiple instances of the same transaction can read concurrently data, you will see the same data. Dirty reads and non-repeatable reads can be avoided, but phantom reads may occur.

4. Serializable (serializable)

This is the highest isolation level. It forces transactions to be ordered so that they cannot conflict with each other. At this level, a lot of timeouts and lock contention can result. It can avoid the occurrence of dirty reads, non-repeatable reads and phantom reads.

The next article introduces the locking mechanism of the InnoDB engine.

The above is the content of MySQL transactions, locks and applications (1). For more related content, please pay attention to the PHP Chinese website (m.sbmmt.com)!