A detailed analysis of MySQL database transactions

Explain the background before you write!

Do development also for a long time, not how to tidy up the knowledge, now analyze their own understanding of database transactions, formerly with SQL Server, now turn Java and use MySQL, Oracle. This is my main explanation for MySQL database transactions. In fact, a lot of content for a variety of standard database!

Straight into the text!

No matter what you do, there is theoretical knowledge, I also introduce in this piece.

• Characteristics of a transaction

1. atomicity (atomicity): atomicity means that a transaction is an inseparable unit of work, and the operations in the transaction either occur or do not occur.

2. Consistency (consistency): In one transaction, the integrity of the data before and after the transaction must be consistent, you can imagine bank transfer, train ticket purchase.

3. Isolation (Isolation): Multiple transactions, the isolation of transactions refers to multiple users concurrently accessing the database, one user's transaction can not be interfered by other users ' transactions, the data between multiple concurrent transactions are isolated from each other.

4. Persistence (Durability): persistence refers to the fact that once a transaction is committed, it changes the data in the database to be permanent, and then it should not have any effect even if the database fails.

Writing these concepts is a bit of a detour! Go around it! Anyway, it is not my rule, this is the official, explanation is I made up!!!

• Concurrent access issues for transactions

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

1. Dirty reads: In one transaction, when the data is read, the data that is not committed by another transaction is read.

For example, a account to the B account for 1 dollars, but a did not commit transactions, by the B account through the dirty read to see, then, B will think a has turned over the money, but at this time, a account rollback transaction. Actually the money did not give B turn over, but B itself thought a has turned over,,, a bit around, estimate is I describe to around!

Look at the code:

Update account set money=money+1 where name= ' B ';   --At this point A goes to notify BUpdate account set money=money-1 where name= ' A ';

　　

2. Non-repeatable READ: In a transaction, two reads the data content is inconsistent, because at the time of the query, the data is submitted by another transaction has been modified, then there is a problem.

3. Phantom read/virtual read: In one transaction, the amount of data read two times is inconsistent.

• Isolation level of a transaction

3 transactional concurrency issues are described above! Now introduce the solution provided by the database!

1.read UNCOMMITTED: READ UNCOMMITTED data: This is the lowest level, but the efficiency is certainly the highest, but which problem can not be solved.

2.read committed: Reads the data that has been submitted: can resolve dirty reads.

3.repeatable read: Reread reads: can resolve dirty read and non-repeatable read.

4.serializable: Serialization: Can solve the dirty read non-repeatable read and read, the least efficient, equivalent to the lock table, generally do not use in development.

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

Here's what I'll focus on. The MySQL database demonstrates on the various transaction isolation levels above:

Two syntax is introduced first:

1. View the default isolation level for MySQL database: SELECT @ @tx_isolation

2. Set the isolation level for MySQL: Set session transaction ISOLATION LEVEL transaction isolation Levels

• Isolation level presentation for transactions

Note: If you want to simulate to open two MySQL client, that is to simulate two users!

1.read UNCOMMITTED

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

First I have an account form.

Window One

Windows two

database table raw Data money is 5000, and when I start a transaction, the Zhangsan account increases by 1000 and the John Doe account minus 1000, but my transaction has not yet been committed, but when I query the database table again, the data has changed, which is dirty read and non-repeatable read !

Phantom read/False reading I will not hide, also exist!

2.read committed

I changed the database transaction isolation to read committted.

Or the table above:

Window One

Windows two

database table Zhangsan account money and Lisi account money have changed, I did not commit the transaction, the other window transaction opened under the query, there is no dirty read, but when I commit a transaction, in another window under the transaction again, there is a non-repeatable read situation, so that can avoid dirty reads, but the query has already appeared non-repeatable read and Phantom read/virtual read!

3.repeatable Read

I changed the database transaction isolation to read committted.

Or the table above:

Window One

Windows two

I opened the transaction in two windows, when the window one after the data operation, and commit the transaction, in the case of Windows two transaction, I went to query, no query to just window one of the data operation record, so that the dirty read and non-repeatable read .

Some people say that also avoids the false reading/Phantom reading, actually does not have.

Look at the picture:

When I operate the Lisi account, the data only changes change, but when I modify WANGWU this account, then to inquire about the WANGWU, account data, but in fact, before I do not operate, Wangwu account data is not out of the query. This is the Phantom Read/False reading!

If you do not understand the Phantom read/False reading this piece, you can check the InnoDB.

4.serializable

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

A total.

Performance at the transaction isolation level:

Read Uncommitted>read committed>repeatable read>serialazable

Security at the transaction isolation level:

Read Uncommitted<read committed<repeatable read<serialazable

MySQL's transaction control:

Open transaction: Start transaction;

Commit: Commit;

Rollback: rollback;

A detailed analysis of MySQL database transactions