MySQL learning transaction control

What is transaction control

A transaction refers to a series of operations performed as a logical unit of work. These operations either all succeed, or all fail. Transactions ensure that multiple data modifications are processed as a unit.

• In MySQL, only databases or tables using the Innodb storage engine support transactions.
• Transactions are used to maintain the integrity of the database and ensure batches Either all or none of the sql statements are executed
• transactions are used to manage INSERT, UPDATE and DELETE statements

If Zhang San transfers 100 yuan to Li Si at the ATM machine, in the bank's business system, two-step data change operations will be performed:

1. Subtract 100 yuan from Zhang San’s account
2. Add 100 yuan to Li Si’s account

Ask, if the operation What happens if operation 1 is executed successfully but operation 2 fails?

Related free learning recommendations: mysql video tutorial

Four characteristics of transactions

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

• Atomicity: The transaction must be an atomic unit of work. Each operation included in the transaction must be done or none of the operations must be done.
• Consistency: When the transaction is completed, it must be All data remains consistent
• Isolation: transactions run independently. Multiple transactions are isolated from each other and do not interfere with each other. 100% isolation of transactions will sacrifice speed
• Persistence: After the transaction execution is completed, its impact on the system is permanent

MySQL transaction control

Under default , MySQL automatically submits transactions, that is, each SQL statement of INSERT, UPDATE, and DELETE will perform a COMMIT operation immediately after submission. Therefore, to start a transaction, you can use start transaction or begin, or set the value of autocommit to 0.

• Method one:
• Method two:

Example

1. Log in Database, use the student database and view all data tables

USE student;SHOW TABLES;

2. Create the bank_account data table and insert two records, set Zhang San’s balance field value to 1000

CREATE TABLE bank_account(
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(30) COMMENT '姓名',
	balance DECIMAL(18, 2) COMMENT '账户余额');INSERT INTO bank_account(id, name, balance) VALUE(1, '张三', 0);INSERT INTO bank_account(id, name, balance) VALUE(2, '李四', 0);UPDATE bank_account SET balance = balance + 1000 WHERE id = 1;

3. View the default autocommit value

SELECT @@autocommit;

4. View all records in the bank_account data table

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. Again Start transaction control, insert the same two SQL statements, but change commit to 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 data table content again and find the rollback There is no change in the data after that

SELECT * FROM bank_account;

Note: This article is a summary of the blogger's MySQL learning and does not support any commercial use. Please indicate the source when reprinting! If you also have a certain interest and understanding in MySQL learning, you are welcome to communicate with bloggers at any time~

More related free learning recommendations:mysql tutorial (video)

The above is the detailed content of MySQL learning transaction control. For more information, please follow other related articles on the PHP Chinese website!