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

This article mainly introduces the example analysis of transaction control pair in MySQL, which is very detailed and has certain reference value. Friends who are interested must read it!

What is transaction control?

A transaction is a series of operations performed as a logical unit of work that either succeed or fail. Transactions ensure that multiple data modifications are handled as a single unit.

In MySQL, only databases or tables that use the Innodb storage engine support things

Transactions are used to maintain the integrity of the database, ensuring that batches of sql statements are either executed or not executed

Transactions are used to manage INSERT, UPDATE, and DELETE statements

If Zhang San transfers 100 yuan to Li Si on the ATM, in the bank's business system, he will mainly perform two steps of data change operation:

Subtract 100 yuan from Zhang San's account

Add 100 yuan to Li Si's account

I would like to ask, what happens if operation 1 succeeds and operation 2 fails?

Four characteristics of transactions

If a database supports transactions, then the database must have four features of ACID, namely, Atomicity (atomicity), Consistency (consistency), Isolation (isolation), and Durability (persistence).

Atomicity: the transaction must be an atomic unit of work, and the operations contained in the transaction are either done or not done.

Consistency: when the transaction is completed, all data must be kept in a consistent state

Isolation: transactions run independently. Multiple transactions are isolated from each other and do not interfere with each other. 100% isolation of transactions at the expense of speed

Persistence: after the transaction is executed, its impact on the system is permanent

Transaction Control of MySQL

By default, MySQL automatically commits transactions, that is, the COMMIT operation is performed immediately after each SQL statement of INSERT, UPDATE, and DELETE is committed. Therefore, to start a transaction, you can use start transaction or begin, or set the value of autocommit to 0. 0.

Method 1:

Method 2:

Examples

Log in to the database, use the student database and view all the data tables

USE student;SHOW TABLES

two。 Create a bank_account data table and insert two records to set the balance field value of Zhang San + 1000

CREATE TABLE bank_account (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR (30) COMMENT 'name', balance DECIMAL (18,2) COMMENT 'account balance'); INSERT INTO bank_account (id, name, balance) VALUE (1, 'Zhang San', 0); INSERT INTO bank_account (id, name, balance) VALUE (2,'Li Si', 0); UPDATE bank_account SET balance = balance + 1000 WHERE id = 1

3. View the default autocompare valu

SELECT @ @ autocommit

4. View all records of the bank_account data sheet

SELECT * FROM bank_account

5. Start transaction control and execute two SQL statements

START TRANSACTION;UPDATE bank_account SET balance = balance-100 WHERE id = 1 * * update bank_account SET balance = balance + 100 WHERE id = 2 * * commit

6. View the contents of the data table at this time

SELECT * FROM bank_account

7. Start transaction control again, insert two same SQL statements, but change commit (commit) to rollback (rollback)

START TRANSACTION;UPDATE bank_account SET balance = balance-100 WHERE id = 1 * * update bank_account SET balance = balance + 100 WHERE id = 2 * * Rollback

8. Check the contents of the data table again and find that there is no change in the data after the rollback

SELECT * FROM bank_account

Note: this article is a summary of the MySQL study of the blogger and does not support any commercial use. Please indicate the source of the reprint. If you also have some interest and understanding in MySQL learning, you are welcome to contact the blogger at any time.

The above is all the contents of the article "sample Analysis of transaction Control pairs in MySQL". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

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.