9.1事务的详细介绍

--学校财务让出纳张三和李四去银行取钱，对同一个银行账务A内有200元，
--张三进行提款操作100元，李四进行转账操作100元到B账户。要是没有进行隔离可能会出现一下并发问题：
--（1）第一类丢失更新：先是张三提款时账户内有200元，同时李四转账时也是200元，然后张三李四同时操作
--张三操作成功取走100元，李四操作失败回滚账户内最终为200元，这样张三的操作被覆盖了么银行损失100元
--（2）脏读：张三提款100元未提交，李四进行转账查到账户内还有100元，这时候，张三放弃操作回滚，李四正常操作提交
--账户内最终为0元，李四读取了张三的脏数据，客户损失了100元
--（3）虚读:和脏读类似，是针对插入操作过程中的读取问题，如王二存款100元未提交，这时候，银行做报表统计查询账户为200元
--然后王二提交了，这时候银行再统计发现账户为300元了，无法判断哪个到底以哪个为准？
--(4)不可重复读：张三和李四同时开始，都查到账户为200元，张三先开始提款100元提交，李四在准备最后更新的时候又进行了一次查询
--发现结果时100元，这时李四就会很困惑，不知道将账务改为100还是0--
--(5)第二类丢失更新：不可重复读的一个特例，例如：张三不做第二次查询而是直接操作完成，账户内最终为100元，李四的操作被覆盖掉了
--银行损失100元，感觉和第一类更新类似。
--食物是所有数据课管理系统中一个非常重要的概念，不管是数据库管理人员还是数据库开发人员，都应该对事务有较深刻的理解

--既然上述出现了问题，我们怎样解决呢？如何防止丢失更新？如何防止脏读和虚读？如何实现可重复读呢？？
--这时候就引入了事务管理来解决以上问题。
--
--那什么是事务呢?让我们来了解一下事物的原理
--将一组操作绑定在一个事务中，为了使事务成功，必须成功完成该事务中的所有操作
--例如银行转账问题：把转出和转入作为一个整体，形成一个操作集合，这个集合中的操作要么不执行，要么都执行。

--了解了事物的原理那再来了解一下事物的概念吧
--事务（transaction）是由对数据库的若干操作组成的一个逻辑单元，这些操作要么不执行，要么都执行，是一个不可分割的整体。事务用这种方式保证数据满足并发性
--和完整性的要求。使用事务可以避免发生有的语句被执行，而另一外一些语句没有被执行，从而造成数据不一致问题。

--既然都了解到了事务的概念那为啥不再多学点呢是不是。继续了解事物的特性
--原子性（A），一致性（C），隔离性（I）和持久性（D）简称ACID原则。
--光知道这些还不够，我们要做到知其然知其所以然
--原子性（Atomicity）:事务必须是原子工作单元，实务中的操作要么全部执行，要么全不执行，不能只完成部分操作。
--原子性在数据库系统中，由恢复机制来实现。
--一致性（Consistency）:事务开始之前，数据库处于一致性的状态哦；事务结束后，数据库必须仍处于一致性状态。
--数据库一致性的定义是由用户负责的，如前面所述的银行转账，用户可以定义转账前后两个账户金额之和应保持不变。
--隔离性（Isolation）:系统必须保证事务不收其他并发执行事务的影响，即当多个事务同时进行是，各事务之间相互隔离，
--不可相互干扰，事务查看数据时所处的状态，要么是另一个并发事务修改它之前的状态，要么是另一个并发事务修改它之后的状态，
--事务不会查看中间状态的数据。隔离性通过系统的并发控制机制实现。
--持久性（Durability）:一个已完成的事务对数据所作的任何变动在系统中是永久有效的，即使该事务产生的修改是不正确的，错误也将一直保持。
--持久性通过恢复机制实现，发生故障时，可以通过日志等手段恢复数据信息。
--事务四原则保证了一个事物或者成功提交，或者失败回滚，二者必居其一，因此，它对数据的修改具有可恢复性。即当事务失败时，它对数据的修改都会恢复到该事务执行前的状态。

