Basic learning and experience sharing of MySQL transactions

A transaction is a logical group of operations. Each unit that makes up this group of operations must either succeed or fail. This feature is called a transaction. Here are some tips on learning MySQL transactions:

Characteristics of transactions

1. Atomicity: Atomicity means that a transaction is an indivisible unit of work, and the operations in a transaction either all occur or none of them occur.

2. Consistency: In a transaction, the integrity of the data before and after the transaction must remain consistent. You can imagine bank transfers and train ticket purchases.

3. Isolation: Multiple transactions. Transaction isolation means that when multiple users access the database concurrently, the transaction of one user cannot be interfered with by the transactions of other users, and the data between multiple concurrent transactions must be isolated from each other.

4. Durability: Durability means that once a transaction is committed, the changes it makes to the data in the database are permanent, and even if the database fails, it should not be affected in any way.

Writing about these concepts is a bit confusing! Just go around it! Anyway, it’s not my rule, it’s official, and the explanation is made up by me! ! !

Concurrent access issues of transactions

If we do not consider the isolation problem, there are three types of concurrent access problems in transactions.

1. Dirty read: In a transaction, when reading data, uncommitted data from another transaction is read.

For example, account A transfers 1 yuan to account B, but A does not commit the transaction. Account B sees it through dirty read. At this time, B will think that A has transferred the money, but at this time, account A rolls back the transaction. In fact, the money was not transferred to B, but B himself thought that A had transferred it. It's a bit confusing. I guess it's because of my description!

Look at the code:

update account set money=money+1 where name='B'; --A now notifies B
update account set money=money -1 where name='A';

2. Non-repeatable read: In a transaction, the data content read twice is inconsistent. This is because there is a time interval between the queries, and the data has been modified and submitted by another transaction, which will cause problems.

3. Phantom read/virtual read: In a transaction, the amount of data read twice is inconsistent.

Transaction isolation level

The above introduces 3 types of transaction concurrency problems! Now let’s introduce the solution provided by the database!

1.read uncommitted: read data that has not been committed: This is the lowest level, but it is definitely the most efficient, but it cannot solve any problem.

2.read committed: read committed data: can solve dirty read.

3.repeatable read: repeatable read: can solve dirty read and non-repeatable read.

4.serializable: Serialization: can solve dirty reads, non-repeatable reads and virtual reads. It has the worst efficiency and is equivalent to locking the table. It is generally not used in development.

The above "2" is the default setting for Oracle database, and "3" is the default setting for MySQL database.

Next, I will focus on explaining the demonstration of the MySQL database at the various transaction isolation levels above:

First, we introduce two grammars:

1. View the default isolation level of the MySQL database: select @@tx_isolation

As shown in the figure:

2. Set the isolation level of MySQL: set session transaction isolation level transaction isolation level

As shown in the figure:

Transaction isolation level demonstration

Note: If you want to simulate yourself, you need to open two MySQL clients, that is, simulate two users!

1. read uncommitted

As shown in the figure:

I changed the transaction isolation level of the database to read uncommitted via syntax.

First I have an account table.

As shown in the figure: Window 1

Window 2

The original data money in the database table is 5000. When I started the transaction, 1000 was added to zhangsan's account and 1000 was subtracted from lisi's account. However, my transaction has not been committed yet. However, when I queried the database table again, the data had changed. This is dirty read and non-repeatable read!

I won’t hide the fact that phantom reading/virtual reading exists as well!

2.read committed

As shown in the figure:

I changed the database transaction isolation to read committedtted.

Still the table above:

As shown in the figure: Window 1

Window 2

The money of the database table zhangsan's account and the money of the account lisi have changed. I did not commit the transaction. When I queried with the transaction in another window open, no dirty read occurred. However, when I committed the transaction and queried again under the transaction in another window, a non-repeatable read occurred. This can avoid dirty reads, but non-repeatable reads and phantom reads/virtual reads have occurred during the query!

3. Repeatable read

As shown in the figure:

I changed the database transaction isolation to read committedtted.

Still the table above:

As shown in the figure: Window 1

Window 2

I opened transactions in both windows. After window one performed data operations and committed the transaction, I queried the data in window two while the transaction was open. I found no data operation records in window one. This avoided dirty reads and non-repeatable reads.

Some people say that it also avoids false reading/phantom reading, but it doesn't.

See the picture:

When I operated the lisi account, only the data was modified. But when I modified the wangwu account, the data of the wangwu account appeared when I queried it. But in fact, before I operated it, the data of the wangwu account could not be queried. This is phantom reading/virtual reading!

If you don't understand phantom reads, you can check InnoDB.

4.Serializable

I won’t demonstrate it. It is not recommended for development and is slow, but all problems can be avoided! !

To sum up

Transaction isolation level performance:

read uncommitted>read committed>repeatable read>serializable

Security of transaction isolation levels:

read uncommitted < read committed < repeatable read < serialazable

mysql transaction control:

Start transaction: start transaction;

Submit: commit;

Rollback: rollback;