【茶包射手日記】用 TransactionScope 包 LINQ 迴圈查詢出錯
0 |
使用者報案某網頁功能故障,經抽絲剝繭鎖定爆炸點在一段「TransactionScope 中以 LINQ to SQL 取回物件集合,跑 foreach 開連線查資料庫」邏輯(警語:跑迴圈執行資料庫查詢,迴圈次數如過高將成效能殺手,宜避免),簡化為以下程式片段可重現問題:(註: 程式碼經過簡化,在 foreach 迴圈裡不斷開啟資料庫連線看起來很突兀,但實際案例中 foreach 迴圈是呼叫外部程式庫的共用函式,要追進去才會知道其中開了連線查詢資料庫)
using (var tx = new TransactionScope())
{
using (var db = GetLinqToSqlDataContext())
{
//using (var tx2 = new TransactionScope(TransactionScopeOption.Suppress))
{
var datas = from o in db.SomeTable
where o.Col1 == "A" || o.Col1 == "B"
select o;
foreach (var q in datas)
{
using (var cn = new SqlConnection(cs))
{
cn.Open();
var cmd = cn.CreateCommand();
cmd.CommandText = "select getdate() as d";
var dr = cmd.ExecuteReader();
dr.Read();
Response.Write(dr[0]);
}
}
}
}
}
錯誤訊息為:
[SqlException (0x80131904): There is already an open DataReader associated with this Command which must be closed first.]
System.Data.SqlClient.SqlInternalConnectionTds.ExecuteTransactionYukon(TransactionRequest transactionRequest, String transactionName, IsolationLevel iso, SqlInternalTransaction internalTransaction, Boolean isDelegateControlRequest) +396
System.Data.SqlClient.SqlInternalConnectionTds.ExecuteTransaction(TransactionRequest transactionRequest, String name, IsolationLevel iso, SqlInternalTransaction internalTransaction, Boolean isDelegateControlRequest) +146
System.Data.SqlClient.SqlDelegatedTransaction.Promote() +134
[TransactionPromotionException: Failure while attempting to promote transaction.]
System.Data.SqlClient.SqlDelegatedTransaction.Promote() +756746
System.Transactions.TransactionStatePSPEOperation.PSPEPromote(InternalTransaction tx) +63
System.Transactions.TransactionStateDelegatedBase.EnterState(InternalTransaction tx) +177
[TransactionAbortedException: The transaction has aborted.]
System.Transactions.TransactionStateAborted.CheckForFinishedTransaction(InternalTransaction tx) +11
System.Transactions.EnlistableStates.Promote(InternalTransaction tx) +25
System.Transactions.Transaction.Promote() +61
System.Transactions.TransactionInterop.ConvertToOletxTransaction(Transaction transaction) +46
System.Transactions.TransactionInterop.GetExportCookie(Transaction transaction, Byte[] whereabouts) +193
System.Data.SqlClient.SqlInternalConnection.GetTransactionCookie(Transaction transaction, Byte[] whereAbouts) +35
System.Data.SqlClient.SqlInternalConnection.EnlistNonNull(Transaction tx) +450
System.Data.SqlClient.SqlInternalConnection.Enlist(Transaction tx) +4889946
System.Data.SqlClient.SqlInternalConnectionTds.Activate(Transaction transaction) +4890005
System.Data.ProviderBase.DbConnectionInternal.ActivateConnection(Transaction transaction) +33
System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +1286
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +65
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +117
System.Data.SqlClient.SqlConnection.Open() +122
ASP.testdtc_aspx.Page_Load(Object sender, EventArgs e) in X:\WWW\TestDTC.aspx:28
測試發現如將最外層的 TransactionScope 移掉,或將 foreach 用 new TransactionScope(TransactionScopeOption.Suppress) 包起來不參與交易就不會出錯。
初步推測錯誤原因:LINQ to SQL 查詢傳回的 System.Data.Linq.DataQuery<T> 跑 foreach 並不是一次讀完所有資料再跑迴圈,而是開著 DataReader 每跑一圈讀一筆,當處於 TransactionScope 交易範圍內,此時如試圖另外建立資料庫連線,連線將會升級參與分散式交易,過程中將觸發 There is already an open DataReader associated with this Command which must be closed first. 錯誤。依此原理,先 .ToList() 或 .ToArray() 再 foreach 也可解決問題,經實測也獲得驗證。
進一步推論,問題關鍵在 TransactionScope 中 DataReader 開啟狀態不能再開連線,故即使沒用 LINQ to SQL 也可能踩雷,例如以下程式範例,在 TransactionScope 內 while (dr.Read()) { 開DB連線; } 一樣能引爆,故在 Entity Framework 情境下也可能遇到。
using (var tx = new TransactionScope())
{
using (var cn1 = new SqlConnection(cs))
{
cn1.Open();
var cmd1 = cn1.CreateCommand();
cmd1.CommandText = "select 1 union select 2";
var dr1 = cmd1.ExecuteReader();
while (dr1.Read())
{
using (var cn2 = new SqlConnection(cs))
{
cn2.Open();
var cmd = cn2.CreateCommand();
cmd.CommandText = "select getdate() as d";
var dr2 = cmd.ExecuteReader();
dr2.Read();
Response.Write(dr2[0]);
}
}
}
}
爬文在 Stackoverflow 找到另一種解法,在連線字串加上 MultipleActiveResultSets=true 開啟 MARS 功能,可解除同時間單一 DataReader 限制避開問題,但有些副作用:
- 針對非 MARS 模式最佳化的老程式搭配 MARS 使用時效能稍差
- 同時執行多個批次作業時,你跟 SQL Server 會難以斷定 USE、SET、BEGIN TRAN、COMMIT、ROLLBACK 等指令的影響範圍
評估後,將 LINQ to SQL 查詢結果 .ToArray() 再 foreach 是影響最小的做法。
A case of foreach loop inside TransactionScope throws DataReader multiple open exception, using ToArray() or MutipleActiveResultSets parameter can solve the problem.
Comments
Be the first to post a comment