013 数据库学习笔记--事务

事务：

• 由一系列T-SQL语句构成的逻辑工作单元，为了完成一定的业务逻辑，将操作封装起来，可以是一个操作，也可以是多个操作。
• 与其他语句形成边界，形成一个相对独立的工作单元。

事务应用：

• 多个表进行操作时使用；
• 处理过程中，出现了某种异常或系统死机或断电，数据并不会保持到数据库

 事务的处理结果：

• 事务没有发生任何错误，事务全部被提交
• 如果有某一个操作发生错误或发生故障，则所有的操作都会被全部回滚到最初状态

作用：　

• 通过事务提高数据的安全性
• 增强了数据的处理效率
• 维护数据库的完整性

事务的特性：ACID

• A：原子性，Atomic一个工作单元，一个整体提交或回滚，各个元素是密不可分的——一个操作；
• C：一致性，ConDemoltent 数据必须保持一致，数据状态一致
• I：隔离性，Isolated多个事务之间是彼此隔离的——事务独立性
• D：持久性，Durabiliy 事务提交后，对数据库的更改是永久保存的，即使系统出现故障，也会保留，真实的修改了数据库

事务分类：

• 显式事务
  • 明确指出事务的起止边界。如果不显式定义事务的边界，SQL Server 会默认把每个单独的语句作为-一个事务;换句话说，SQLServer默认在执行完每个语句之后就自动提交事务
  • 显示事务需要定义以BEGIN TRAN语句作为开始。如果想提交事务，则应该以COMMIT TRAN语句显式结束事务;如果不想提交事务(撤消事务中的修改)，则应该以ROLLBACK TRAN语句显式结束事务
• 隐式事务
  • SQL查询分析器中，当前会话默认就是为隐式事务。每执行一条DML操作，就直接提交到数据库保存

开启显式事务：begin tran

开启隐式事务：begin tran  \r\n  set  implicit_transaction on

关闭隐式事务：set  implicit_transaction off

语法：

--开启事务
begin try 
	begin tran 
	--set implicit_transactions on;--开启一个隐式事务

	--一系列T-SQL操作、insert\update\delete

	commit tran--提交事务
end try
begin catch
	
	rollback tran --事务回滚
end catch	

示例：

--开启事务
begin try 
	begin tran 
	--set implicit_transactions on;--开启一个隐式事务

	--一系列T-SQL操作、insert\update\delete
	insert into [dbo].[student] values('测试事务',10,'未知')
	delete from [student] where student_name='张三'

	print '操作成功';
	commit tran--提交事务
end try
begin catch
	print '操作出现异常';
	rollback tran --事务回滚
end catch	


--注：事务一般应用于存储过程之中

应用：

ALTER   procedure [dbo].[INIT_DICT_QUEUECODE]
(
	 @p_QUEUEID int,
	 @p_QUEUENAME  varchar(100),
	 @p_DEVICECOUNT int, 
	 @p_CHECKDURATION int,
	 @p_BEGINTIMEPART varchar(50),
	 @p_ENDTIMEPART varchar(50),
	 @p_CODECOUNT int,
	 @p_QUEUESIGN varchar(50),
	 @p_ADDRESS varchar(300),
	 @p_REMARK varchar(500),
	 @p_ofdepart varchar(30),
	 @p_PMBEGINTIME varchar(50),
	 @p_PMENDTIME varchar(50),
	 @p_CodeCoefficient varchar(50),
	 @p_TIMEPART varchar(50),
	 @p_PARAMTYPE  varchar(50),
	 @p_CALLTYPE varchar(50),
	 @p_result int output
)
as
insert into QS_PARAM (QUEUEID, QUEUENAME, DEVICECOUNT, CHECKDURATION, BEGINTIMEPART, ENDTIMEPART, CODECOUNT,QUEUESIGN,ADDRESS,ofdepart,PMBEGINTIME, PMENDTIME, CodeCoefficient, TIMEPART,PARAMTYPE,CALLTYPE,REMARK)
values(@p_QUEUEID, @p_QUEUENAME, @p_DEVICECOUNT, @p_CHECKDURATION, @p_BEGINTIMEPART, @p_ENDTIMEPART, @p_CODECOUNT,@p_QUEUESIGN,@p_ADDRESS,@p_ofdepart,@p_PMBEGINTIME, @p_PMENDTIME, @p_CodeCoefficient, @p_TIMEPART,@p_PARAMTYPE,@p_CALLTYPE,@p_REMARK)
declare 
	@loopNum int,--循环次数
	 @codeNum int,--号源编号
	 @codeFirstAm int,
	 @codeFirstPm int
