MySQL进阶------事务_mysql事务进阶-优快云博客

本文深入探讨MySQL事务的隔离级别，包括Read Uncommitted、Read Committed、Repeatable Read及Serializable，详细讲解如何设置系统和会话级别的事务隔离，并演示了事务的开始、提交和回滚操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

MySQL进阶------事务

事务隔离级别

Read Uncommitted（读取未提交内容）
Read Committed（读取提交内容）
Repeatable Read（可重读）
Serializable（可串行化）

（具体操作流程介绍其他文章）

查看mysql系统的事务隔离级别：

mysql> SELECT @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| REPEATABLE-READ       |
+-----------------------+
1 row in set (0.00 sec)

查看mysql会话的事务隔离级别：

mysql> SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)

mysql> SELECT @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| REPEATABLE-READ        |
+------------------------+
1 row in set (0.00 sec)

设置系统的事务隔离级别：

mysql> set global transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| READ-COMMITTED        |
+-----------------------+
1 row in set (0.00 sec)

mysql> SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)

设置会话的事务隔离级别

mysql> set session transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| READ-COMMITTED        |
+-----------------------+
1 row in set (0.00 sec)

mysql> SELECT @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set (0.00 sec)

mysql默认是自动提交事务的，查看autocommit变量：

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set (0.01 sec)

设置mysql不自动提交事务：

mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            0 |
+--------------+
1 row in set (0.01 sec)

使用rollback回滚事务

mysql> select * from t1;
Empty set (0.00 sec)

mysql> insert into t1 values (51, 3000);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+----+------+
| a  | b    |
+----+------+
| 51 | 3000 |
+----+------+
1 row in set (0.00 sec)

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

mysql> select * from t1;
Empty set (0.00 sec)

mysql>

使用start transaction;或begin;显示的开启一个事务：

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

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

mysql> insert into t1 values (51, 3000);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+----+------+
| a  | b    |
+----+------+
| 51 | 3000 |
+----+------+
1 row in set (0.00 sec)

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

mysql> select * from t1;
Empty set (0.00 sec)

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

mysql> select * from t1;
Empty set (0.00 sec)