--学习如逆水行舟不进则退，所以还得继续学习
--我们一起来学习下事物的工作原理吧
--事务以begin transaction 开始，以commit transaction或rollback transaction结束
--其中commit transaction表示事务正常结束，提交给数据库，而rollback tranasction表示事务非正常结束，撤销事务已经做的操作，回滚到开始时的状态。
--再给大家熟一点，其实我们平常执行的SQL语句就是一个事务，为啥我们都不知道呢？
--因为事务分为显式事务和隐性事务。
--而我们执行的SQL语句就是隐性事务，也叫系统提供的事务。不如说create table studnet(....)就是一个隐性事务
--这下感觉隐性事务是不是好多呀，既然多呢，那咱们就不多说了，我们还是说说显式事务吧
--那什么是显式事务呢
--书上说的是显式事务又称为用户定义的事务。事务有一个开头和一个结尾，它们指定了操作的边界。边界内的所有资源都参与同一个事务。当事务执行遇到错误时，将取消事务对数据库所做的操作。
--因此呢我们需要把参与事务的语句封装在一个begin tran/commit tran块中。一个显式事务的语句以begin transaction开始，至commit transaction或rollback transaction结束。事务的定义是一个完整的过程，
--指定事务的开始和表明事务的结束两者缺一不可。
--事务学得差不多了但是还缺少语法，不然我们无法定义事务呀
--现在就让我们来看看事务的用法吧
--（1）begin transaction语句定义事务的起点。语法格式：begin tran[saction]事务名称 @事务变量名称
--具体说明下哦：@事务变量名称是由用户定义的变量，必须用char、varchar、nchar或nvarchar数据类型来声明该变量
--begin transaction语句的执行使全局变量@@trancount的值加1
--（2）commit transaction提交事务
--提交事务，意味着将事务开始以来所执行的所有数据修改为数据库的永久部分，因为此也标志着一个事务的结束。一旦执行了该命令，将不能回滚事务。只有所有修改都准备提交给数据库时，才执行这一操作。
--语法为：commit [tran[saction]]事务名称 @事务变量名称
--再来说明下：commit transaction语句的执行会使全局变量@@trancount的值减1
--（3）rollback transaction回滚事务
--就是说当事务执行过程中遇到错误时，使用rollback transaction语句使事务回滚到七点或者指定的保持点。同时系统讲清除自事务起点或者某个保存点所做的数据修改，并且释放由事务控制的资源。这条语句也标志着事务的结束。
--语法为：rollback[tran[saction][事务名称] @事务变量名称|存储点名称|@含有存储点名称的变量名]
--最后说明下：当条件回滚只影响事务的一部分时，食物不要全部撤销已执行的操作。可以让事务回滚到指定位置。此时呢，需在事务中设定保存点（savepoint）。保存点所在位置之前的事务语句，不用回滚，
--即保存点之前的操作被视为有效的。保存点的创建通过“save transaction 保存点名称”语句来实现，然后再执行“rollback transaction 保存点名称”语句回滚到该保存点；若事务回滚到起点，则全局变量@@trancount的值减1；
--若事务回滚到指定的保存点，则全局变量@@trancount的值不变。

--好了关于事务的相关定义语法啥的就介绍完了，其实真算起来也没啥东西是不是。所谓万事俱备，只欠东风了。既然我们把事务的定义啥的都了解了，下面就是进入实战的阶段啦。先伙伴你们的枪炮都上膛了吗
--现在随我一起向事务的案例发起进攻，冲啊，兄弟们，你们是无敌的！！！
--第一座城池事务的显示开始和显示回滚
--创建数据，创建表，声明局部变量，开始事务，插入数据，显示回滚，查看结果
use master --使用master作为当前数据库
go
--master数据库中若存在用户创建的表testTable,则删除之
if object_id('testTable') is not null
drop table testTable
go

