Mysql 命令行控制事务 小鱼儿 2021-06-24 14:34 405阅读 0赞 1、创建表 **CREATE** **TABLE** \`t1\` ( \`a\` **int**(11) NOT NULL, \`b\` **int**(11) **DEFAULT** NULL, **PRIMARY** **KEY** (\`a\`) ) ENGINE=InnoDB **DEFAULT** CHARSET=latin1; 2、查看mysql系统级别的事务隔离级别: mysql> **SELECT** @@**global**.tx\_isolation; \+\-----------------------+ | @@**global**.tx\_isolation | \+\-----------------------+ | **REPEATABLE**\-**READ** | \+\-----------------------+ 1 row in **set** (0.00 sec) 3、查看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) 4、设置系统的事务隔离级别: 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) 5、设置会话的事务隔离级别: 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) 6、mysql默认是自动提交事务的,查看autocommit变量: mysql> **select** @@autocommit; \+\--------------+ | @@autocommit | \+\--------------+ | 1 | \+\--------------+ 1 row in **set** (0.01 sec) 7、设置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) 8、使用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) 9、使用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) 10、使用savepoint在事务中创建一个保存点(可以在一个事务中创建多个保存点) 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> savepoint tx1; Query OK, 0 **rows** affected (0.00 sec) mysql> **insert** **into** t1 **values** (52, 3000); Query OK, 1 row affected (0.00 sec) mysql> **select** \* **from** t1; \+\----+------+ | a | b | \+\----+------+ | 51 | 3000 | | 52 | 3000 | \+\----+------+ 2 **rows** in **set** (0.00 sec) mysql> **rollback** **to** tx1; Query OK, 0 **rows** affected (0.00 sec) mysql> **select** \* **from** t1; \+\----+------+ | a | b | \+\----+------+ | 51 | 3000 | \+\----+------+ 1 row in **set** (0.00 sec) mysql> **commit**; Query OK, 0 **rows** affected (0.04 sec)
还没有评论,来说两句吧...