Analysis of Mysql transaction characteristics and level principles

1. What is a transaction?

A database transaction (abbreviated as: transaction) is a logical unit in the execution process of a database management system, consisting of a finite sequence of database operations.

2. Four major attributes of transactions

They are atomicity, consistency, isolation, and persistence.

1. Atomicity

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

2. Consistency

Consistency means that a transaction must transform the database from one consistent state to another consistent state, that is, a transaction must be in a consistent state before and after execution. For example, suppose the total amount of money of user A and user B is 1000. No matter how and how many times A and B transfer money, the total amount of money of the two users should still be 1000 after the transaction is completed. This is the consistency of the transaction.

3. Isolation

Isolation means that when multiple users access the database concurrently, such as operating the same table at the same time, the transaction opened by the database for each user cannot be interfered with by the operations of other transactions, and multiple concurrent transactions must be isolated from each other. Regarding transaction isolation, the database provides multiple isolation levels, which will be introduced later.

4. Durability

Persistence means that once a transaction is committed, the changes to the data in the database are permanent, and the transaction commit operation will not be lost even if the database system fails. For example, when we use JDBC to operate the database, after submitting the transaction method, the user is prompted that the transaction operation is completed. When our program is executed and we see the prompt, we can determine that the transaction has been submitted correctly. Even if there is a problem with the database at this time, we must fully execute our transaction. Otherwise, it will cause a serious error where we see the prompt that the transaction is completed, but the database fails to execute the transaction due to a failure. This is not allowed.

3. MySQL isolation level

READ UNCOMMITTED

In the uncommitted read transaction level, a transaction can read data that is not committed by another transaction.
For example, transaction A updates a field but is not committed. In transaction B, when reading the name field of the record with id 1000, the value of name is 'aaa', but transaction A is not committed, so name = 'aaa' may be rolled back. In this case, transaction A reads the data of an uncommitted transaction, which is called a dirty read.

READ COMMITTED

In the committed read transaction level, a transaction can read data after another transaction is committed.

For example, transaction A reads the name field of the record with id 1000 as aaa, and then transaction B updates the name value of this record and commits it. If transaction A reads name again, the value of name will be bbb. Therefore, in a transaction, if a field is read multiple times, the values ​​obtained may be different.

The values ​​read twice before and after transaction A are inconsistent!

REPEATABLE READ

In the repeatable read transaction level, the fields that a transaction reads repeatedly do not change.

For example, transaction A reads the record with id 1000 and the name value is aaa, then transaction B changes the name to bbb, and transaction B is committed. When transaction A reads name again, it will not read bbb. Therefore, transaction A is equivalent to being in an independent world, and any changes from the outside will not affect transaction A.

However, repeatable reads can lead to phantom reads. What are phantom reads? For example:
Transaction A queries a table. There is only one record in the table with id 1. However, transaction B inserts a record with id 2. Transaction A does not know that there is data with id 2, so it also inserts a record with id 2. This will definitely fail. This situation is called phantom read.

Note: InnoDB in MYSQL solves phantom reads through MVCC (Multi-version Concurrency Control). In addition, the default transaction level of MYSQL is repeatable read, and the default isolation level of Oracle and SQL Server is Read committed.

SERIALIZABLE

Serializable transaction level, locks each row of data read

The advantage of locking is that it avoids dirty reads and phantom reads, and also avoids the possibility of non-repeatable reads. However, because of locking, concurrency is greatly reduced, because only one thread can obtain the lock at the same time. It may also cause a lot of timeout problems.

Summary: Non-repeatable reads and phantom reads are easily confused. Non-repeatable reads focus on modifications, while phantom reads focus on additions or deletions. To solve the problem of non-repeatable reads, you only need to lock the rows that meet the conditions. To solve the problem of phantom reads, you need to lock the table.

Isolation Level:

The higher the level, the more secure your data is, but the performance is lower.

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.