create table testTable(id int,name nchar(10))
go
declare @TransactionName varchar(20);--声明局部变量
set @TransactionName='Transaction1';--局部变量赋初值
print @@trancount --向客户端返回当链接上的已发生的begin transaction语句数
begin tran @TransactionName --显示开始事务
    print @@trancount
    insert into testTable values(1,'张三')--插入记录
    insert into testTable values(2,'李四')
rollback tran @TransactionName --显示回滚事务，取消插入操作，将标中数据恢复到初始状态
print @@trancount
--我们看代码没有错误吧，既然没有错误，程序就会从开始运行到结束，最会有个回滚事务
--事务回滚到局部变量赋初值，所以呢张三李四的数据没有插入到表中。
begin tran @TransactionName
    print @@trancount
    insert into testTable values(3,'王二')--插入记录
    insert into testTable values(4,'麻子')
    if @@ERROR>0  --如果系统出现意外
        rollback tran @TransactionName  --则进行回滚操作 
    else
        commit tran @TransactionName  --显示提交事务
print @@trancount
--首先来看事务开始@@trancount初值为0，没有啥意外，插入数据，这时候@@trancount的值为0
--插入数据后，来到判断，没有错误，执行if条件的另一个语句，也就是提交事务，这时事务提交完成
--也就是说这个整体都己经完成了。所以两条记录成功插入了表中。
select * from testTable  --查询表中所有记录
drop table testTable  --删除表
--第一座城池已经攻下，我们来总结下这座城带给了我们什么
--首先是数据表创建，先判断该数据库中是否有该表，有则删除，没有则创建
--其次就是变量的声明和赋值
--然后就是显示事务的创建开始，数据的插入事务回滚和不回滚，以及在事务中判断事务是否会出现错误
--最终数据时提交还是回滚，以及查看数据，删除表。
--不知不觉我们从这座城学到了这么多东西。
--拿下一城士气爆棚，继续进攻
--第二城
--向教师表中插入一名教师的信息，运行正常则插入成功，反之回滚
use bankdb --使用bankdb作为当前数据库
go
--bankdb数据库中若有teacher表则删除
if OBJECT_ID('teacher') is not null
drop table teacher
go
--创建teacher表
create table teacher(
id int,name varchar(10),birthday datetime,department varchar(20),salary int null
)
go

begin transaction
    insert into teacher values('001','你妹',1937-07-01,'计算机学院',1000)
    insert into teacher values('002','你大爷',1921-08-0,'计算机学院',1000)
    select * from teacher

    update teacher set salary=salary+1000  --给教师加薪水
    Save transaction savepoint1
    insert into teacher values('003','铁憨憨',1958-07-17,'计算机学院',null)
    if @@ERROR>0
        rollback transaction savepoint1
    if @@ERROR>0
        rollback transaction
    else 
        commit transaction
select * from teacher
--这座城大略一看没啥东西呀是不是，但仔细一看save transaction [名字] 是啥东西啊，
--这是在事务内设置的保存点的名字，这样在第一次回滚时，就可以回滚到这个保存点，就是savepoint1
--而不是回滚到整个事务，insert into teacher会被取消，但是事务本身仍然将继续，也就是插入的教师信息将从事务中消除去
--但是事务本身仍在进行。数据表撤销该教室信息的插入，但是给教师的薪水加100元的操作会被正常保存在数据库中；到了后一个回滚，
--由于没有给出回滚到的保存点名字，rollback transaction将回滚到begin transaction前的状态，即修改和插入操作都被撤销，就像没有发生一样。
--这座城太简陋了，没啥东西啊，同志们收拾下，准备下一个城池
--第三城
--删除‘工业工程’系，将‘工业工程’系的学生规划到‘企业管理’系
use 教学管理
go
begin transaction my_transaction_delete
use 教学管理 --使用数据库-》教学管理
go
    delete from 系部 where 系别='工业工程'
    save transaction after_delet  --设置事务恢复断点
