【茶包射手日記】用 TransactionScope 包 LINQ 迴圈查詢出錯

使用者報案某網頁功能故障,經抽絲剝繭鎖定爆炸點在一段「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 是影響最小的做法。

歡迎推文分享:
Published 26 April 2018 10:18 PM 由 Jeffrey
Filed under: ,
Views: 2,170



意見

沒有意見

你的看法呢?

(必要的) 
(必要的) 
(選擇性的)
(必要的) 
(提醒: 因快取機制,您的留言幾分鐘後才會顯示在網站,請耐心稍候)

5 + 3 =

搜尋

Go

<April 2018>
SunMonTueWedThuFriSat
25262728293031
1234567
891011121314
15161718192021
22232425262728
293012345
 
RSS
創用 CC 授權條款
【廣告】
twMVC

Tags 分類檢視
關於作者

一個醉心技術又酷愛分享的Coding魔人,十年的IT職場生涯,寫過系統、管過專案, 也帶過團隊,最後還是無怨無悔地選擇了技術鑽研這條路,近年來則以做一個"有為的中年人"自許。

文章典藏
其他功能

這個部落格


Syndication