事务
事务用于将某些操作的多个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.事务保持点,实现部分回滚
定义保存点,以及回滚到指定保存点前状态的语法如下。
-
定义保存点—SAVEPOINT保存点名;
-
回滚到指定保存点—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)