MySQL transaction concepts and usage in-depth explanation

This article uses examples to explain the concept and usage of MySQL transactions. Share with you for your reference, the details are as follows:

The concept of affairs

A MySQL transaction is one or more database operations, which are either all executed successfully or all failed and rolled back.

Transactions are implemented through transaction logs, which include redo logs and undo logs.

The state of the transaction

active

When the database operation corresponding to the transaction is in progress, we say that the transaction is in an active state.

partially committed

When the last operation in a transaction is executed, but the effects are not flushed to disk because the operations are all performed in memory, we say that the transaction is in a partially committed state.

failed

When a transaction is in an active or partially committed state, it may encounter some errors (database errors, operating system errors, or direct power outages, etc.) and cannot continue to execute, or the execution of the current transaction is stopped manually. We say that the transaction is in a failed state.

aborted

If a transaction fails halfway through execution, we undo the impact of the failed transaction on the current database. We call this undo process a rollback.

When the rollback operation is completed, that is, the database is restored to the state before the transaction was executed, we say that the transaction is in an aborted state.

committed

When a transaction in a partially committed state has synchronized all modified data to disk, we can say that the transaction is in a committed state.

As you can see from the figure, the life cycle of a transaction is considered to be over only when the transaction is in a committed or aborted state. For a committed transaction, the changes made by the transaction to the database will take effect permanently. For a transaction in an aborted state, all changes made by the transaction to the database will be rolled back to the state before the transaction was executed.

The role of transactions

Transactions are primarily intended to ensure data consistency in complex database operations, especially when data is accessed concurrently.
MySQL transactions are mainly used to process data with large operation volumes and high complexity.

Characteristics of transactions

Atomicity (also known as indivisibility)

The data operations of a transaction are either all executed successfully or all failed and rolled back to the state before execution, as if the transaction had never been executed.

Isolation (also known as independence)

Multiple transactions are isolated from each other and do not affect each other. The database allows multiple concurrent transactions to read, write, and modify its data at the same time. Isolation can prevent data inconsistency due to cross-execution when multiple transactions are executed concurrently.

Four isolation states:
1. Read uncommitted
2. Read committed
3. Repeatable read
4. Serializable

Consistency

Before and after the transaction operation, the data remains in the same state and the integrity of the database is not compromised.
Atomicity and isolation have a crucial impact on consistency.

Durability

When the transaction operation is completed, the data will be flushed to disk for permanent storage and will not be lost even in the event of a system failure.

Transaction syntax

data

#Create a data table:
create table account(
  -> id int(10) auto_increment,
  -> name varchar(30),
  -> balance int(10),
  ->primary key (id));
#Insert data:
insert into account(name,balance) values('老王媳妇',100),('老王',10);

mysql> select * from account;
+----+--------------+---------+
| id | name | balance |
+----+--------------+---------+
| 1 | Lao Wang's Wife | 100 |
| 2 | Lao Wang | 10 |
+----+--------------+---------+

Lao Wang's wife has 100 yuan in her WeChat account, which is used to give Lao Wang pocket money every month. The better his performance, the more he gets. Lao Wang also has his own little piggy bank. He has already saved up 10 yuan for pocket money, hahaha.

begin

Transaction start mode 1

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> Transaction Operation SQL......

start transaction [modifier]

Modifiers:
1. read only //read only 2. read write //read and write by default 3. WITH CONSISTENT SNAPSHOT //consistent read

Transaction start mode 2

mysql> start transaction read only;
Query OK, 0 rows affected (0.00 sec)
mysql> Transaction Operation SQL......

#If read only is set, an error will be reported if the data is modified:

mysql> start transaction read only;
Query OK, 0 rows affected (0.00 sec)

mysql> update account set balance=banlance+30 where id = 2;
ERROR 1792 (25006): Cannot execute statement in a READ ONLY transaction.

commit

The transaction is committed and flushed to disk if successful

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

rollback

