关于事务嵌套,以前了解一些,但总是属于模棱两可,平时处理这种问题时候也是依照前人的经验,但至于为什么这么做,还真是“知其然不知其所以然”。
今天一个同事问我关于事务的问题,我就用代码给他举例测试,在测试的过程中我遇到了一点小问题,但在继续测试的时候,我解决了这个问题,也让我对事务的嵌套有了更加深刻的认识。
废话不再多说了,开始正题。
本文的目的是跟大家讨论一下关于嵌套事务的相关问题,所以有关事务的基础知识和概念,本文假设读者已经了解。
嵌套事务一般的使用场景是一些公用的,最小单元的业务逻辑,这些业务逻辑很多情况下都是被另外一些更加复杂,更加完整的业务逻辑调用。
为了更加贴近实际,本文的例子尽量接近真实业务,在此我们拿一个电子商务网站的订单支付来进行举例,具体例子如下
提交订单之后,支付订单(扣除账户余额)并更新订单的状态。
根据业务,我们创建三个表
会员表
账户变动记录表
订单表
建表语句如下:
CREATE
TABLE
T_Users(
Id
INT
IDENTITY(1,1)
PRIMARY
KEY
NOT
NULL
,
--自增编号
UserName NVARCHAR(50)
NOT
NULL
,
--用户名,保持唯一
UserMoney
DECIMAL
(9,2)
NOT
NULL
DEFAULT
0
--用户账户余额,不能小于
)
CREATE
TABLE
T_MoneyLog(
Id
INT
IDENTITY(1,1)
PRIMARY
KEY
NOT
NULL
,
--自增编号
UserName NVARCHAR(50)
NOT
NULL
,
--用户名
ChangeType
INT
NOT
NULL
,
--账户变动类型(1支付订单,发送短信,提交参会申请)
MoneyBefore
DECIMAL
(9,2)
NOT
NULL
DEFAULT
0,
--会员账户变动前余额
ChangeMoney
DECIMAL
(9,2)
NOT
NULL
DEFAULT
0,
--变动的金额
MoneyAfter
DECIMAL
(9,2)
NOT
NULL
DEFAULT
0,
--会员账户变动后余额
Remark NVARCHAR(100),
--账户变动备注
AddTime DATETIME
NOT
NULL
DEFAULT
GETDATE()
--变动时间
)
CREATE
TABLE
T_Order(
Id
INT
IDENTITY(1,1)
NOT
NULL
,
--自增编号
OrderId
VARCHAR
(20)
NOT
NULL
PRIMARY
KEY
,
--订单号
SumMoney
DECIMAL
(9,2)
NOT
NULL
DEFAULT
0,
--订单总共需要支付费用
OrderStatus
INT
NOT
NULL
DEFAULT
0,
--订单状态(未支付,已支付)
AddTime DATETIME
NOT
NULL
DEFAULT
GETDATE(),
--订单提交时间
PayTime DATETIME
NULL
--订单支付时间
)
|
注:因为是测试,所以这里简化了这些表。
因为扣除账户余额是一个公用的,最小单元的业务逻辑,所以我们专门建立一个存储过程来执行该过程。代码如下:
Create
Proc [dbo].[Sp_UserMoneyLess]
@UserName NVARCHAR(50),
--用户名
@ChangeMoney
DECIMAL
(9,2),
--变动金额
@ChangeType
INT
,
--账户变动类型
@Result
INT
OUTPUT
,
--输出参数,执行结果
@Msg NVARCHAR(100)
OUTPUT
--执行结果描述
AS
--为输出参数设置默认值
SET
@Result = 1
SET
@Msg =
'执行成功'
DECLARE
@before
DECIMAL
(9,2),@
after
DECIMAL
(9,2)
--开启事务
BEGIN
TRAN UserMoneyLess
SELECT
@before=tu.UserMoney
FROM
T_Users tu
WHERE
tu.UserName=@UserName
SET
@
after
=@before-@ChangeMoney
IF @
after
<0
BEGIN
SET
@Result=-1
SET
@Msg =
'账户余额不足'
ROLLBACK
TRAN UserMoneyLess
RETURN
END
--更新会员账户余额
UPDATE
T_Users
SET
UserMoney = @
after
WHERE
UserName = @UserName
IF @@ERROR<>0
BEGIN
SET
@Result = -2
SET
@Msg =
'更新账户余额发生异常,异常信息:'
+ ERROR_MESSAGE()
ROLLBACK
TRAN UserMoneyLess
RETURN
END
--产生账户变动记录
INSERT
INTO
T_MoneyLog
(
UserName,
ChangeType,
MoneyBefore,
ChangeMoney,
MoneyAfter,
Remark,
AddTime
)
VALUES
(
@UserName,
@ChangeType,
@before,
@ChangeMoney,
@
after
,
'支付订单扣除账户余额'
,
GETDATE()
)
IF @@ERROR<>0
BEGIN
SET
@Result = -3
SET
@Msg =
'产生账户变动记录发生异常,异常信息:'
+ ERROR_MESSAGE()
ROLLBACK
TRAN UserMoneyLess
END
COMMIT
TRAN UserMoneyLess
|
现在我们来测试一下我们的Sp_UserMoneyLess,在测试之前,我们插入一些测试数据:
INSERT
INTO
T_Users
(
UserName,
UserMoney
)
VALUES
(
'Geodon'
,
100
)
INSERT
INTO
T_Order
(
-- Id -- this column value is auto-generated
OrderId,
UserName,
SumMoney,
OrderStatus,
AddTime
)
VALUES
(
'20130303160545612'
,
'Geodon'
,
120,
0,
GETDATE()
)
|
好了,测试数据已经准备完毕。现在我们可以开始我们的测试了,执行下面的测试代码:
DECLARE
@Result
INT
,@Msg NVARCHAR(200)
EXEC
Sp_UserMoneyLess
'Geodon'
,120,1,@Result
OUTPUT
,@Msg
OUTPUT
SELECT
@Result,@Msg
|
运行结果:
Ok没问题,是我们想要的结果。
再进行一次测试,测试余额足够的情况,执行下面的测试代码:
DECLARE
@Result
INT
,@Msg NVARCHAR(200)
EXEC
Sp_UserMoneyLess
'Geodon'
,10,1,@Result
OUTPUT
,@Msg
OUTPUT
SELECT
@Result,@Msg
|
运行结果:
我们查询一下执行结果
Ok没问题,正常执行。
接下来我们再为订单支付创建一个存储过程,代码如下:
CREATE
PROC Sp_PayOrder
@OrderId
VARCHAR
(20),
--订单号
@UserName NVARCHAR(50),
--用户名
@Result
INT
OUTPUT
,
--支付结果
@Msg NVARCHAR(100)
OUTPUT
--支付结果描述
AS
--为输出参数设置默认值
SET
@Result=1
SET
@Msg=
'执行成功'
--查询订单需要支付的金额,如果订单号不存在或者该订单支付过,返回-1,停止执行
DECLARE
@orderMoney
DECIMAL
(9,2)
SELECT
@orderMoney = to1.SumMoney
FROM
T_Order to1
WHERE
to1.OrderId=@OrderId
AND
to1.OrderStatus=0
AND
to1.UserName=@UserName
IF @orderMoney
IS
NULL
BEGIN
SET
@Result=-1
SET
@Msg=
'订单号不存在或者该订单支付过'
RETURN
END
--开启事务
BEGIN
TRAN PayOrder
--扣除会员账户余额
EXEC
Sp_UserMoneyLess @UserName,@orderMoney,1,@Result
OUTPUT
,@Msg
OUTPUT
IF @Result<>1
BEGIN
SET
@Result=-2
ROLLBACK
TRAN PayOrder
RETURN
END
--更新订单支付状态
UPDATE
T_Order
SET
OrderStatus = 1
WHERE
OrderId=@OrderId
AND
OrderStatus=0
COMMIT
TRAN PayOrder
|
这个存储过程包含了一个事务,而且在事务的内部又调用了Sp_UserMoneyLess,而Sp_UserMoneyLess这个存储过程内部又包含了一个事务,这就出现了事务嵌套的场景,这也正是本文要讨论的内容。
我们现在进行测试,执行如下测试代码:
DECLARE
@Result
INT
,@Msg NVARCHAR(200)
EXEC
Sp_PayOrder
'20130303160545612'
,
'Geodon'
,@Result
OUTPUT
,@Msg
OUTPUT
SELECT
@Result,@Msg
|
运行结果如下:
大家可以看到,执行存储过程出现了错误
我们先来看一下第一个错误
可以看的出,这个错误是在内部事务回滚带有名称的事务的时候,发现没有该名称的事务或保存点,因为会引发这个异常,为什么会这样呢?大家看一下微软的解释:
ROLLBACK TRANSACTION 语句的 transaction_name 参数引用一组命名嵌套事务的内部事务是非法的,transaction_name 只能引用最外部事务的事务名称。
哦!原来Sql Server不允许我们在内部的事务中包含事务名称。那好,我们现在就把这个事务名称去掉,代码修改如下:
ALTER
PROC [dbo].[Sp_UserMoneyLess]
@UserName NVARCHAR(50),
--用户名
@ChangeMoney
DECIMAL
(9,2),
--变动金额
@ChangeType
INT
,
--账户变动类型
@Result
INT
OUTPUT
,
--输出参数,执行结果
@Msg NVARCHAR(100)
OUTPUT
--执行结果描述
AS
--为输出参数设置默认值
SET
@Result = 1
SET
@Msg =
'执行成功'
DECLARE
@before
DECIMAL
(9,2),@
after
DECIMAL
(9,2)
--开启事务
BEGIN
TRAN
SELECT
@before=tu.UserMoney
FROM
T_Users tu
WHERE
tu.UserName=@UserName
SET
@
after
=@before-@ChangeMoney
IF @
after
<0
BEGIN
SET
@Result=-1
SET
@Msg =
'账户余额不足'
ROLLBACK
TRAN
RETURN
END
--更新会员账户余额
UPDATE
T_Users
SET
UserMoney = @
after
WHERE
UserName = @UserName
IF @@ERROR<>0
BEGIN
SET
@Result = -2
SET
@Msg =
'更新账户余额发生异常,异常信息:'
+ ERROR_MESSAGE()
ROLLBACK
TRAN
RETURN
END
--产生账户变动记录
INSERT
INTO
T_MoneyLog
(
UserName,
ChangeType,
MoneyBefore,
ChangeMoney,
MoneyAfter,
Remark,
AddTime
)
VALUES
(
@UserName,
@ChangeType,
@before,
@ChangeMoney,
@
after
,
'支付订单扣除账户余额'
,
GETDATE()
)
IF @@ERROR<>0
BEGIN
SET
@Result = -3
SET
@Msg =
'产生账户变动记录发生异常,异常信息:'
+ ERROR_MESSAGE()
ROLLBACK
TRAN
END
COMMIT
TRAN
|
再次执行测试代码:
DECLARE
@Result
INT
,@Msg NVARCHAR(200)
EXEC
Sp_PayOrder
'20130303160545612'
,
'Geodon'
,@Result
OUTPUT
,@Msg
OUTPUT
SELECT
@Result,@Msg
|
从结果中我们发现除了上次的第二个错误之外,又产生了另外一个错误:
为什么又出现了这个错误呢?从这个错误我们可以分析出错误的原因是在外部的事务中回滚事务的时候没有找到对应的Begin Tran,可我们的代码中明明有 Begin Tran PayOrder啊,为什么还会出现这个错误呢?在此我找到了微软的解释:
如果在一组嵌套事务的任意级别执行使用外部事务名称的 ROLLBACK TRANSACTION transaction_name 语句,那么所有嵌套事务都将回滚。如果在一组嵌套事务的任意级别执行没有transaction_name 参数的 ROLLBACK WORK 或 ROLLBACK TRANSACTION 语句,那么所有嵌套事务都将回滚,包括最外部事务。
原来内部的事务中如果执行了没有事务名称的回滚,会将所有的嵌套事务,包括最外层的事务都回滚。那怎么办呢?难道我们不能使用嵌套事务?你可以能会说:不可能啊,应该是可以的啊!呵呵,是的,我们当然可以使用嵌套事务。解决这个问题有两种方法:
第一种:利用Commit Tran的原理,内部事务任何时候进行Commit tran,不管数据异常与否,如果出现异常数据,返回异常数据提示就可以。
这种方法来源于微软的一段解释:
SQL Server 数据库引擎将忽略内部事务的提交。根据最外部事务结束时采取的操作,将提交或者回滚内部事务。如果提交外部事务,也将提交内部嵌套事务。如果回滚外部事务,也将回滚所有内部事务,不管是否单独提交过内部事务。
也就是说,最终的事务是否提交,决定权在外部的事务,即使内部事务进行了提交,只要外部事务根据内部返回的值来决定提交或者回滚,就可以把外部和所有嵌套的事务都提交或者回滚。
好了,我们把代码修改如下:
ALTER
PROC [dbo].[Sp_UserMoneyLess]
@UserName NVARCHAR(50),
--用户名
@ChangeMoney
DECIMAL
(9,2),
--变动金额
@ChangeType
INT
,
--账户变动类型
@Result
INT
OUTPUT
,
--输出参数,执行结果
@Msg NVARCHAR(100)
OUTPUT
--执行结果描述
AS
--为输出参数设置默认值
SET
@Result = 1
SET
@Msg =
'执行成功'
DECLARE
@before
DECIMAL
(9,2),@
after
DECIMAL
(9,2)
--开启事务
BEGIN
TRAN
SELECT
@before=tu.UserMoney
FROM
T_Users tu
WHERE
tu.UserName=@UserName
SET
@
after
=@before-@ChangeMoney
IF @
after
<0
BEGIN
SET
@Result=-1
SET
@Msg =
'账户余额不足'
COMMIT
TRAN
RETURN
END
--更新会员账户余额
UPDATE
T_Users
SET
UserMoney = @
after
WHERE
UserName = @UserName
IF @@ERROR<>0
BEGIN
SET
@Result = -2
SET
@Msg =
'更新账户余额发生异常,异常信息:'
+ ERROR_MESSAGE()
COMMIT
TRAN
RETURN
END
--产生账户变动记录
INSERT
INTO
T_MoneyLog
(
UserName,
ChangeType,
MoneyBefore,
ChangeMoney,
MoneyAfter,
Remark,
AddTime
)
VALUES
(
@UserName,
@ChangeType,
@before,
@ChangeMoney,
@
after
,
'支付订单扣除账户余额'
,
GETDATE()
)
IF @@ERROR<>0
BEGIN
SET
@Result = -3
SET
@Msg =
'产生账户变动记录发生异常,异常信息:'
+ ERROR_MESSAGE()
END
COMMIT
TRAN
|
然后我们再次执行订单支付的模拟:
DECLARE
@Result
INT
,@Msg NVARCHAR(200)
EXEC
Sp_PayOrder
'20130303160545612'
,
'Geodon'
,@Result
OUTPUT
,@Msg
OUTPUT
SELECT
@Result,@Msg
|
运行结果
Ok,出现了我们想要的结果。
第二种,利用事务保存点来解决。
该方法利用在内部嵌套中增加一个事务保存点(Save Tran savepoint_name),然后在内部嵌套中需要进行回滚的地方执行Rollback Tran savepoint_name这样,就可以把事务回滚到savepoint_name这个保存点,好了,了解了原理,我们修改代码如下:
ALTER
PROC [dbo].[Sp_UserMoneyLess]
@UserName NVARCHAR(50),
--用户名
@ChangeMoney
DECIMAL
(9,2),
--变动金额
@ChangeType
INT
,
--账户变动类型
@Result
INT
OUTPUT
,
--输出参数,执行结果
@Msg NVARCHAR(100)
OUTPUT
--执行结果描述
AS
--为输出参数设置默认值
SET
@Result = 1
SET
@Msg =
'执行成功'
DECLARE
@before
DECIMAL
(9,2),@
after
DECIMAL
(9,2)
--开启事务
SAVE TRAN UserMoneyLess
SELECT
@before=tu.UserMoney
FROM
T_Users tu
WHERE
tu.UserName=@UserName
SET
@
after
=@before-@ChangeMoney
IF @
after
<0
BEGIN
SET
@Result=-1
SET
@Msg =
'账户余额不足'
ROLLBACK
TRAN UserMoneyLess
RETURN
END
--更新会员账户余额
UPDATE
T_Users
SET
UserMoney = @
after
WHERE
UserName = @UserName
IF @@ERROR<>0
BEGIN
SET
@Result = -2
SET
@Msg =
'更新账户余额发生异常,异常信息:'
+ ERROR_MESSAGE()
ROLLBACK
TRAN UserMoneyLess
RETURN
END
--产生账户变动记录
INSERT
INTO
T_MoneyLog
(
UserName,
ChangeType,
MoneyBefore,
ChangeMoney,
MoneyAfter,
Remark,
AddTime
)
VALUES
(
@UserName,
@ChangeType,
@before,
@ChangeMoney,
@
after
,
'支付订单扣除账户余额'
,
GETDATE()
)
IF @@ERROR<>0
BEGIN
SET
@Result = -3
SET
@Msg =
'产生账户变动记录发生异常,异常信息:'
+ ERROR_MESSAGE()
ROLLBACK
TRAN UserMoneyLess
END
|
大家可以看到我们在原来事务起始的地方,增加了一个事务保存点SAVE TRAN UserMoneyLess,然后在下面回滚的地方,加上了保存点名称。
再次执行测试代码:
DECLARE
@Result
INT
,@Msg NVARCHAR(200)
EXEC
Sp_PayOrder
'20130303160545612'
,
'Geodon'
,@Result
OUTPUT
,@Msg
OUTPUT
SELECT
@Result,@Msg
|
结果:
Ok,是我们想要的结果。
上面两种方法解决了事务嵌套的问题。当然用上述的两种方法所创建的嵌套存储过程如果想要单独使用,必须在外层嵌套一层事务或者在业务层调用这种存储的时候,加上事务,各个ORM中都有事务的功能,如果你还在用DbHelper,也可以自己封装一个事务处理类,在业务逻辑层进行事务处理。