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

Today, I would like to talk to you about how to understand the affairs in MySQL. Many people may not know much about it. In order to make you understand better, the editor has summarized the following for you. I hope you can get something from this article.

Transaction is also called TCL, and its full name is transaction control language, which means transaction control language.

I. understanding of affairs

Zhang San had 1000 yuan, Li Si also had 1000 yuan, Zhang San gave Li Si 500, and Li Si had 1500 at this time. We imagine that the following mysql statement will be executed:

Update table user set money=500 where name = "Zhang San"; update table user set money=1500 where name = "Li Si"

But it may be different in a computer. It is possible that the following statements are not executed, so transactions are used to ensure that both statements are executed or not executed.

A transaction means that a statement or set of statements forms a unit that either executes all or none at all.

Transactions have four characteristics, which are also four features that are often tested in interviews: ACID:

A (atomicity Atomicity): atomicity means that transactions are indivisible and either all are executed or none are executed.

C (consistency Consistency): transactions must move the database from one consistency state to another.

I (isolated Isolation): refers to the execution of a transaction that cannot be interfered with by other transactions.

D (persistence Durability): persistence means that once a transaction is committed, the change to the database is permanent.

Create a transaction

Transactions can actually be divided into two main categories: implicit transactions and displayed transactions:

Implicit transactions are very simple, such as our insert, delete, update, select statements are implicit transactions.

The displayed transaction refers to the marked start and end, so let's create a displayed transaction.

Step 1: disable the step submission feature

Set autocommit = 0

Step 2: start a transaction

Start transaction

Step 3: sql statement

Update table user set money=500 where name = "Zhang San"; update table user set money=1500 where name = "Li Si"

Step 4: end the transaction

Commit (commit) or rollback (rollback). If we are sure that there is nothing wrong with our statement, then we can commit, and if we think there is something wrong with our statement, then rollback.

A new table is created here, and two pieces of data are inserted. Let's update it with transactions:

Here we are using commit to submit. Of course, if we suddenly find that there is something wrong with our previous operation, we can use rollback.

III. Isolation level of the transaction

The above transaction is generally not a problem in a single case, but if you run more than one at the same time, there will be a problem. We know that there are always a variety of problems with concurrent operations, and the following three typical problems arise for transactions:

(1) dirty reading

There are two transactions T1 and T2. If T1 reads a piece of data that is updated by T2 but has not yet been committed, T2 suddenly feels inappropriate for transaction rollback, that is, it will not commit. At this point, the data read by T1 is invalid data.

(2) non-repeatable

There are two transactions T1 and T2. If T1 reads a piece of data, and then T2 updates the data, T1 reads it again and finds that the value has changed.

(3) Phantom reading

There are two transactions T1 and T2. If T1 reads a piece of data, and then T2 inserts some new data, there will be more data when T1 reads again.

How to solve these problems? Since multiple transactions don't work well at the same time, isolate them. At this point, transaction isolation is used.

The default transaction isolation level for mysql is repeatable-read,Oracle. The default is read-committed.

It's also easy to demonstrate the isolation level of a transaction. You only need to open two client presentations to OK, not to mention here.

After reading the above, do you have any further understanding of how to understand the affairs in MySQL? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

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.