The transaction is rolled back and returns to the state before the transaction operation.

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

It should be emphasized here that the ROLLBACK statement is only used by our programmers to manually roll back a transaction. If the transaction encounters some errors during execution and cannot continue to execute, the transaction itself will be automatically rolled back.

Complete submission example

In January, Lao Wang performed very well, and his wife gave him 20 yuan in pocket money as a reward.

Execution steps:
1. Read data from Lao Wang’s wife’s account
2. Deduct 20 yuan from Lao Wang’s wife’s account
3. Read data from Lao Wang's account
4. Add 20 yuan to Lao Wang’s account
5. Execution submission is successful
6. At this time, Wang's wife only has 80 yuan in her account, while Wang has 30 yuan in his account. Wang is very happy.

mysql> begin;
Query OK, 0 rows affected (0.01 sec)

mysql> update account set balance=balance-20 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> update account set balance=balance+20 where id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

#Account balance:
mysql> select * from account;
+----+--------------+---------+
| id | name | balance |
+----+--------------+---------+
| 1 | Lao Wang's Wife | 80 |
| 2 | Lao Wang | 30 |
+----+--------------+---------+

Complete rollback example

In February, Lao Wang had been performing very well, insisting on doing housework and walking the dog. Lao Wang's wife wanted to give him 25 yuan in pocket money, but Lao Wang just couldn't stand the praise. When Lao Wang's wife was transferring pocket money to Lao Wang, she suddenly saw a WeChat message from a little girl on Lao Wang's phone on the table: Dear Brother Wang... Lao Wang's wife was very angry, and she withdrew the transfer in a rage and canceled the pocket money for this month.

Execution steps:
1. Read data from Lao Wang’s wife’s account
2. Deduct 25 yuan from Lao Wang's wife's account
3. Read data from Lao Wang's account
4. Add 25 yuan to Lao Wang’s account
5. At this time, Lao Wang's wife withdraws the previous operation
6. At this point, the account balances of Mr. Wang and his wife remain the same as before the operation.

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update account set balance=balance-25 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> update account set balance=balance+25 where id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

#Account balance:
mysql> select * from account;
+----+--------------+---------+
| id | name | balance |
+----+--------------+---------+
| 1 | Lao Wang's Wife | 80 |
| 2 | Lao Wang | 30 |
+----+--------------+---------+

Transaction-supported storage engines

1. InnoDB
2. NDB

For storage engines that do not support transactions, such as MyISAM, the transactions will not take effect and the SQL statements will be automatically committed. Therefore, rollback is invalid for storage engines that do not support transactions.

create table tb1(
  -> id int(10) auto_increment,
  -> name varchar(30),
  -> primary key (id)
  ->)engine=myisam charset=utf8mb4;
  
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into tb1(name) values('Tom');
Query OK, 1 row affected (0.01 sec)

mysql> select * from tb1;
+----+------+
| id | name |
+----+------+
| 1 | Tom |
+----+------+
1 row in set (0.00 sec)

mysql> rollback; //Rollback is invalid Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select * from tb1;
+----+------+
| id | name |
+----+------+
| 1 | Tom |
+----+------+
1 row in set (0.00 sec)

Setting and viewing transactions

# Check the transaction opening status:
mysql> SHOW VARIABLES LIKE 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+

By default, transactions are automatically committed, and each SQL statement is automatically committed.

If you need to operate a transaction at this time, you need to explicitly start (begin or start transaction) and commit (commit) or rollback (rollback).

If it is set to OFF, the transaction will not be actually executed until a commit or rollback operation is performed.

Turn off auto-submit mode

The first

Explicitly start a transaction using the START TRANSACTION or BEGIN statement.

The second

Set the value of the system variable autocommit to OFF.

SET autocommit = OFF;

Implicit submission

