【13】事务

1、什么叫做事务?

mysql中,事务其实是一个最小的不可分割的工作单元。事务能够保证一个业务的完整性。

eg.银行转账:

a要向b转出100元:a-->-100:

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

b-->+100:

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

实际的程序中,如果上述只有一条语句成功了,而另外一条没有执行成功的话,就会出现数据前后不一致。

因此,多条sql语句,可能会有同时执行成功的要求,要么就同时失败。

2、mysql中如何控制事务?(commit、rollback)

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

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

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

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

mysql> create database bank;
mysql> use bank;
mysql> create table user(
    -> id int primary key,
    -> name varchar(20),
    -> money int
    -> );
mysql> INSERT INTO user VALUES(1,‘a‘,1000);

事务回滚:撤销sql语句执行效果

mysql> rollback;
mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
+----+------+-------+

尽管使用rollback,user的数据还在,没有撤回。

解决:设置mysql自动提交为false:

mysql> set autocommit=0;
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            0 |
+--------------+

结果:

mysql> INSERT INTO user VALUES(2,‘b‘,1000);
mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
|  2 | b    |  1000 |
+----+------+-------+
mysql> rollback;
mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
+----+------+-------+

再一次插入数据:

手动提交数据(commit),这时候再撤销是行不通的。(持久性产生效果,rollback无用了

mysql> INSERT INTO user VALUES(2,‘b‘,1000);
mysql> commit;
mysql> rollback;
mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
|  2 | b    |  1000 |
+----+------+-------+

自动提交

@@autocommit=1

手动提交

commit;

事务回滚

rollback;

转账:

mysql> update user set money=money-100 where name=‘a‘;
mysql> update user set money=money+100 where name=‘b‘;
mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |   900 |
|  2 | b    |  1100 |
+----+------+-------+
mysql> rollback;
mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
|  2 | b    |  1000 |
+----+------+-------+

事务为我们提供了一个可以反悔的机会。

3、手动开启事务:begin 和 start transaction

还原自动提交设置:

mysql> set autocommit=1;
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+

begin; 或者 start transaction; 都可以帮我们手动开启一个事务。

mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
|  2 | b    |  1000 |
+----+------+-------+

转账后:

mysql> update user set money=money-100 where name=‘a‘;
mysql> update user set money=money+100 where name=‘b‘;
mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |   900 |
|  2 | b    |  1100 |
+----+------+-------+

此时,事务回滚没有被撤销,rollback无用(因为已设置成自动提交):

mysql> rollback;
mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |   900 |
|  2 | b    |  1100 |
+----+------+-------+

再次转账:

mysql> begin;
mysql> update user set money=money-100 where name=‘a‘;
mysql> update user set money=money+100 where name=‘b‘;
mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |   800 |
|  2 | b    |  1200 |
+----+------+-------+

rollback之后:

mysql> rollback;
mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |   900 |
|  2 | b    |  1100 |
+----+------+-------+

使用begin(start transaction)手动开启事务,事务可以回滚。

mysql> start transaction;
mysql> update user set money=money-100 where name=‘a‘;
mysql> update user set money=money+100 where name=‘b‘;
mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |   800 |
|  2 | b    |  1200 |
+----+------+-------+
mysql> rollback;
mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |   900 |
|  2 | b    |  1100 |
+----+------+-------+

事务开启之后,一旦commit提交,就不可以回滚(当前这个事务在提交的时候就结束了)。

mysql> commit;
mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |   900 |
|  2 | b    |  1100 |
+----+------+-------+
mysql> rollback;
mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |   900 |
|  2 | b    |  1100 |
+----+------+-------+