数据库复习6——事务

begin标志开始一个事务，多个SQL语句就是事务逻辑工作单元，commit（提交）是结束当前事务并提交事务内数据变更，让其生效

数据库一致性在事务上表现的比较特殊，具体来说：事务执行的过程中可以让数据库处于不一致状态，但一旦提交（commit）数据库必须回到一致的状态（一致性是指广义上的一致性，即真实世界的数据一致性，如A和B转帐他们的总账户余额不变等）

在了解事务具体作用以及事务使用场景前，先来了解一下事务的ACID性质：

• Atomicity：原子性，一个事务要么所有操作被执行，要么全部都不被执行
• Consistency：一致性，独立地看待一个事务，它必须保证数据库一致性
• Isolation：独立性，多个事务允许并发的执行，但是必须保证事务之间不会互相干扰，并且一个事务执行过程对其他事务应该是透明的
• Durability：持久性，当事务成功完成时，它对数据库的更改必须持久化

银行转账是介绍事务时最喜欢用的例子，账户A向账户B转账M元，表达成事务（抽象，非SQL，把update分成多步）就是：

read(A)
A = A - M
write(A)
read(B)
B = B + M
write(B)

这个转账过程是银行数据库经常需要完成的业务逻辑，我们可以把这样经常调用的事务封装成存储过程以供频繁调用（见存储过程一章）

ACID性质具体体现在这个存储过程上就是：

• A：要么转账过程的6个步骤全被完成要么全部不被完成，若A的账户不足M元，第三步write写失败（完整性约束），必须保证之前的操作对数据库均不产生影响
• C：A和B之和必须保证不变
• I：其他和A、B相关的并发转账事务或其他事务对当前转账不产生影响
• D：一旦A和B这两个账户持有者收到了转账成功的信息，那么该事务必须永久性生效，即使系统故障（权责划分明确，转账无误，即使故障不可恢复也应该由银行系统负责）

13.2 事务管理

了解事务管理之前必须了解一个事务在执行过程中可能处于状态：

• Active：事务初始状态，执行时保持该状态
• Partially committed：最后一个操作被执行后的状态（还未commit）
• Failed：发现错误，停止执行
• Aborted：事务被回滚（rollback）数据库恢复到事务执行前的状态，Aborted后有两个选择：重新执行（如果事务内部没有逻辑错误）、kill该事务
• Committed：事务成功执行完成后的状态

事务状态转换关系如下图：

从ACID性质以及上面的事务状态关系可以看出，数据库事务管理主要处理两件事情：

• 错误/失败：事务执行失败（逻辑错误、系统崩溃、断电等等异常）时恢复处理
• 并发执行：多个事务能够并发执行（并发的意义在于提高CPU和磁盘利用率，以及降低平均响应时间），并且防止并发事务间互相影响对数据库一致性的破坏

13.3 SQL事务

PPT says, “in SQL, a transaction begins implicitly”，但是我网上搜和自己测试（OSX上SQLite）都需要显式调用begin;或begin transaction [name];表示一个事务的开始

SQL事务中支持两条语句：commit [transaction];和rollback;，含义都很明确，另外end或end transaction [name];也能结束事务并默认commit

（1）测试声明事务和回滚

建表 → 开始一个匿名的事务 → 插入一个元组 → select查看（已插入）→ rollback测试

sqlite> create table Stu(id integer primary key, name text);
sqlite> begin;
sqlite> insert into Stu values(1, ‘jcguo‘);
sqlite> select * from Stu;
1|jcguo
sqlite> rollback;
sqlite> select * from Stu;

SQLite命令行无输出，表示Stu是空表，成功回滚插入

（2）测试回滚后事务是否结束

接刚才rollback后的select语句 → 插入两个元组 → select查看（已插入）→ commit命令测试刚才的事务是否结束

sqlite> insert into Stu values(1, ‘jcguo‘);
sqlite> insert into Stu values(2, ‘wp‘);
sqlite> select * from Stu;
1|jcguo
2|wp
sqlite> commit;
Error: cannot commit - no transaction is active

DBMS返回错误，说明刚才事务已经结束，并且验证没有事务执行时不能执行commit命令（即不像ppt中讲的SQL事务是隐式执行的）

（3）测试commit

sqlite> begin transaction tt;
sqlite> insert into Stu values(3, ‘wpo‘);
sqlite> commit;
sqlite> select * from Stu;
1|jcguo
2|wp
3|wpo

插入后commit，事务成功执行

（4）测试事务逻辑错误

sqlite> begin transaction tt;
sqlite> insert into Stu values(3, ‘wpo‘);
Error: UNIQUE constraint failed: Stu.id
sqlite> rollback;
sqlite> rollback;
Error: cannot rollback - no transaction is active

插入一条引发完整性约束失败的元组，事务并没有隐式地进入Failed状态并自动Aborted，我还是手动完成rollback

注：

1. 本来打算事务、恢复和并发放在一个章节，发现后面两张内容较多还是分开较好
2. 上述测试不是很完善，SQLite也不是主流DBMS，后续恢复和并发会有更多测试
3. 搜索发现某些DBMS支持set implicit_transactions on/off;语句开启或关闭隐式事务

数据库复习6——事务

标签：数据库   事务   sql   sql事务