Shulou(Shulou.com)06/02 Report--

This article mainly explains "what is the meaning of Mysql transaction". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Now let the editor take you to learn "what is the meaning of Mysql transaction"?

The previous article focused on the concept of SQL lock, which talked about the concept of transaction. In fact, most developers are no stranger to transactions. The concept of transaction is that a set of MySQL statements form an execution unit. If an exception occurs in the execution of a SQL statement in the unit, you need to roll back the entire transaction to an unmodified state. The committed data represents the successful execution of the transaction only if all SQL statements in the unit are executed successfully. In fact, in previous articles, we all know that Mysql supports multiple storage engines, in fact, it is precisely because the MyISAM engine does not support transactions to be replaced by InnoDB. So this article is mainly aimed at the InnoDB engine to understand the concept of transaction in detail.

Transaction characteristics

Atomicity: a transaction is an atomic operation unit in which all or none of the changes to the data are performed.

Consistency: the processing included in the transaction should meet the constraints set in advance by the database, such as primary key constraints or NOT NULL constraints.

Isolation: the intermediate state during a transaction is not visible to the outside.

Persistence: after the transaction is completed, its modification of the data is permanent.

Four isolation levels of isolation

The four transaction isolation levels supported by the InnoDB engine are read uncommitted, read committed, repeatable read, and serial read.

Read unsubmitted: dirty reading is allowed, and dirty data not submitted in other session can be read.

Read submitted: cannot read data that has not been submitted by other session. It can only be read if other session data has been submitted, which is not repeated.

Repeatable: it can be read repeatedly at this level. The InnoDB engine uses repeatable readings by default and does not allow reading dirty data that has not been submitted yet, but there may be phantom readings unique to InnoDB.

Serial reading: at this level, the highest degree of isolation, transactions can only be executed one after another serial, can not be executed concurrently. Table-level shared locks are required for each serial read, and read and write operations are blocked.

Illusory reading

The transaction is inserting a record that has been checked and does not exist, but the result of the insertion is that the data already exists, and the previous check operation is like a phantom. Mysql defaults to a repeatable read level, so only phantom readings are possible.

Set transaction isolation level

You can configure the transaction-isolation attribute under [mysqld] in the my.ini file. The four values of isolation are READ-UNCOMMITTED, READ-COMMITIED, REPEATABLE-READ and SERIALIZABLE, corresponding to four isolation levels: read uncommitted, read committed, repeatable read and serial read.

Transaction classification

There are generally two types of transactions: implicit transactions and display transactions. In Mysql, transactions commit automatically by default, so each DML statement is actually a transaction process. Implicit transaction: without opening and ending flags, SQL statements are automatically committed after execution by default. For example, INSERT, UPDATE, and DELETE statements that we often use are implicit transactions. Show transaction: need to show on and off, and then perform a series of actions, and finally commit the transaction to release the connection if all operations are performed successfully, and if there is an exception, all operations in the transaction are rolled back.

The transaction uses the steps to open the transaction: turn off autocommit, and then open the transaction. Transaction operation: a series of DML statements are executed. Transaction end: either commit the transaction or roll back the transaction operation depending on whether the transaction operation is successful or not.

Next, we simply use the transaction to transfer money from user A to user B.

Create a user table and insert two pieces of user data:

Configure database connection information:

Next, take a database connection from the connection pool and make a database connection:

If the database connection is successful, the transaction is started using the connection object:

Then check whether the transfer account and the account to be transferred exist, and perform a series of operations such as transfer. Here, due to the devil callback, I use the async library to make a chain call:

Finally, the transaction commit or transaction rollback is performed according to the success or failure of the transaction operation, and the database connection is released:

Then you can test the success of the transfer and find that the transfer operation is successful.

If you test the failed transfer operation again, you can find that the deduction of A points and the increase of B points have all been rolled back, so the credit balances of An and B have not changed.

At this point, I believe you have a deeper understanding of what "Mysql transaction means". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.