Shulou(Shulou.com)05/31 Report--

This article mainly introduces the basic learning of MySQL affairs and experience sharing, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to understand.

A transaction is a logical set of operations that make up the units of this group of operations. Either they all succeed or all fail. This feature is transactions. Here are the lessons learned from MySQL transaction learning:

Characteristics of transactions

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

two。 Consistency: in a transaction, the integrity of the data before and after the transaction must be 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, one user's transaction cannot be interfered by other users' transactions, and the data of multiple concurrent transactions should be isolated from each other.

4. Durability: persistence means that once a transaction is committed, its changes to the data in the database are permanent, and then even if the database fails, it should not have any impact on it.

It's a bit roundabout to write these concepts! Just go around! Anyway, it's not my rule, it's official, and I made up the explanation!

Concurrent access to transactions

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

1. Dirty read: in one transaction, when data is read, the uncommitted data of another transaction is read.

For example, An account transferred 1 yuan to B account, but A did not commit the transaction and was seen by B account through dirty reading. At this time, B will think that A has transferred the money, but at this time, An account rolls back the transaction. In fact, the money was not transferred to B, but B himself thought that A had already turned around. It was a bit of a circle. I probably described it.

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'

two。 Non-repeatable reading: in one transaction, the contents of the data read two times are inconsistent, because there is a time interval during the query, and the data has been modified and committed by another transaction, which will cause problems.

3. Phantom / virtual reading: in a transaction, the amount of data read by two times is inconsistent.

Isolation level of the transaction

Three kinds of transaction concurrency problems are introduced above! Now let's introduce the solutions provided by the database!

1.read uncommitted: reading uncommitted data: this is the lowest level, but certainly the most efficient, but none of the problems can be solved.

2.read committed: read data that has been submitted: can solve dirty reading.

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

4.serializable: serialization: can solve dirty reading can not be repeated read and virtual read, the worst efficiency, equivalent to locking table, generally not used in development.

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

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

Let's start with two grammars:

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

As shown in the figure:

two。 Set the isolation level for mysql: the isolation level for set session transaction isolation level transactions

As shown in the figure:

Demonstration of isolation level for transactions

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

1.read uncommitted

As shown in the figure:

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

First of all, I have an account table.

As shown in figure 1: window 1

Window two

Database table raw data money is 5000, when I started the transaction, the zhangsan account increased by 1000 and the Li Si 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 reading and non-repeatable reading!

I will not hide the illusion / false reading, it also exists!

2.read committed

As shown in the figure:

I changed the database transaction isolation to read committted.

It's the same table above:

As shown in figure 1: window 1

Window two

Database table Zhangsan account money and lisi account money have changed, I did not commit the transaction, when another window transaction opened the query, there was no dirty reading, but when I submitted the transaction, I queried again under the transaction of another window, there was a situation that can not be read repeatedly, so that dirty reading can be avoided, but there has been unrepeatable reading and false reading / virtual reading when querying.

3.repeatable read

As shown in the figure:

I changed the database transaction isolation to read committted.

It's the same table above:

As shown in figure 1: window 1

Window two

I opened the transaction in both windows, and the transaction was committed after the data operation was carried out in window 1. When the transaction in window 2 was opened, I went to query and did not query the data operation record of window 1 just now. In this way, dirty reading and non-repeatable reading can be avoided.

Some people say that false reading / illusory reading is also avoided, but there is no such thing.

Look at the picture:

When I was operating the lisi account, the data only changed, but when I modified the wangwu account, I went to query the data of the wangwu account, but in fact, before I operated, the data of the wangwu account could not be queried. This is fantasy / virtual reading!

If you don't understand the fantasy / virtual reading section, you can check InnoDB.

4.serializable

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

To sum up.

Performance of the transaction isolation level:

Read uncommitted > read committed > repeatable read > serialazable

Transaction isolation level security:

Read uncommitted

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.