Basic MySQL transaction learning and experience sharing, mysql transaction Experience Sharing

Basic MySQL transaction learning and experience sharing, mysql transaction Experience Sharing

A transaction is a logical group of operations that comprise all the units of this group of operations. If it fails or fails, it is a transaction. The following is my experience in MySQL transaction learning:

Features of transactions

1. Atomicity: a transaction is an inseparable unit of work. Operations in a transaction either occur or do not occur.

2. Consistency: in a transaction, the data integrity before and after the transaction must be consistent. You can imagine bank transfers and train tickets.

3. isolation: multiple transactions. transaction Isolation means that when multiple users access the database concurrently, one user's transactions cannot be disturbed by other users' transactions, data of multiple concurrent transactions must be isolated from each other.

4. Durability: Once a transaction is committed, its changes to the data in the database are permanent. Then, even if the database fails, it should not have any impact on it.

Writing these concepts is a bit difficult! Go around! It's not what I set out. It's official and I edited it !!!

Concurrent access to transactions

If we do not consider the isolation problem, there are three concurrent access problems for the transaction.

1. Dirty read: when reading data in a transaction, it reads uncommitted data from another transaction.

For example, account A transferred 1 RMB to account B, but account A did not submit the transaction and was seen by account B through dirty reading. Then, B will assume that account A has transferred the money, however, account A rolls back the transaction. In fact, the money was not transferred to B, but B thought that A had already been transferred. It was A bit difficult. I guess it was my description!

Check the Code:

Update account set money = money + 1 where name = 'B'; -- at this time, A notifies the Bupdate account set money = money-1 where name = 'a ';

2. repeatable read: in a transaction, the data content read twice is inconsistent, because there is a time interval during the query, and the data has been modified and committed by another transaction, then there will be problems.

3. Phantom read/virtual read: The data volume read twice in a transaction is inconsistent.

Transaction isolation level

The preceding three transaction concurrency problems are introduced! Now we will introduce the solutions provided by the database!

1. read uncommitted: read data that has not yet been submitted. This is the lowest level, but the efficiency is definitely the highest. But none of these problems can be solved.

2. read committed: read committed data: Dirty read can be solved.

3. repeatable read: re-reading: it can solve the problem of dirty reading and non-repeated reading.

4. serializable: it can solve the problem of non-repeated reads and virtual reads of dirty reads, with the worst efficiency. It is equivalent to locking tables and is generally not used during development.

The above "2" is the default setting of the oracle database, and "3" is the default setting of the mysql database.

Next, I will focus on the demonstration of mysql databases at various transaction isolation levels above:

First, we will introduce two syntaxes:

1. view the default isolation level of the mysql database: select @ tx_isolation

2. set the mysql isolation level: the isolation level of the set session transaction isolation level transaction

Demonstration of transaction isolation level

Note: If you want to activate two mysql clients, that is, simulate two users!

1. read uncommitted

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

First, I have an account table.

Window 1

Window 2

The original data money in the database table is 5000. After I started the transaction, I increased 1000 in the zhangsan account and reduced 1000 in the Li Si account, but my transaction has not yet been committed, however, when I query the database table again, the data has changed. This is dirty read and non-repeated read!

Phantom read/virtual read I will not hide it, it also exists!

2. read committed

 

I changed the database transaction isolation to read committted.

Or the table above:

Window 1

Window 2

 

 

The money in the database table zhangsan account and the money in the lisi account have changed. I did not submit a transaction. When another window transaction is opened for query, no dirty reads appear, however, when I commit a transaction and re-query it in another transaction window, there may be non-repeated reads, which can avoid dirty reads, however, repeated read and phantom read/virtual read are available during query!

3. repeatable read

I changed the database transaction isolation to read committted.

Or the table above:

Window 1

Window 2

I have enabled transactions in both windows. When the data operation is performed in window 1 and the transaction is committed, when the transaction in window 2 is started, I will query, the data operation record of window 1 was not found. This avoids dirty reads and non-repeated reads.

Some people say that virtual read/phantom read is also avoided.

Figure:

When I operate the lisi account, the data only changes. However, when I modify the account wangwu, I query the data of the account wangwu, however, before I did not perform any operations, the wangwu account data could not be queried. This is Phantom read/virtual read!

If you do not understand the phantom read/virtual read, you can check InnoDB.

4. serializable

I will not demonstrate it. It is not recommended for development, and the efficiency is slow, but all problems can be avoided !!

To sum up

Transaction isolation-level performance:

Read uncommitted> read committed> repeatable read> serialazable

Transaction isolation-level security:

Read uncommitted <read committed <repeatable read <serialazable

Mysql transaction control:

Start transaction: start transaction;

Submit: commit;

Rollback: rollback;