MySQL transaction, this article is enough

Time:2020-5-23

Original link: https://blog.ouyangsihai.cn/ >>MySQL transaction, this article is enough

Before reading this article, let’s review the previous series of articles on MySQL, which should be helpful for you to read the following articles.

  • Comparison of storage engines such as InnoDB and MyISAM
  • When the interviewer asks you B-tree and B + tree, he will leave this article to him
  • The concept, usage, optimization and usage scenario of MySQL B + tree index
  • MySQL full text index tutorial
  • Another artifact of MySQL – lock, necessary for MySQL interview

0 what is a transaction

TransactionIs the basic unit of concurrency control. The so-called transaction is a sequence of operations. These operations are either executed or not executed. It is an indivisible unit of work. Transaction is the unit for database to maintain data consistency. At the end of each transaction, data consistency can be maintained.

At the same time, transactions are strictly defined and must meet four characteristics, that is, acid, as we have always said. However, it does not mean that all kinds of databases must meet four characteristics. For different database implementations, to varying degrees, they may not fully meet the requirements. For example, for Oracle databases, the default transaction isolation level isREAD COMMITTED, does not meet the isolation requirements.

Now let’s take advantage of the hot iron to introduce the four characteristics of the necessary knowledge of business. These characteristics are also in the interview. When interviewers interview mysql, they ask more questions. Therefore, these characteristics must be understood and remembered thoroughly. Make a joke, be hit by a train, and they should not be forgotten!

Four characteristics of transaction

The four characteristics of a transaction are simply as follows:ACID, respectivelyAtomicity, consistency, isolation and persistence

Let’s introduce it.

  • Atomicity

Atomicity refers to that the transaction of the whole database is an indivisible unit of work, each of which should be an atomic operation.

When we execute a transaction, if one of a series of operations fails, we need to restore all operations in the transaction to the state before the transaction is executed, which is the atomicity of the transaction.

Here is a simple example.

i++;

The simplest code above is often asked, is this an atomic operation? That’s certainly not. If we put this code into a transaction, wheni+1When there is a problem, the whole code I + + (I = I + 1) is rolled back, so the value of I will not change after the rollback.

That’s the concept of atomicity.

  • Consistency

uniformityIt means that the transaction changes the database from one state to the next consistent state, that is to say, before and after the transaction execution, the two states should be the same, that is, the integrity constraint of the database will not be broken.

In addition, it should be noted that consistency does not pay attention to the intermediate status, such as the process of bank transfer. You transfer to others. As for the intermediate status, you are less than 500, and he is more than 500. These intermediate statuses do not pay attention to. If you transfer multiple times, the intermediate status is not visible. Only the final success or failure status is visible.

When it comes to distributed consistency, it can be divided into strong consistency, weak consistency and final consistency. It’s interesting to check these concepts by yourself.

  • Isolation

We can start many transactions at the same time in MySQL database, but they are separated from each other, that is to say, they do not affect each other, that is, transactionsIsolation

  • Durability

TransactionalpersistenceOnce a transaction is committed, it is permanent. If a problem occurs, the database can be recovered. Therefore, persistence guarantees high reliability of transactions.

2 classification of transactions

Transactions can be divided into many types, generally including:Flat transactions, flat transactions with savepoints, chain transactions, nested transactions, distributed transactions

flat transaction

Flat transaction is the simplest one, and it is also the most used one in actual development. In this transaction, all operations are at the same level, and the most common way is as follows:

BEGIN WORK
Operation 1
Operation 2
Operation 3
...
Operation N
COMMIT WORK

for instance

begin work;

select * from user;

update user set name = 'sihai' where id = 1;

commit work;

The main purpose of flat transactionsshortcomingYou cannot commit or roll back a part of a transaction, or commit in several independent steps.

Flat transactions with savepoints

In addition to supporting operations supported by flat transactions, the biggest difference between such transactions and flat transactions isAllow rollback to an earlier state in the same transaction during transaction execution, this is because the errors that may occur in the execution of some transactions will not be invalid for all operations, it is not satisfactory to abandon the whole transaction, and the cost is too large.Save pointIt is used to inform the system that the current state of the transaction should be remembered so that the transaction can return to that state in the event of an error in the future.

for instance

begin work;

select * from user;

savepoint t1;

update user set name = 'sihai' where id = 1;

savepoint t2;

commit work;

In the above way, we set up two savepoints T1 and T2, which are saved byROLLBACK TO SAVEPOINT t1, we can go back toSave point T1

Chain transactions

Chain transaction: when a transaction is submitted, the unnecessary data objects are released, and the necessary processing context is implicitly passed to the next transaction to be started. Note that the commit transaction and the next transaction will be combined into oneAtomic operation, that is, the next transaction can see the result of the previous transaction.