When we start a transaction using the START TRANSACTION or BEGIN statement, or set the value of the system variable autocommit to OFF, the transaction will not be automatically committed. However, if we enter certain statements, the transaction will be quietly committed, just like we entered the COMMIT statement. This situation where the transaction is committed due to some special statements is called implicit commit.

Data definition language (DDL) is used to define or modify database objects.

The so-called database objects refer to databases, tables, views, stored procedures, and so on. When we use CREATE, ALTER, DROP and other statements to modify these so-called database objects, the transaction to which the previous statement belongs will be implicitly committed.

BEGIN;
SELECT ... # A statement in a transaction UPDATE ... # A statement in a transaction ... # Other statements in a transaction CREATE TABLE ... # This statement implicitly commits the transaction to which the previous statement belongs

Implicitly use or modify tables in MySQL database

Implicitly use or modify tables in the mysql database.

When we use statements such as ALTER USER, CREATE USER, DROP USER, GRANT, RENAME USER, REVOKE, SET PASSWORD, etc., the transaction to which the previous statement belongs will also be implicitly committed.

Transaction control or locking statements

Transaction control or locking statements.

When we use the START TRANSACTION or BEGIN statement to start another transaction before a transaction is committed or rolled back, the previous transaction will be implicitly committed.

BEGIN;
SELECT ... # A statement in a transaction UPDATE ... # A statement in a transaction ... # Other statements in a transaction BEGIN; # This statement implicitly commits the transaction to which the previous statement belongs

Or if the current value of the autocommit system variable is OFF and we manually turn it to ON, the transaction to which the previous statement belongs will also be implicitly committed.

Alternatively, using locking statements such as LOCK TABLES and UNLOCK TABLES will implicitly commit the transaction to which the previous statement belongs.

Statement to load data

For example, when we use the LOAD DATA statement to import data into the database in batches, the transaction to which the previous statement belongs will also be implicitly committed.

Some statements about MySQL replication

When using statements such as START SLAVE, STOP SLAVE, RESET SLAVE, and CHANGE MASTER TO, the transaction to which the previous statement belongs will also be implicitly committed.

Some other statements

Using statements such as ANALYZE TABLE, CACHE INDEX, CHECK TABLE, FLUSH, LOAD INDEX INTO CACHE, OPTIMIZE TABLE, REPAIR TABLE, and RESET will also implicitly commit the transaction to which the previous statement belongs.

Transaction savepoints

concept

By putting a few dots in the database statement corresponding to the transaction, we can specify which point to roll to when calling the ROLLBACK statement instead of returning to the original origin.

With transaction save points, when we perform complex transaction operations, we don’t have to worry about rolling back to the original state if an error occurs, just like going back to the time before liberation overnight.

Use Syntax

1. SAVEPOINT savepoint name; //mark savepoint
2. ROLLBACK TO [SAVEPOINT] savepoint name; //Roll back to a savepoint
3. RELEASE SAVEPOINT savepoint name; // delete

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update account set balance=balance-20 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> savepoint action1;
Query OK, 0 rows affected (0.02 sec)

mysql> select * from account;
+----+--------------+---------+
| id | name | balance |
+----+--------------+---------+
| 1 | Lao Wang's Wife | 60 |
| 2 | Lao Wang | 30 |
+----+--------------+---------+

mysql> update account set balance=balance+30 where id = 2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> rollback to action1; //Roll back to action1 savepoint Query OK, 0 rows affected (0.00 sec)

mysql> select * from account;
+----+--------------+---------+
| id | name | balance |
+----+--------------+---------+
| 1 | Lao Wang's Wife | 60 |
| 2 | Lao Wang | 30 |
+----+--------------+---------+

Readers who are interested in more MySQL-related content can check out the following topics on this site: "Summary of MySQL transaction operation skills", "Summary of MySQL index operation skills", "Summary of MySQL commonly used functions", "Summary of MySQL log operation skills", "Summary of MySQL stored procedure skills" and "Summary of MySQL database lock-related skills".

I hope this article will be helpful to everyone's MySQL database design.