mysql事务是什么（二）

BEGIN 或 START TRANSACTION:显式地开启一个事务.
COMMIT 或 COMMIT WORK:提交事务,并使已对数据库进行的所有修改变为永久性的.
ROLLBACK 或 ROLLBACKWORK:回滚会结束用户的事务,并撤销正在进行的所有未提交的修改.
SAVEPOINT S1:使用SAVEPOINT允许在事务中创建一个回滚点,一个事务中可以有多个SAVEPOINT;"S1"代表回滚点名称.
ROLLBACK TO[SAVEPOINT] S1:把事务回滚到标记点.

#前期准备
mysql> use stevelu;
Database changed
mysql> create table account (id int,name char(10),money double);
Query OK, 0 rows affected (0.00 sec)
mysql> desc account;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | YES  |     | NULL    |       |
| name  | char(10) | YES  |     | NULL    |       |
| money | double   | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql> insert into account values(1,'zhangsan',1000);
Query OK, 1 row affected (0.00 sec)
mysql> insert into account values(2,'lisi',1000);
Query OK, 1 row affected (0.01 sec)
mysql> select * from account;
+------+----------+-------+
| id   | name     | money |
+------+----------+-------+
|    1 | zhangsan |  1000 |
|    2 | lisi     |  1000 |
+------+----------+-------+
2 rows in set (0.00 sec)
---------------------------------------------------------------------------------------------------
#再开一个终端，分别设置两个当前会话事务隔离级别
mysql> set session transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)
mysql> show session variables like '%isolation%';
+---------------+----------------+
| Variable_name | Value          |
+---------------+----------------+
| tx_isolation  | READ-COMMITTED |
+---------------+----------------+
1 row in set (0.00 sec)
---------------------------------------------------------------------------------------------------

#模拟zhangsan用户存入100
#开启事务
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update account set money=money+100 where name='zhangsan';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select * from account;
+------+----------+-------+
| id   | name     | money |
+------+----------+-------+
|    1 | zhangsan |  1100 |
|    2 | lisi     |  1000 |
+------+----------+-------+
2 rows in set (0.00 sec)
---------------------------------------------------------------------------------------------------
#再开一个终端查看
mysql> use stevelu;
mysql> select * from account;
+------+----------+-------+
| id   | name     | money |
+------+----------+-------+
|    1 | zhangsan |  1000 |
|    2 | lisi     |  1000 |
+------+----------+-------+
2 rows in set (0.00 sec)
---------------------------------------------------------------------------------------------------
#第一个终端操作
#打快照点
mysql> savepoint S1;
Query OK, 0 rows affected (0.01 sec)
#lisi用户减100元
mysql> update account set money=money-100 where name='lisi';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
#打快照点
mysql> savepoint S2;
Query OK, 0 rows affected (0.00 sec)
#查看表记录
mysql> select * from account;
+------+----------+-------+
| id   | name     | money |
+------+----------+-------+
|    1 | zhangsan |  1100 |
|    2 | lisi     |   900 |
+------+----------+-------+
2 rows in set (0.00 sec)
#继续插入记录
mysql> insert into account values(3,'wangwu',1000);
Query OK, 1 row affected (0.01 sec)
mysql> select * from account;
+------+----------+-------+
| id   | name     | money |
+------+----------+-------+
|    1 | zhangsan |  1100 |
|    2 | lisi     |   900 |
|    3 | wangwu   |  1000 |
+------+----------+-------+
3 rows in set (0.00 sec)
#回退到快照点2
mysql> rollback to S2;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from account;
+------+----------+-------+
| id   | name     | money |
+------+----------+-------+
|    1 | zhangsan |  1100 |
|    2 | lisi     |   900 |
+------+----------+-------+
2 rows in set (0.00 sec)

#第一个终端
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update account set money=money+100 where name='zhangsan';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> update account set money=money-100 where name='lisi';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select * from account;
+------+----------+-------+
| id   | name     | money |
+------+----------+-------+
|    1 | zhangsan |  1100 |
|    2 | lisi     |   900 |
+------+----------+-------+
2 rows in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
#第二个终端
mysql> select * from account;
+------+----------+-------+
| id   | name     | money |
+------+----------+-------+
|    1 | zhangsan |  1100 |
|    2 | lisi     |   900 |
+------+----------+-------+
2 rows in set (0.00 sec)

SET AUTOCOMMIT=0; #禁止自动提交
SET AUTOCOMMIT=1; #开启自动提交.Mysq1默认为1
SHOW VARIABLES LIKE'AUTOCOMMIT';  #查看Mysq1中的AUTOCOMMIT值