嵌套事务与TransactionScope

嵌套的数据库事务可能有点反直觉,如果内层事务回滚,语法上的处在外层事务中的操作无法继续进行。

按照Handling SQL Server Errors in Nested Procedures,创建事务会令@@TRANCOUNT加1,提交事务@@TRANCOUNT减1,回滚事务重置@@TRANCOUNT为0。所以提交与回滚不是对称的。

System.Transactions.TransactionScope是推荐使用的事务管理方法,它的构造函数接受TransactionScopeOption以指定如何创建事务。下表指示了在嵌套的、以不同方式创建的隐式事务中使用回滚和提交的结果。

内层事务 内层操作 外层操作 内层操作的值被保留 外层操作的值被保留 异常
Required 提交 提交 True True
Required 提交 回滚 False False
Required 回滚 提交 False False 执行内层事务后无法继续执行外层事务。
The operation is not valid for the state of the transaction.
Required 回滚 回滚 False False 执行内层事务后无法继续执行外层事务。
The operation is not valid for the state of the transaction.
RequiresNew 提交 提交 True True
RequiresNew 提交 回滚 True False
RequiresNew 回滚 提交 False True
RequiresNew 回滚 回滚 False False

从此表可发现,Requires会检查是否已经处于事物环境(是否存在环境事务),如果是则不再创建新事物,相当于没有using (TransactionScope tx = new TransactionScope());如果没有环境事务,才创建事务。

RequiresNew一定创建一个新的事务,并且如果已经存在环境事务,新创建的事务脱离于原有的事物环境。即使代码“语法上的”外层事务回滚,也跟RequiresNew创建的事务没有关系。

default.aspx.vb

[code lang=”CSharp”]
private void Default_Load(object sender, EventArgs e)
{
List<ResultRow> list = new List<ResultRow>();

foreach (void innerScopeOption_loopVariable in {TransactionScopeOption.Required, TransactionScopeOption.RequiresNew})
{
innerScopeOption = innerScopeOption_loopVariable;
foreach (void canInnerCommit_loopVariable in {true,false })
{
canInnerCommit = canInnerCommit_loopVariable;
foreach (var canOuterCommit_loopVariable in {true,false})
{
canOuterCommit = canOuterCommit_loopVariable;

using (CSQLDBUtils db = new CSQLDBUtils(OpConsoleLib.std.CONNECTIONSTRING))
{db.executeNonQuery("Delete from TestTable");}

ResultRow row = new ResultRow
{
InnerCommit = canInnerCommit,
OuterCommit = canOuterCommit,
InnerScope = innerScopeOption
};
using (TransactionScope tx = new TransactionScope())
{
InnerTransaction(innerScopeOption, canInnerCommit)

try
{
using (CSQLDBUtils db = new CSQLDBUtils(OpConsoleLib.std.CONNECTIONSTRING))
{
db.executeNonQuery("Insert into TestTable Values(‘Outer’)");
}
}
catch (Exception ex)
{
row.ErrorMessage = "Unable to run outer query after inner method. <br/>" + ex.Message;
goto TestFinished;
}

if (canOuterCommit)
tx.Complete();
}
TestFinished:

SetResult(row);
list.Add(row);
}
}
}
ListView1.DataSource = list;
ListView1.DataBind();
}

private void InnerTransaction(TransactionScopeOption scopeOption, bool canCommit)
{
using (TransactionScope tx = new TransactionScope(scopeOption))
{
using (CSQLDBUtils db = new CSQLDBUtils(OpConsoleLib.std.CONNECTIONSTRING))
{
db.executeNonQuery("Insert into TestTable Values(‘Inner’)");
}
if (canCommit)
tx.Complete();
}
}

private void SetResult(ResultRow row)
{
using (CSQLDBUtils db = new CSQLDBUtils(OpConsoleLib.std.CONNECTIONSTRING))
{
if (Convert.ToInt32(db.getScalerValue("select count(*) from TestTable where name=’Inner’")) == 1)
row.IsInnerValueKept = true;
else
row.IsInnerValueKept = false;

if (Convert.ToInt32(db.getScalerValue("select count(*) from TestTable where name=’Outer’")) == 1)
row.IsOuterValueKept = true;
else
row.IsOuterValueKept = false;
}
}

public class ResultRow
{

public TransactionScopeOption InnerScope { get; set; }
public bool IsInnerValueKept { get; set; }
public bool InnerCommit { get; set; }
public bool IsOuterValueKept { get; set; }
public bool OuterCommit { get; set; }
public string ErrorMessage { get; set; }
}
[/code]

default.aspx

[code lang=”html”]
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="Default"%>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<link href="css/bootstrap.css" rel="stylesheet" />
<script src="js/bootstrap.js"></script>

<style type="text/css">
.inner {
color: rgb(183, 76, 148);
}

.outer {
color: #254C70;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:ListView ID="ListView1" runat="server">
<LayoutTemplate>
<table class="table table-striped">
<thead>
<tr>
<th class="inner">内层事务</th>
<th class="inner">内层操作</th>
<th class="outer">外层操作</th>
<th class="inner">内层操作的值被保留</th>
<th class="outer">外层操作的值被保留</th>
<th>异常</th>
</tr>
</thead>
<tbody>
<asp:PlaceHolder ID="itemPlaceHolder" runat="server" />
</tbody>
</table>
</LayoutTemplate>
<ItemTemplate>
<tr>
<td class="inner"><%# DirectCast(Container.DataItem, ResultRow).InnerScope.ToString() %></td>
<td class="inner"><%# If(DirectCast(Container.DataItem, ResultRow).InnerCommit, "提交", "回滚") %></td>
<td class="outer"><%# If(DirectCast(Container.DataItem, ResultRow).OuterCommit, "提交", "回滚") %></td>
<td class="inner"><%# DirectCast(Container.DataItem, ResultRow).IsInnerValueKept %></td>
<td class="outer"><%# DirectCast(Container.DataItem, ResultRow).IsOuterValueKept %></td>
<td><%# DirectCast(Container.DataItem, ResultRow).ErrorMessage %></td>
</tr>
</ItemTemplate>
</asp:ListView>

</div>
</form>
</body>
</html>
[/code]

发表评论

电子邮件地址不会被公开。

:wink: :twisted: :roll: :oops: :mrgreen: :lol: :idea: :evil: :cry: :arrow: :?: :-| :-x :-o :-P :-D :-? :) :( :!: 8-O 8)