SQL中的事务,简单讲解

    概念:

--事物
--我们知道违反check 约束的错误，其后SQL语句还不执行
--所以为的是解决该问题

--什么是事物，事物是一种机制，每个事务是一个独立的工作单元，包含一组数据库执行命令
--是一个不可分割的逻辑单元，处理并发操作时候，事务是一个最小的控制单元，它适合多用户同事操作数据库
--a，原子性，一个事务是一个完整的操作，没有说成功一半失败一半的道理
--b，一致性，必须使用所有数据的保持一致的状态
--c，隔离性，所有事务，并发事务彼此隔离每个事务都是独立存在的如果其他任何进程在同时使用相同数据，则必须等事务完成后才能进行
--d，持久性，只要事务提交成功之后就不能再回滚了
-- 优化事务，事务中尽可能的少放程序语句包括定义变量，系统会预留大量资源以保证事务的四大性质
--事务会在修改数据时候会加锁，占用过长时间，
--事务访问的数据量越少越好，访问行数越少则锁定越少，尽量不用 * 
--事务中尽量不要有等待用户输入，这样可能会因为事务占用大量资源而造成阻塞
--一般得记录错误@@error次数和@@rowcount 受影响行数进行判断以免@@error 被覆盖
--无法回滚的语句不能出现在事务中比如创建，修改，删除数据库
--一个事务执行完成后其后的SQL语句可以正常执行，但其后语句出错，事务不能回滚
--事务执行中出现不可预知事故或终止或错误，比如事务执行中突然断电则事务会在下次启动系统时候自动回滚
--工作原理，事务进行修改时候会将数据锁定，然后生成临时事务日志，存放数据更改前的数据和更改存储过程，
--如果错误，不会执行修改直接将数据回滚回去，并解除锁定，如果成功提交，事务日志内容将存储到数据库中，
--隔离级别高-低

--read uncommitted 不隔离，其他事务可以同时进行增删改
--read committed  默认，不允许读取没有提交的数据，事务执行中数据被修改的可能性还是很大，一个事务只能读取
--另一个事务已经提交更新的结果
--repeatable read 在事务中锁定所有读取的数据不让其他修改删除，单可添加
--snapshot 快照隔离
--serializable 将事务要用到的所有数据表全部锁定，并发性最低
--事务分为显式事务，隐式事务，自动提交事务，常用显式事务
--回滚事务 rollback transaction  
--提交事务 commit transaction  

--修改事务隔离级别语法
--set transaction isolation level read committed   --level后是隔离级别

 

if exists(select * from sys.databases where name ='stuDB1')
	drop database stuDB1
go
create database stuDB1
go
use stuDB1
go
create table bank
(
	cusId int primary key identity,
	cusName nvarchar(16),
	cusMoney money check(cusMoney>=1 and cusMoney<100000 ) --约束规定账户里面金额不能少于1元
)
go
insert into bank values('曹操',1000)
insert into bank values('孙权',1000)
go
--创建显式事务 孙权给曹操1000元让曹操不攻打东吴
declare @errorSum int =0    --错误总数
declare @moneyOut money =1000
--开始事务
begin transaction
--减少孙权的钱
update bank set cusMoney=cusMoney-@moneyOut where cusName='孙权'
	set @errorSum=@@error
--曹操加钱
update bank set cusMoney=cusMoney+@moneyOut where cusName='曹操'
	set @errorSum=@errorSum+@@error          

if(@errorSum<>0 or @rowcountSun<>2)
	begin
		print '转账失败，孟德将攻打荆州'
		rollback transaction  --回滚事务
	end
else
	begin
		commit transaction  --提交事务
		print '转账成功,孙曹联盟打死刘备'
	end
--会失败事务因为有约束不能小于1元，如果不用事务曹操的钱会添加成功，
--意思说孙权钱不减少钱而曹操的钱增加了，这样银行会倒闭的
go

--在存储过程使用事务
create proc proc_GetAndSell
	@money money,
	@outAccountNO int, --出
	@inAccountNo int --转入账户
as
	if not exists(select 1 from bank where cusId = @outAccountNO )
		begin
			print '转出账号不存在'
			return
		end
	if not exists(select 1 from bank where cusId = @inAccountNo )
		begin
			print '转入账号不存在'
			return
		end
	if(@money<100)
		begin
			print '转账金额不能少于100元'
			return
		end
declare @errorSum int = 0
begin transaction
	update bank set cusMoney =  cusMoney-@money where cusId = @outAccountNO
	set @errorSum = @@ERROR
	update bank set cusMoney =  cusMoney + @money where cusId = @inAccountNo
	set @errorSum = @@ERROR+@errorSum
	print convert(nvarchar,@errorSum)+'错误'
	if(@errorSum<>0 )
		begin
			print '转账失败'
			rollback transaction
		end
	else
		begin
			commit transaction
			print '转账成功!'
		end
go

exec proc_GetAndSell 200000,2,1
--实测 @@error 和@@rowcount 不能在同一SQL下使用
--因为上一行是@@error累加表达式,或是@@rowcount 都不和理都是记录到了表达式执行成功没

select * from bank 
--drop proc proc_GetAndSell                            
--set transaction isolation level read committed