mysql--事务

事务

  事务用于将某些操作的多个SQL作为原子性操作，也就是这些sql语句要么同时成功，要么都不成功，一旦有某一个出现错误，即可回滚到原来的状态，从而保证数据库数据完整性。
1.mysql事物属性
事务是由一组SQL语句组成的逻辑处理单元，事物具有ACID属性。
四大特性
原子性：事务是一个原子操作单元。在当时原子是不可分割的最小元素，其对数据的修改，要么全部成功，要么全部都不成功。
一致性：事务开始到结束的时间段内，数据都必须保持一致状态。
隔离性：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的‘独立’环境执行。
持久性：事务完成后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。
2.事务常见问题

更新丢失： 原因：当多个事务选择同一行操作，并且都是基于最初选定的值，由于每个事务都不知道其他事务的存在，就会发生更新覆盖的问题。类比github提交冲突。
脏读： 原因：事务A读取了事务B已经修改但尚未提交的数据。若事务B回滚数据，事务A的数据存在不一致性的问题。
不可重复读： 原因：事务A第一次读取最初数据，第二次读取事务B已经提交的修改或删除数据。导致两次读取数据不一致。不符合事务的隔离性。
幻读： 原因：事务A根据相同条件第二次查询到事务B提交的新增数据，两次数据结果集不一致。不符合事务的隔离性。
3.事务的隔离级别

  数据库的事务隔离越严格，并发副作用越小，但付出的代价也就越大。这是因为事务隔离实质上是将事务在一定程度上"串行"进行，这显然与"并发"是矛盾的。根据自己的业务逻辑，权衡能接受的最大副作用。从而平衡了"隔离" 和 "并发"的问题。MySQL默认隔离级别是可重复读。

  脏读，不可重复读，幻读，其实都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决。

+------------------------------+---------------------+--------------+--------------+--------------+
| 隔离级别                      | 读数据一致性         | 脏读         | 不可重复 读   | 幻读         |
+------------------------------+---------------------+--------------+--------------+--------------+
| 未提交读(Read uncommitted)    | 最低级别            | 是            | 是           | 是           | 
+------------------------------+---------------------+--------------+--------------+--------------+
| 已提交读(Read committed)      | 语句级              | 否           | 是           | 是           |
+------------------------------+---------------------+--------------+--------------+--------------+
| 可重复读(Repeatable read)     | 事务级              | 否           | 否           | 是           |
+------------------------------+---------------------+--------------+--------------+--------------+
| 可序列化(Serializable)        | 最高级别，事务级     | 否           | 否           | 否           |
+------------------------------+---------------------+--------------+--------------+--------------+

查看当前数据库的事务隔离级别：show variables like ‘tx_isolation’;

mysql> show variables like 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+

4.事务级别的设置

1.未提交读（READ UNCOMMITED） 解决的障碍：无； 引入的问题：脏读
    set SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

2.已提交读 （READ COMMITED） 解决的障碍：脏读； 引入的问题：不可重复读
    set SESSION TRANSACTION ISOLATION LEVEL read committed;

3.可重复读（REPEATABLE READ）解决的障碍：不可重复读； 引入的问题：
    set SESSION TRANSACTION ISOLATION LEVEL repeatable read;

4.可串行化（SERIALIZABLE）解决的障碍：可重复读； 引入的问题：锁全表，性能低下
    set SESSION TRANSACTION ISOLATION LEVEL repeatable read;

总结：
  事务隔离级别为可重复读时，如果有索引（包括主键索引）的时候，以索引列为条件更新数据，会存在间隙锁间、行锁、页锁的问题，从而锁住一些行；如果没有索引，更新数据时会锁住整张表
  事务隔离级别为串行化时，读写数据都会锁住整张表
  隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大，对于多数应用程序，可以优先考虑把数据库系统的隔离级别设为Read Committed，它能够避免脏读取，而且具有较好的并发性能。

5.事务保持点，实现部分回滚
定义保存点，以及回滚到指定保存点前状态的语法如下。

1. 定义保存点—SAVEPOINT保存点名;

2. 回滚到指定保存点—ROLLBACK TO SAVEPOINT 保存点名:

1、查看user表中的数据

mysql> select * from user;
+-----+----------+-----+------+
| mid | name | scx | word |
+-----+----------+-----+------+
| 1 | zhangsan | 0 | NULL |
| 2 | wangwu    | 1 | NULL |
+-----+----------+-----+------+
2 rows in set (0.05 sec)
2、mysql事务开始

mysql> BEGIN; -- 或者start transaction；
Query OK, 0 rows affected (0.00 sec)
3、向表user中插入2条数据

