嵌套事务与TransactionScope
2016年3月31日嵌套的数据库事务可能有点反直觉，如果内层事务回滚，语法上的处在外层事务中的操作无法继续进行。
按照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
private void Default_Load(object sender, EventArgs e)
{
List<ResultRow> list = new List<ResultRow>();
foreach (var innerScopeOption_loopVariable in {TransactionScopeOption.Required, TransactionScopeOption.RequiresNew})
{
innerScopeOption = innerScopeOption_loopVariable;
foreach (var 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; }
}default.aspx
<%@ 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>
8) 8) 8) 8) 8) 8)
😀 😀 😀 😀