Rock的SQL筆記本

失敗原因是 OLE DB 提供者 “MSOLEDBSQL” 無法開始分散式交易。看到這錯誤訊息及他貼給我的SQL語法後，我直覺先將該Linked Server的remote proc transaction promotion設為False，然後再請他測試。他測試後表示錯誤還是一樣(類似下圖狀況，用SSMS模擬)。

這狀況就有點奇特，該語法他也沒開交易然後remote proc transaction promotion我也關了，為何SQL Server還是會自己啟用分散式交易呢?但該語法在SSMS執行卻不會報錯(如下圖)，只在AP執行會產出該錯誤訊息。

 

因此我只好開啟Profiler後請他在AP執行該報表來查查前端到底丟了那些語法過來。在Profiler中看見Login訊息中顯示該Session的預設ISOLATION LEVEL居然是SERIALIZABLE而不是SQL預設的READ COMMITTED。

 

因此我在SSMS中將ISOLATION LEVEL改為SERIALIZABLE後再執行該語法，果然產出一樣的錯誤。因此可以推斷交易層級升級導致利用Insert Into #tmp Exec(@SQL)方式拉資料時會讓SQL Server背地啟用分散式交易。

至於為何Login會是SERIALIZABLE是因為前端EF底層在啟用交易時如沒指定交易層級時，預設會使用SERIALIZABLE。再請同仁設定EF開交易時改用READ COMMITTED後，這問題就解決了。

由於吃過蠻多次Linked Server的虧，後續我將remote proc transaction promotion及ISOLATION LEVEL跟Transaction當成3個變數，然後根據各組合模式來測試SQL Server是否會啟用分散式交易，語法如下。

--變數一:transaction promotion
--EXEC master.dbo.sp_serveroption 
--@server=N'Srv', 
--@optname=N'remote proc transaction promotion', 
--@optvalue=N'true'


--變數二:隔離層級
--SET TRANSACTION ISOLATION LEVEL Read Committed
--SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

--變數三:啟用交易
BEGIN TRANSACTION;
	Declare @SQL Varchar(4000);
	Drop Table If Exists #tb1;
	Create Table #tb1(id int,cname nvarchar(10));
	SET @SQL='SELECT * From Ext.dbo.tb1';
	SET @SQL='SELECT * FROM OPENQUERY([Srv], ''' + REPLACE(@SQL, '''', '''''') + ''')';
	--INSERT INTO #tb1 EXEC(@SQL);
	--INSERT INTO #tb1 SELECT * FROM OPENQUERY([Srv],'SELECT * From Ext.dbo.tb1');
	--EXECUTE sp_executesql N'INSERT INTO #tb1 SELECT * FROM OPENQUERY([Srv],''SELECT * From Ext.dbo.tb1'')';
	--INSERT INTO #tb1 EXECUTE sp_executesql N'SELECT * FROM OPENQUERY([Srv],''SELECT * From Ext.dbo.tb1'')';
	EXECUTE [Srv].[Ext].[dbo].[sp1];
	Select * From #tb1;
COMMIT;

 

測試結果如下圖:

	remote proc  transaction promotion	Transaction Isolation Level	Begin Transaction	INSERT INTO #tb1  EXEC(@SQL)	INSERT INTO #tb1  SELECT * FROM  OPENQUERY	EXECUTE  sp_executesql	INSERT INTO #tb1  EXECUTE sp_executesql	EXECUTE  [Srv].[DB].dbo.sp1
1	TRUE	Read Committed	Yes	Y	Y	Y	Y	N(無法分散式交易)
2	TRUE	Read Committed	No	Y	Y	Y	Y	Y
3	TRUE	SERIALIZABLE	Yes	N(無法分散式交易)	N(無法分散式交易)	N(無法分散式交易)	N(無法分散式交易)	N(無法分散式交易)
4	TRUE	SERIALIZABLE	No	N(無法分散式交易)	Y	Y	N(無法分散式交易)	Y
5	FALSE	Read Committed	Yes	Y	Y	Y	Y	Y
6	FALSE	Read Committed	No	Y	Y	Y	Y	Y
7	FALSE	SERIALIZABLE	Yes	N(無法分散式交易)	N(無法分散式交易)	N(無法分散式交易)	N(無法分散式交易)	Y
8	FALSE	SERIALIZABLE	No	N(無法分散式交易)	Y	Y	N(無法分散式交易)	Y

我是ROCK

rockchang@mails.fju.edu.tw