mysql> INSERT INTO user VALUES ('3','one','0','');
Query OK, 1 row affected (0.08 sec)
mysql> INSERT INTO user VALUES ('4,'two','0','');
Query OK, 1 row affected (0.00 sec)
mysql> select * from user;
+-----+----------+-----+------+
| mid | name | scx | word |
+-----+----------+-----+------+
| 1 | zhangsan | 0 | NULL |
| 2 | wangwu    | 1 | NULL |
| 3 | one            | 0 | |
| 4 | two             | 0 | |
+-----+----------+-----+------+
4 rows in set (0.00 sec)
4、指定保存点，保存点名为test

mysql> SAVEPOINT test;
Query OK, 0 rows affected (0.00 sec)
5、向表user中插入第3条数据

mysql> INSERT INTO user VALUES ('5','three','0','');
Query OK, 1 row affected (0.00 sec)
mysql> select * from user;
+-----+----------+-----+------+
| mid | name | scx | word |
+-----+----------+-----+------+
| 1 | zhangsan | 0 | NULL |
| 2 | wangwu | 1 | NULL |
| 3 | one | 0 | |
| 4 | two | 0 | |
| 5 | three | 0 | |
+-----+----------+-----+------+
5 rows in set (0.02 sec)
6、回滚到保存点test

mysql> ROLLBACK TO SAVEPOINT test;
Query OK, 0 rows affected (0.31 sec)
mysql> select * from user;
+-----+----------+-----+------+
| mid | name | scx | word |
+-----+----------+-----+------+
| 1 | zhangsan | 0 | NULL |
| 2 | wangwu    | 1 | NULL |
| 3 | one            | 0 | |
| 4 | two            | 0 | |
+-----+----------+-----+------+
4 rows in set (0.00 sec)

我们可以看到保存点test以后插入的记录没有显示了，即成功团滚到了定义保存点test前的状态。利用保存点可以实现只提交事务中部分处理的功能。

6.事务控制语句

BEGIN或START TRANSACTION；显式地开启一个事务；
COMMIT；                  也可以使用COMMIT WORK，不过二者是等价的。COMMIT会提交事务，并使已对数据库进行的所有修改成为永久性的；
ROLLBACK；                有可以使用ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；
SAVEPOINT identifier；    SAVEPOINT允许在事务中创建一个保存点，一个事务中可以有多个SAVEPOINT；
RELEASE SAVEPOINT identifier；    删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
ROLLBACK TO identifier；   把事务回滚到标记点；
SET TRANSACTION；   用来设置事务的隔离级别。InnoDB存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ和SERIALIZABLE。

  用 BEGIN, ROLLBACK, COMMIT来实现
  BEGIN 开始一个事务
  ROLLBACK 事务回滚
  COMMIT 事务确认
  直接用 SET 来改变 MySQL 的自动提交模式:
  SET AUTOCOMMIT=0或者off 禁止自动提交
  SET AUTOCOMMIT=1或者on 开启自动提交

事例：

create table user(
id int primary key auto_increment,
name char(32),
balance int
);

insert into user(name,balance)
values
('wsb',1000),
('chao',1000),
('ysb',1000);

#原子操作
start transaction;
update user set balance=900 where name='wsb'; #买支付100元
update user set balance=1010 where name='chao'; #中介拿走10元
update user set balance=1090 where name='ysb'; #卖家拿到90元
commit;  #只要不进行commit操作，就没有保存下来，没有刷到硬盘上
 
#出现异常，回滚到初始状态
start transaction;
update user set balance=900 where name='wsb'; #买支付100元
update user set balance=1010 where name='chao'; #中介拿走10元
uppdate user set balance=1090 where name='ysb'; #卖家拿到90元,出现异常没有拿到
rollback;  #如果上面三个sql语句出现了异常，就直接rollback，数据就直接回到原来的状态了。但是执行了commit之后，rollback这个操作就没法回滚了
#我们要做的是检测这几个sql语句是否异常，没有异常直接commit，有异常就rollback，但是现在单纯的只是开启了事务，但是还没有说如何检测异常，我们先来一个存储过程来捕获异常，等我们学了存储过程，再细说存储过程。
commit;

#通过存储过程来捕获异常：(shit！，写存储过程的是，注意每一行都不要缩进！！！按照下面的缩进来写，居然让我翻车了！！！我记住你了~~~)，我的代码直接黏贴就能用。

delimiter //
create PROCEDURE p5()
BEGIN
DECLARE exit handler for sqlexception
BEGIN
rollback;
END;

START TRANSACTION;
update user set balance=900 where name='wsb'; #买支付100元
update user set balance=1010 where name='chao'; #中介拿走10元
#update user2 set balance=1090 where name='ysb'; #卖家拿到90元
update user set balance=1090 where name='ysb'; #卖家拿到90元
COMMIT;

END //
delimiter ;

 


mysql> select * from user;
+----+------+---------+
| id | name | balance |
+----+------+---------+
|  1 | wsb  |    1000 |
|  2 | chao |    1000 |
|  3 | ysb  |    1000 |
+----+------+---------+
3 rows in set (0.00 sec)