Chain transaction means that when rolling back, only the latest savepoint can be recovered, while flat transaction with savepoint can be rolled back to any correct savepoint.

MySQL transaction, this article is enough

for instance

begin work;

select * from user;

savepoint t1;

update user set name = 'sihai' where id = 1;

savepoint t2;

commit work;

This is still the case, but for chain transactions, it is not possible to roll back directly to the savepoint T1, which can be recovered to the nearest savepoint T2. In addition, weNeed attentionThe chain transaction will release all locks held by the current transaction after executing the commit, while the flat transaction with a savepoint will not affect the locks held.

Nested transactions

The transaction is nested in the transaction. This structure is a bit like the structure of a horizontal tree. The transaction at the root node is called the top transaction. The predecessor of a transaction is called a parent transaction, and other transactions are called child transactions. The predecessor of a transaction is called a parent transaction, and the next level of a transaction is called a child transaction.

A child transaction can be committed or rolled back, but its commit does not take effect immediately unless it is committed by its parent transaction. Therefore, it can be determined that any sub transaction is actually committed after the top transaction is committed. Similarly, the rollback of any transaction will cause all its sub transactions to be rolled back together.

MySQL transaction, this article is enough

BEGIN WORK
     SubTransaction1:
             BEGIN WORK
                 SubOperationX
             COMMIT WORK
     SubTransaction2:
             BEGIN WORK
                 SubOperationY
             COMMIT WORK
     ...
     SubTransactionN:
             BEGIN WORK
                 SubOperationN
             COMMIT WORK
COMMIT WORK

Distributed transaction

Distributed transaction usually refers to a flat transaction running in a distributed environment, so it needs to access different nodes in the network according to the data location.

In different physical addresses, through network access, different transactions are executed, which is called distributed transactions.

3 use of transactions

First of all, in this part, we’d like to introduce the statements of these transactions, which are not many and complicated to use. Let’s use a table to sort them out.

MySQL transaction, this article is enough

be carefulCOMMITandCOMMIT WORKThe difference between these statements is that commit work is used to control the behavior after the transaction is completedCHAINstillRELEASE, if it is chain, then the transaction isChain transactions

The user can use the parametercompletion_typeControl, as follows:

MySQL transaction, this article is enough

  • completion_ Type = 1 instance

Perform the following operations:;

SET @@completion_type = 1;

BEGIN WORK;

INSERT INTO lock_test SELECT 10;

COMMIT WORK;

Then we will perform the following operations:;

INSERT INTO lock_test SELECT 115;

ROLLBACK;

 SELECT * FROM lock_test;

We insert a piece of data 115 first, and then roll back. We know that 115 should be inserted successfully if it is not in a transaction. Even if we roll back, but after we roll back, the query results are as follows:

MySQL transaction, this article is enough

At this time, there is no record 115, that is, rollback takes effect. It means thatCOMMIT WORKAfter that, it is a new transaction, so this result will appear.

  • completion_ Type = 2 instance

Let’s do the following first;

SET @@completion_type = 2;

BEGIN WORK;

INSERT INTO lock_test SELECT 5;

COMMIT WORK;

Above we have committed the transaction, when we use the following statement to query the lock_ When testing data, it will appearDisconnect

SELECT * FROM lock_test;

MySQL transaction, this article is enough

4 isolation level of transaction

There are four isolation levels for transactions:

  • READ UNCOMMITTED
  • READ COMMITTED
  • REPEATABLE READ
  • SERIALIZABLE

For the problems and summary of these isolation levels, you can see this article: another magic weapon of MySQL – lock, which is necessary for MySQL interview

5 Summary

This article introduces the contents of MySQL database transaction from the following contents, and other contents in more details will be explained later.

  • concept
  • Transaction type
  • Transaction usage
  • Isolation level of transaction

1、Hard to createOld fellow, iron article needs you.give the thumbs-upLet more people see, hope to help you!

2. If you like wechat reading, you can also pay attention to myWeChat official accountLearn javaThe official account is already available.6WFans, reply:1024The official account is issued for official account.Java quality series, pay attention to us and you will get a lot!

MySQL transaction, this article is enough

Recommended Today

Configure Apache to support PHP in the Apache main configuration file httpd.conf Include custom profile in

In Apache’s main configuration file / conf/ http.conf Add at the bottom Include “D:workspace_phpapache-php.conf” The file path can be any In D: workspace_ Create under PHP file apache- php.conf file Its specific content is [html] view plain copy PHP-Module setup LoadFile “D:/xampp/php/php5ts.dll” LoadModule php5_module “D:/xampp/php/php5apache2_2.dll” <FilesMatch “.php$”> SetHandler application/x-httpd-php </FilesMatch> <FilesMatch “.phps$”> SetHandler application/x-httpd-php-source </FilesMatch> […]