mysql学习笔记(8)

发布于:2024-10-13 ⋅ 阅读:(41) ⋅ 点赞:(0)

一.事务

0.引入

        在mysql中,事务是一个最小的不可分割的单元,事务能保障一个业务的完整性。

例如银行转账:

a ----> -100

update user set money = money = money - 100 where name = 'a';

b ----> +100

update user set money = money = money + 100 where name = 'b';

实际程序中,如果只有一条程序执行成功,而另一条没有成功,出现数据不一致。

update user set money = money = money - 100 where name = 'a';
update user set money = money = money + 100 where name = 'b';

多条sql程序,有同事执行成功的需求。要么就同时失败。

mysql中如何控制事务?

1.mysql是默认开启事务的(自动提交)。

select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+

默认事务开启的作用是什么?

当我们去执行一个说起来语句时,效果会立即体现出来,且不能回滚。

create database bank;
use bank;
create table user(
    id int primary key,
    name varchar(20),
    money int
);
insert into user value(1,'张三',2000);
mysql> select * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | 张三 |  2000 |
+----+------+-------+

回滚(rollback):撤销sql语句执行效果;

rollback;
mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | 张三 |  2000 |
+----+------+-------+
1 row in set (0.00 sec)

那么该如何撤销呢?

设置mysql自动提交为FALSE:

set autocommit = 0;

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

select @@autocommit;

+--------------+
| @@autocommit |
+--------------+
|            0 |
+--------------+
1 row in set (0.00 sec)

上面的操作关闭了mysql的自动提交(commit);

测试一下:

mysql> insert into user value(2,'李四',6000);
Query OK, 1 row affected (0.00 sec)

mysql> select * from user;
+----+------+-------+  ##这是虚拟的表,真是的表没有提交
| id | name | money |
+----+------+-------+
|  1 | 张三 |  2000 |
|  2 | 李四 |  6000 |
+----+------+-------+
2 rows in set (0.00 sec)

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

mysql> select * from user;
+----+------+-------+##真是的表还未提交。
| id | name | money |
+----+------+-------+
|  1 | 张三 |  2000 |
+----+------+-------+
1 row in set (0.00 sec)

 如何让他真是的插入数据呢?

##再一次插入数据
mysql> insert into user value(2,'李四',6000); 
Query OK, 1 row affected (0.00 sec)
##手动插入数据
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
##无法撤销
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | 张三 |  2000 |
|  2 | 李四 |  6000 |
+----+------+-------+
2 rows in set (0.00 sec)
小结

        自动提交;@@autocommmit = 1

        手动提交;commit;

        事务回滚;rollback;

如果说这时候转账,

update user set money = money - 100 where name = '张三';
update user set money = money + 100 where name = '李四';
mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | 张三 |  1900 |
|  2 | 李四 |  6100 |
+----+------+-------+
2 rows in set (0.00 sec)

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

mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | 张三 |  2000 |
|  2 | 李四 |  6000 |
+----+------+-------+
2 rows in set (0.00 sec)

2.手动开启事务

set autocommit = 1;

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

除了上述方法之外,begin; 或者 start transaction;都可以手动开启一个事务。

mysql> select *from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | 张三 |  2000 |
|  2 | 李四 |  6000 |
+----+------+-------+
mysql> update user set money = money - 100 where name = '张三';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update user set money = money + 100 where name = '李四';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | 张三 |  1900 |
|  2 | 李四 |  6100 |
+----+------+-------+
2 rows in set (0.00 sec)

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

mysql>  select * from user;
+----+------+-------+## rollback是没有效果的
| id | name | money |
+----+------+-------+
|  1 | 张三 |  1900 |
|  2 | 李四 |  6100 |
+----+------+-------+
2 rows in set (0.00 sec)
 手动开启事务(1)
mysql> begin ;
Query OK, 0 rows affected (0.00 sec)

mysql> update user set money = money - 100 where name = '张三';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update user set money = money + 100 where name = '李四';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | 张三 |  1800 |
|  2 | 李四 |  6200 |
+----+------+-------+
2 rows in set (0.00 sec)

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

mysql> select * from user;
+----+------+-------+##成功回滚了
| id | name | money |
+----+------+-------+
|  1 | 张三 |  1900 |
|  2 | 李四 |  6100 |
+----+------+-------+
2 rows in set (0.00 sec)
手动开启事务(2)
mysql> start transaction;

mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | 张三 |  1900 |
|  2 | 李四 |  6100 |
+----+------+-------+
2 rows in set (0.00 sec)

mysql> update user set money = money - 100 where name = '张三';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update user set money = money + 100 where name = '李四';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | 张三 |  1800 |
|  2 | 李四 |  6200 |
+----+------+-------+
2 rows in set (0.00 sec)

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

mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | 张三 |  1900 |
|  2 | 李四 |  6100 |
+----+------+-------+
2 rows in set (0.00 sec)

事务开启之后,一旦commit调,就不能回滚。

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

mysql>  select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | 张三 |  1900 |
|  2 | 李四 |  6100 |
+----+------+-------+
2 rows in set (0.00 sec)

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

mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | 张三 |  1900 |
|  2 | 李四 |  6100 |
+----+------+-------+
2 rows in set (0.00 sec)

3.事物的特征

A.原子性:事务是最小的单位,不可分割;

C.一致性:事务要求,同一事物中的sql语句,必须保证同时成功或同时失败;

I . 隔离性:事务a和事务b之间是有隔离性的;

D.持久性:事务一旦结束(commit; rollback),就不可能返回。

4.事物的开启

        ①事务默认提交: set autocommit = 0;

        ②begin;

        ③start transection;

5.事务手动提交

        commit;

6.事务手动回滚

        rollback;

7.事物的隔离性