A brief discussion on database transactions and isolation levels

Transaction

Transaction refers to a set of operations that meet ACID characteristics. You can submit a transaction through Commit. You can also use Rollback to roll back.

ACID

1. Atomicity

Transactions are considered indivisible The smallest unit of the transaction, all operations of the transaction are either submitted successfully, or all operations fail and are rolled back. Rollback can be implemented using rollback logs. The rollback log records the modification operations performed by the transaction. These modification operations can be performed in reverse during rollback.

2. Consistency

Consistency means that a transaction must transform the database from one consistency state to another consistency state, that is to say a A transaction must be in a consistent state both before and after it is executed.

Take transfers as an example, assuming that the total money of user A and user B is 5,000, then no matter how the transfer is between A and B, or how many times the transfer is completed, the money of the two users will be the same after the transaction is completed. The total should add up to 5000, which is the consistency of the transaction.

3. Isolation

Isolation is when multiple users access the database concurrently, such as when operating the same table, the database is opened for each user The transaction cannot be interfered by the operations of other transactions, and multiple concurrent transactions must be isolated from each other.

To achieve such an effect: for any two concurrent transactions T1 and T2, from the perspective of transaction T1, T2 either ends before T1 starts, or starts after T1 ends, so Each transaction is unaware that other transactions are executing concurrently.

4. Durability

Durability means that once a transaction is submitted, the changes to the data in the database are permanent, even if The operation of committing transactions will not be lost even if the database system encounters a failure.

Concurrency consistency issues

1. Dirty read

Dirty read refers to a Data from another uncommitted transaction was read during transaction processing.

T1 modifies a data, and T2 then reads this data. If T1 undoes this modification, then the data read by T2 is dirty data.

2. Non-repeatable read

Non-repeatable read refers to a certain data in the database within the scope of a transaction Multiple queries returned different data values. This is because it was modified and committed by another transaction between queries.

For example, transaction T1 is reading certain data, and transaction T2 immediately modifies the data and submits the transaction to the database. When transaction T1 reads the data again, it gets a different result and sends a non-repeatable read.

The difference between non-repeatable read and dirty read is that dirty read is when a transaction reads the uncommitted dirty data of another transaction, while non-repeatable read is when the data submitted by the previous transaction is read.

3. Phantom reading

Phantom reading is a phenomenon that occurs when transactions are not executed independently. For example, transaction T1 modifies a data item in all rows in a table from "1" to "2". At this time, transaction T2 inserts a row of data items into the table, and the value of this data item Still "1" and submitted to the database. If the user who operates transaction T1 looks at the data that was just modified, he will find that there is still one row that has not been modified. In fact, this row was added from transaction T2. ​​It is like an hallucination. This is a phantom read.

Phantom reading and non-repeatable reading both read another committed transaction (this is different from dirty reading). The difference is that non-repeatable reading queries the same data item, while phantom reading queries Reading targets a batch of data as a whole (such as the number of data).

T1 reads data in a certain range, T2 inserts new data in this range, and T1 reads data in this range again. The result read at this time is different from the result read the first time. .

MySQL isolation level

##1. Serializable: Force transactions to be executed serially.

It can avoid the occurrence of dirty reads, non-repeatable reads and phantom reads.

2. Repeatable read: Ensure that the result of reading the same data multiple times in the same transaction is the same

Avoid dirty reads, A non-repeatable read occurs.

3. Read committed: A transaction can only read the modifications made by the committed transaction

This can avoid the occurrence of dirty reads.

4. Read uncommitted: Modifications in a transaction are visible to other transactions even if they are not committed

The lowest level, no guarantee in any situation.

The highest isolation level is the Serializable level, and the lowest is the Read uncommitted level. The higher the level, the lower the execution efficiency.

A level like Serializable uses a lock table (similar to the lock in Java multi-threading) so that other threads can only wait outside the lock, so the isolation level you usually choose should be based on the actual situation. .

The default isolation level in the MySQL database is Repeatable read (repeatable read).

In Oracle database, only two levels are supported: Serializable (serialization) level and Read committed (read committed). The default is Read committed level.

Recommended learning: MySQL tutorial

The above is the detailed content of A brief discussion on database transactions and isolation levels. For more information, please follow other related articles on the PHP Chinese website!