DECLARE  paramDetails cursor 
for 
	select queuename,paramtype,begintimepart,endtimepart,ofdepart,calltype,codecount from qs_param  a 
	where a.queueid=@p_QUEUEID and a.paramtype=@p_PARAMTYPE order by convert(float,endtimepart)
 begin  try
	 set @loopNum=1
	 set @codeNum=1
	 set @codeFirstAm=0--未进行初始化
	 set @codeFirstPm=0--未进行初始化
	--删除号源字典
	 delete from dict_queuecode  where queuename=@p_QUEUENAME and codeparamtype=@p_PARAMTYPE and ofdepart=@p_ofdepart
    --开始事务
	begin tran --当前事务点，rollback、commit都从这里开始    
	DECLARE
					 @v_QUEUEID int,
					 @v_QUEUENAME  varchar(100),
					 @v_DEVICECOUNT int, 
					 @v_CHECKDURATION int,
					 @v_BEGINTIMEPART varchar(50),
					 @v_ENDTIMEPART varchar(50),
					 @v_CODECOUNT int,
					 @v_QUEUESIGN varchar(50),
					 @v_ADDRESS varchar(50),
					 @v_ofdepart varchar(30),
					 @v_PMBEGINTIME varchar(50),
					 @v_PMENDTIME varchar(50),
					 @v_CodeCoefficient varchar(50),
					 @v_TIMEPART varchar(50),
					 @v_PARAMTYPE  varchar(50),
					 @v_CALLTYPE varchar(50)
 open paramDetails
 fetch next from paramDetails into @v_queuename,@v_paramtype,@v_begintimepart,@v_endtimepart,@v_ofdepart,@v_calltype,@v_codecount
		WHILE @@FETCH_STATUS =0
		 begin
					--循环获取qs_param表中维护的某个时间段
					set @loopNum=1
					 while @loopNum <= @v_codecount 
							 begin
								--上午
								  if @codeFirstAm=0 and @v_calltype='上午' 
									  begin
											set @codeNum=1
											set @codeFirstAm=1
										end
								--下午
								  if @codeFirstPm=0 and @v_calltype='下午' 
									  begin
											set @codeNum=1
											set @codeFirstPm=1
										end
								  --添加号源信息
									insert into DICT_QUEUECODE  (queuename,codeparamtype,codevalue,timepart,hintinfo,OFDEPART,calltype,queueid)
									values (@v_queuename,@v_paramtype,@codeNum,@v_begintimepart+'~'+@v_endtimepart,'',@v_ofdepart,@v_calltype,@p_QUEUEID)
								  --重新赋值
									 set @loopNum=@loopNum+1
									 set @codeNum=@codeNum+1
							end
		fetch next from paramDetails into @v_queuename,@v_paramtype,@v_begintimepart,@v_endtimepart,@v_ofdepart,@v_calltype,@v_codecount
		end
		close paramDetails
		DEALLOCATE  paramDetails
		set @p_result=1
    --提交事务
	commit    
end try
begin catch
      set @p_result=-1 
	--回滚事务
	rollback    
end catch

事务并发问题：

1. 丢失或覆盖更新
   • 一个事务子不知道其他事务存在的情况下，对数据进行修改，而造成的数据丢失
2. 脏读
   • 一个事务读取了另一个事务并未提交的更新
3. 不可重复读
   • 多个事务访问同一条数据，每次读取的数据不同，不可重复读
4. 幻象读
   • 第一次读取的数据行与第二次读取的数据行不一致，可能第二次读取到的数据是第一次读取时不存在的(新增或删除数据)

解决方式：设置事务隔离级别——事务接受的不一致数据访问级别(隔离级别如果太低，则会造成很多并发问题；隔离级别较高的话，则可降低并发问题)

1. read committed：
   • 默认的级别，可避免脏读问题；
   • 会产生不可重复读、幻象读
2. read uncommitted：
   • 0 级别，上面四种并发问题都会产生
3. repeatable read：
   • 可有效防止其他用户更新数据，可有效避免脏读
   • 不可重复读
   • 会产生幻象读
4. serializable：
   • 可避免脏读、不可重复读、幻象读

设置事务隔离级别示例：

--开启事务
begin try 
--设置事务的隔离级别 
set tran isolation level read committed --默认的级别，可避免脏读问题；会产生不可重复读、幻象读
--set tran isolation level read uncommitted --0 级别，上面四种并发问题都会产生
--set tran isolation level repeatable read --可有效防止其他用户更新数据，可有效避免脏读；不可重复读；会产生幻象读
--set tran isolation level read serializable --可避免脏读、不可重复读、幻象读

	begin tran 
	--set implicit_transactions on;--开启一个隐式事务

	--一系列T-SQL操作、insert\update\delete
	insert into [dbo].[student] values('测试事务',10,'未知')
	delete from [student] where student_name='张三'

	print '操作成功';
	commit tran--提交事务
end try
begin catch
	print '操作出现异常';
	rollback tran --事务回滚
end catch