--工业工程系的学生系别编号改为企业管理系的系别编号
    update 学生 set 系别='企业管理' where 系别='工业工程'
    if @@ERROR<>0 or @@ROWCOUNT=0 then
--检测更新是否成功，@@error返回上一个SQL语句状态，非零及说明出错，错了则回滚
    begin 
    roolback tran after_delete
--回滚到保存点after_delete,如果使用rollback ny_transaction_delete则会回滚到事务开始之前
    commit tran
    print '更新学生表时出错'
    return
    end

commit transaction my_transaction_delete
go
--大眼一看这是啥也，啥也不是，其实也就是说名不指定回滚事务名称或保存点
--rollback transaction命令会将事务回滚到事务执行前，如果事务是嵌套的，则会回滚到最靠近的begin transaction命令前
--最终大boss 银行装帐业务的事务处理
--银行转账是事务管理最典型的代表，通知门拿起你们的武器，向Boss发起进攻
use master
go
if DB_ID('bankdb') is not null
drop database bankdb
go
--创建数据库bankdb
create database bankdb
go
--选择当前数据库
use bankdb
go
--创建表account
if OBJECT_ID('account') is not null
    drop table account
go

create table account(
id int identity(1,1) primary key,    --设置主键
cardno char(20) unique not null,--创建非空唯一值
balance numeric(18,2)
)

--插入记录到account
insert into account values('01',1000.0)
insert into account values('02',1000.0)
go

--创建存储过程以延时转账事务
if exists(
    select name from sys.objects where name=N'sp_transfer_money')
    drop procedure sp_transfer_money
go

create procedure sp_transfer_money  --创建存储过程
    @out_cardno char(20),--转出账户
    @in_cardno char(20),--转入账户
    @money numeric(18,2)--转账金额
as
begin
    declare @remain numeric(18,2)
    select @remain=balance from account where cardno=@out_cardno
    if @money>0
        if @remain>=@money
            begin
                begin transaction t1--开始执行事务
                --执行的第一个操作，转账出钱，减去转出的金额
                update account set balance=balance-@money where cardno=@out_cardno
                --执行第二个操作，接受转账的金额，余额增加
                update account set balance=balance+@money where cardno=@in_cardno

                if @@ERROR>0  --如果系统出现意外
                    begin
                        rollback tran t1  --进行回滚操作，恢复转账之前状态
                        return 0
                    end
                else
                    begin 
                        commit transaction t1  --显示提交事务
                        print '转账成功！'
                    end
                end
            else
                begin
                    print '余额不足'
                end
            else
                print '转账金额应大于余额'
            end
go
--执行储存过程
exec sp_transfer_money '01','02',50

--查看转账记录
select * from account

--相信大家学到这，对银行转账事务处理不难理解吧。Boss已除，天下太平，我也要卸甲归田啦。
--最后再来总结下使用事务的考虑
--事务应尽可能短哦
--因为较长的事务增加了事务占用数据的时间，会使其他必须等待访问数据的事务等待较长时间。
--为使事务尽可能短，可以采取以下方法
--（1）事务在使用过程控制语句改变程序运行顺序时，一定要非常小心。
--（2）在开始事务之前，一定要了解用户交互式操作才能得到的消息，以便在事务执行过程中，
--可以避免进行一些耗费时间的交互式操作，从而缩短事务进程的时间。
--（3）应该尽可能地使用一些数据操纵语言，例如insert,update和delete语句，因为这些语句主要是操纵数据库中的数据
--而对于一些数据定语言，应该尽可能地少用或者不用，因为数据定义语言的操作既占用比较长的时间，又占用比较多的资源
--并且数据定定义语言的操作通常不涉及数据，所以应在事务中尽可能的少用或者不用
--（4）在使用数据操纵语言时，一定要在这些语句中使用条件判断语句，使得数据操纵语言涉及到尽可能少的记录，从而缩短事务的处理时间
--还有就是不建议使用嵌套事务