进阶篇——数据库的事务

数据库的事务

数据库事务的概念

事务的ACID特点

事务不一致产生的结果

事务的隔离级别

mysql> show global variables like '%isolation%'
    -> ;
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
| tx_isolation          | REPEATABLE-READ |
+-----------------------+-----------------+
2 rows in set (0.01 sec)

 

• 查询回话事务隔离级别

  mysql> show session variables like '%isolation%'
      -> ;
  +-----------------------+-----------------+
  | Variable_name         | Value           |
  +-----------------------+-----------------+
  | transaction_isolation | REPEATABLE-READ |
  | tx_isolation          | REPEATABLE-READ |
  +-----------------------+-----------------+
  2 rows in set (0.00 sec)

  

•  

  mysql> set global transaction isolation level read committed;
  Query OK, 0 rows affected (0.00 sec)
  mysql> show global variables like '%isolation%';
  +-----------------------+----------------+
  | Variable_name         | Value          |
  +-----------------------+----------------+
  | transaction_isolation | READ-COMMITTED |
  | tx_isolation          | READ-COMMITTED |
  +-----------------------+----------------+
  2 rows in set (0.00 sec)

事务控制语句

mysql> begin;		//开启一个事务
Query OK, 0 rows affected (0.00 sec)


mysql> select * from qq;
+----+----------+-------+--------------+--------+
| id | name     | score | address_new  | hobby  |
+----+----------+-------+--------------+--------+
|  1 | zhangsan | 70.00 | 地址不详     | NULL   |
|  2 | wangwu   | 70.00 | 上海         | 篮球   |
+----+----------+-------+--------------+--------+
2 rows in set (0.00 sec)

mysql> update qq set score= score + 20 where name='zhangsan';		//修改某一字段中的记录数值
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from qq;
+----+----------+-------+--------------+--------+
| id | name     | score | address_new  | hobby  |
+----+----------+-------+--------------+--------+
|  1 | zhangsan | 90.00 | 地址不详     | NULL   |
|  2 | wangwu   | 70.00 | 上海         | 篮球   |
+----+----------+-------+--------------+--------+
2 rows in set (0.00 sec)

mysql> commit;		//提交事务，将修改变为永久
Query OK, 0 rows affected (0.00 sec)

 

mysql> begin;		//开始事务
Query OK, 0 rows affected (0.00 sec)

mysql> update qq set score= score - 40 where name='zhangsan';		//修改记录
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from qq;
+----+----------+-------+--------------+--------+
| id | name     | score | address_new  | hobby  |
+----+----------+-------+--------------+--------+
|  1 | zhangsan | 50.00 | 地址不详     | NULL   |
|  2 | wangwu   | 70.00 | 上海         | 篮球   |
+----+----------+-------+--------------+--------+
2 rows in set (0.00 sec)

mysql> rollback;		//使用回滚
Query OK, 0 rows affected (0.00 sec)

mysql> quit		
Bye
[root@mysql ~]# mysql -uroot -pabc123	//退出数据库重新进入

mysql> use blbl
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from qq;	//数值变回来了
+----+----------+-------+--------------+--------+
| id | name     | score | address_new  | hobby  |
+----+----------+-------+--------------+--------+
|  1 | zhangsan | 90.00 | 地址不详     | NULL   |
|  2 | wangwu   | 70.00 | 上海         | 篮球   |
+----+----------+-------+--------------+--------+
2 rows in set (0.00 sec)

 

 

mysql> begin;		//开始事务
Query OK, 0 rows affected (0.00 sec)

mysql> update qq set score= score + 10 where name='zhangsan';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> savepoint s1;	//设置一个回滚节点
Query OK, 0 rows affected (0.00 sec)

mysql> update qq set score= score + 10 where name='wangwu';
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 qq;
+----+----------+--------+--------------+--------+
| id | name     | score  | address_new  | hobby  |
+----+----------+--------+--------------+--------+
|  1 | zhangsan | 100.00 | 地址不详     | NULL   |
|  2 | wangwu   |  80.00 | 上海         | 篮球   |
+----+----------+--------+--------------+--------+
2 rows in set (0.00 sec)

mysql> insert into qq values(3,'lisi',40,'南京','play');	//在插入一条记录
Query OK, 1 row affected (0.00 sec)

mysql> select * from qq;
+----+----------+--------+--------------+--------+
| id | name     | score  | address_new  | hobby  |
+----+----------+--------+--------------+--------+
|  1 | zhangsan | 100.00 | 地址不详     | NULL   |
|  2 | wangwu   |  80.00 | 上海         | 篮球   |
|  3 | lisi     |  40.00 | 南京         | play   |
+----+----------+--------+--------------+--------+
3 rows in set (0.00 sec)

mysql> rollback to s1;	//回滚到第一个节点
Query OK, 0 rows affected (0.00 sec)

mysql> select * from qq;
+----+----------+--------+--------------+--------+
| id | name     | score  | address_new  | hobby  |
+----+----------+--------+--------------+--------+
|  1 | zhangsan | 100.00 | 地址不详     | NULL   |
|  2 | wangwu   |  70.00 | 上海         | 篮球   |
+----+----------+--------+--------------+--------+
2 rows in set (0.00 sec)

 

mysql> set autocommit=0;	//设置禁止自动提交，0是禁止，1是开启自动提交，1是默认
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

mysql> update qq set score= score + 20 where name='zhangsan';	//插入一条记录
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from qq;
+----+----------+--------+--------------+--------+
| id | name     | score  | address_new  | hobby  |
+----+----------+--------+--------------+--------+
|  1 | zhangsan | 120.00 | 地址不详     | NULL   |
|  2 | wangwu   |  70.00 | 上海         | 篮球   |
+----+----------+--------+--------------+--------+
2 rows in set (0.00 sec)

mysql> quit
Bye
[root@mysql ~]# mysql -uroot -pabc123	//退出再重进

mysql> use blbl
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------+
| Tables_in_blbl |
+----------------+
| qq             |
| ss             |
| yy             |
+----------------+
3 rows in set (0.00 sec)

mysql> select * from qq;	//发现数据没有被改变，说明没有自动提交
+----+----------+-------+--------------+--------+
| id | name     | score | address_new  | hobby  |
+----+----------+-------+--------------+--------+
|  1 | zhangsan | 90.00 | 地址不详     | NULL   |
|  2 | wangwu   | 70.00 | 上海         | 篮球   |
+----+----------+-------+--------------+--------+
2 rows in set (0.00 sec)