数据库事务应用编程规范

最新推荐文章于 2024-08-28 17:34:01 发布

kyleing

最新推荐文章于 2024-08-28 17:34:01 发布

阅读量1.1k

点赞数

分类专栏：数据库

数据库专栏收录该内容

18 篇文章

订阅专栏

一、编写有效事务的指导原则

不要在事务处理期间要求用户输入或消息响应。

在事务启动之前，获得所有需要的用户输入。如果在事务处理期间还需要其它的用户输入，则回滚当前的事务，并在提供了用户输入之后重新启动该事务。即使用户立即响应，作为人，其反应时间也要比计算机慢得多。事务占用的所有资源都要保持很长的时间，这就有可能造成阻塞问题。如果用户没有响应，该事务就会仍保持活动状态，并锁定关键资源，直到他们响应为止，但是用户可能会几分钟甚至几小时都不响应。

在浏览数据时，尽量不要打开事务。

在所有预备的数据分析完成之前，不应启动事务。

保持事务尽可能地短。

在知道了必须要进行的修改之后，启动事务，执行修改语句，然后立即提交或回滚。只有在需要时，才打开事务。不要在发出更新语句后，再次进行复杂计算生成数据，再提交和回滚。
灵活地使用更低的事务隔离级别。

可以很容易地编写出许多使用授权读事务隔离级别的应用程序。并不是所有的事务都要求可串行事务隔离级别。

灵活地使用更低的游标并发选项，如乐观并发选项。

在很少有可能并发更新的系统中，处理某个偶然的"别人在您读取数据后更改了该数据"错误的开销，要比在读取数据时始终锁定行的开销小得多。

在事务中尽量使访问的数据量最小。

这样可以减少锁定的行数，从而减少事务之间的争夺。

二、避免死锁：

下列方法有助于最大限度地降低死锁：

· 按同一顺序访问对象。

· 避免事务中的用户交互。

· 保持事务简短并在一个批处理中。

· 使用低隔离级别。

三、触发器

不要尝试在触发器中定义事务，包括begin tran,commit,rollback。

四、存储过程：

存储过程对@@TRANCOUNT应无净改变，如果 @@TRANCOUNT 的值在存储过程完成时与过程执行时不同，则会生成一个 266 信息类错误.

当调用存储过程时，如果 @@TRANCOUNT 为 1 或更大，并且该过程执行 ROLLBACK TRANSACTION 或ROLLBACK WORK 语句，则会产生 266 号错误。这是因为 ROLLBACK 回滚所有未完成的事务，并将 @@TRANCOUNT 减到 0，该值比调用过程时要小。

存储过程既可以被前台执行，也可以在后台执行，凡涉及更新数据的存储过程，在定义事务时应严格遵守以下约定：

--以下为标准存储过程事务定义的模板
Create procedure proc_test
As
begin
declare @is_procedure_transaction char(1)	--是否是存储过程定义的事务
declare @local_error	int					--保存更新错误号

if @@trancount = 0 	--判断事务是否嵌套，如无则存储过程自己定义事务
begin
	select @is_procedure_transaction=’1’	--将存储过程定义的事务标志置’1’
	begin tran tran_procedure			--存储过程自己定义事务
end 

--此处为INSERT,UPDATE,DELETE等更新操作语句,并假定是存储过程中最后一个更新语句,如果不是最后的更新语句，仅处理错误部分
if @@error <> 0		--判断数据库更新时是否有错误发生
begin		--更新有错误
	set @local_error =  @@error
	if @is_procedure_transaction=’1’ 	--判断事务是否由存储过程定义
	begin
		rollback tran	--错误发生则回滚存储过程定义的事务,注意不要带事务名
	end 
	--close cursorname		如果定义有游标，请关闭
	--deallocate cursorname	如果定义有游标，请释放
	raiserror('数据更新错误，SQL错误号='+convert(varchar(6), @local_error),16,1)		--返回错误信息，并显示SQL错误号
	return -1
end 	--更新有错误处理结束
else
begin	--更新成功
	if @is_procedure_transaction=’1’ 		--判断事务是否由存储过程定义
	begin
		commit tran	--更新成功则提交存储过程定义的事务，注意不要带事务名
	end
	--close cursorname		如果定义有游标，请关闭
	--deallocate cursorname	如果定义有游标，请释放
	return 0
end	--更新成功处理结束
		end 	--结束整个存储过程
go

五、隔离级别与锁模式

l 数据一致性要求与并发性要求是一个矛盾体，数据一致性要求高则并发性就差，否则并发性要求高则数据一致性要求相对要求就低一些。应根据具体情况处理。

l 为避免脏读、不可重复读取或幻像读取，引入数据事务隔离级别定义：SET TRANSACTION ISOLATION LEVEL { READ COMMITTED| READUNCOMMITTED| EPEATABLE READ| SERIALIZABLE }，一次只能设置这些选项中的一个，而且设置的选项将一直对那个连接保持有效，直到显式更改该选项为止。这是默认行为，除非在语句的 FROM 子句中在表级上指定优化选项。SET TRANSACTIONISOLATION LEVEL 的设置是在执行或运行时设置，而不是在分析时设置。其中READCOMMITTED为Mssqlserver的默认设置。

可以使用 SELECT、INSERT、UPDATE和 DELETE 语句指定表级锁定提示的范围，以引导 MSSQLServer 使用所需的锁类型。当需要对对象所获得锁类型进行更精细控制时，可以使用表级锁定提示。这些锁定提示取代了会话的当前事务隔离级别。 SQL Server 查询优化器自动作出正确的决定。建议仅在必要时才使用表级锁定提示更改默认的锁定行为。举例如下：

USE pubs
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
SELECT au_lname FROM authors WITH (NOLOCK)
GO
EXEC sp_lock
GO

检查加锁情况发现引用 authors 唯一采用的锁是架构稳定性 (Sch-S) 锁。在这种情况下不能保证可串行性