Detailed analysis of mysql database transactions and mysql Database Analysis

Detailed analysis of mysql database transactions and mysql Database Analysis

 

 

 

Let's explain the background before writing!

I have been doing development for a long time, but I have not learned much about it. Now I want to analyze my understanding of database transactions. I used sqlserver and now I switched to java and used mysql and oracle again. I will mainly explain mysql database transactions. In fact, a lot of content applies to a variety of standard databases!

Enter the text directly!

No matter what you do, you have theoretical knowledge. I will introduce it here.

• 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.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 query it again in another transaction window, the transaction cannot be read repeatedly.Avoid dirty reads, but there are already non-repeated reads and Phantom reads/virtual reads during queries!

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.Dirty read and non-repeated read.

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 isPhantom 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 !!

Total

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;