SQL学习笔记----第七天（事务处理机制在批量 SQL 中的作用）

1.事务的作用和特点

在数据库世界里一直流传着这样一个经典故事：客户A在银行账户上向客户B转了500元钱，这个操作对我们来说很简单，一步就可以完成，但在银行系统的数据库中却需要两步：

• 在客户A的银行账户上减去500元
• 在客户B的银行账户上增加500元

事务处理的一个重要作用(也叫特性)就是保证数据库中多条语句能完整执行，被定义为一个事务的批量 SQL，要么全部执行，要么全部不执行。事务一般须满足以下四特性:

• 原子性（Atomicity,或者不可分割性）：事务中的所有操作，要么全部执行，要么全部不执行，不会只执行中间的一部分；
• 一致性（Consistency）:在事务开始之前和事务字结束之前（不管成功还是失败），数据库的完整性没有被破坏，比如客户端A向客户端B转账500元，不会出现A减少500元。而B没有增加的情况。
• 隔离性（Isolation,也称独立性）：多个事务可以并发的对同样的数据进行读写或者修改，这样可以避免多个事务并发操作导致的数据不一致；
• 持久性（Durability）：事务提交完成后。对表数据的修改是永久性的。

不同数据库开启一个事务的语法略有不同，下面是在
MariaDB(MySQL分支)中一个完整的事务处理过程：

Start Transaction --事务开始
SQL1
SQL2
SQL3
...
COMMIT/ROLLBACK   --提交或回退

PostgreSQL 一个完整的事务处理过程:：

BEGIN [TRANSACTION]; 
SQL1
SQL2
SQL3
...
COMMIT(或 END TRANSACTION)/ROLLBACK   --提交或回退

SqlServer 一个完整的事务处理过程:

BEGIN TRANSACTION
SQL1
SQL2
SQL3
...
COMMIT [TRANSACTION]/ROLLBACK [TRANSACTION]

在 Oracle 中不需要开启事务，在执行修改数据的语句后，直接执行 commit/ROLLBACK 事务控制语句即可。
**这里的提交(COMMIT)是指 SQL 语句执行的结果存储到数据库中，回退 (ROLLBACK ) 是指撤销 SQL 语句的执行。**在 MariaDB 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务，而且默认情况下，MariaDB 的事务是开启并自动提交的，就是说，只要执行一条 DML(insert，delet，update，select 等操作)语句就开启了事务( Start Transaction )，并且自动提交（不需要在脚本中执行 commit 操作），这种自动提交模式是可以通过系统参数 autocommit 控制的，可以根据需要进行设置。

当自动提交模式开启时(参数 autocommit 值为 on)，使用上面例句中的 Start Transaction，会将自动提交模式保持禁用状态，即手动开启了一个新的事务，直到使用 commit 或 rollback 结束该事务，同时恢复到自动提交模式。

2.事务的操作

为了方便查看每个学生的选修课程总学分，在学生信息表中新增总学分字段 sum_grade：

alter table student add sum_grade int  default 0 

现在有一个业务流程需要使用事务来处理：将一位同学的选课成绩录入数据库中，大致的业务处理流程如下:

1. 检索学生信息表 student 中是否存在该学生的信息，如果不存在，需要为该学生在学生信息表student 中创建一个账号；
2. 检索该学生所选课程是否在课程信息表 course 中存在，如果不存在，需要在课程信息表 course中生成一条课程信息记录；
3. 在学生选课信息表 elective 中新增一条该名学生的选课成绩记录；
4. 在学生信息表 student 中更新该学生的总学分；

现在假如在不使用事务的情况下数据库出现故障，会发生什么情况呢？如果故障发生在第一步执行完成后，学生的信息被填充到 student 后，不会出现什么问题，如果故障发生在第二步执行完成后，学生的信息和相关的课程信息被分别添加到 student 和 course 后，对业务数据也不会造成什么影响。

但如果故障发生在第三步执行完成后，学生的单科成绩被添加到表 elective 中，由于第四步没有得到执行，最终该名学生的总学分没有得到更新。

所以针对上面的这些业务场景需要使用事务来处理，事务可以保证以上的操作步骤要么全部执行成功，要么全部执行失败，从而避免了部分步骤执行导致的数据不一致的情况。上面的例子使用事务来处理的具体SQL如下：

Start Transaction;

if not  exists (select 1 from student where  sid='1020' and stu_name='嫦娥仙子') then
  insert into student (sid,stu_name,gender,age,sum_grade) values('1020','嫦娥仙子',0,22,0);
end if;

if not  exists (select 1 from course where  cid='31' and cname='计算机网络工程')  then
   insert into course (cid,cname,credit) values('31','计算机网络工程',5);
end if;
 
if not  exists (select 1 from elective where  cid='31' and sid='1020')  then
 insert into elective(sid,cid,createtime,grade) values('1020','31',now(),93);
end if;

update student set sum_grade =  ifnull(sum_grade,0 )+(case when  (select grade from elective where cid='31' and sid='1020')>=60 then 5 else 4 end )  
 where sid='1020' and stu_name='嫦娥仙子';
 
commit;

上面的脚本执行成功后，通过以下SQL以此查看相关表记录：

 select  * from student where  sid='1020' and stu_name='嫦娥仙子';

结果集：
学生的总学分已更新为5。再看下课程信息表的查询结果：

该学生在选课成绩表中的记录：

 select  * from elective where  cid='31' and sid='1020';

3.小结

本节也是今天在慕课网DBA帖子上学到的，分享给大家，可以一起了解了数据库中比较重要的一个概念-事务；在关系型数据库中，事务可以说是无处不在。由于事务的存在，也为多人并发操作数据库提供了可能。