.net 跨数据库事务的实现

古老的ASP時代,要做Distributed Transaction(分散式交易,指跨越異質資料庫的交易,例如: 將SQL Server跟Oracle的更新動作包成一個Transaction),有個偷懶的方法。在ASP最前端宣告一下<%@ Transaction=Required%>,則整個ASP中的所有資料庫操作,不管Oracle、SQL、Sybase,通通會自動包成Transaction,不用多寫半行Code。

不過,這種寧 可錯殺一百,不可錯放一個的做法效能有點鳥(連沒必要的SELECT動作也被包入Transaction)。會寫VB COM的人多半會寫顆Support Transaction的COM+元件,用來執行特定的資料庫的更新。而多顆異質資料庫的Transactional元件可以再包出一個大 Transaction。不過,這得另外寫COM,註冊到COM+ Application中,多了些額外手續。

ASP.NET 1.x誕生後,針對分散式交易,提供了一個四不像的做法: 
寫一顆繼承自System.EnterpriseService.ServicedComponent的元件,內含更新資料庫的程式邏輯,再設定 TransactionAttribute,然後要Strong-Named/Signed,包上COM+的皮,註冊放入COM+ Application中。

呃... 好像比以前寫COM+還麻煩,堂堂的.NET還是得回頭靠COM+才能實踐分散式交易,會不會有點...

終於.NET 2.0中,針對分散式交易做出了改良。System.Transactions.TransactionScope讓大家有機會重回ASP時代的美妙時光,可以將任意一段資料庫操作包成一個Transaction,不需要額外的手工。例如以下的範例: (要Oracle支援Transaction,記得安裝Oracle Service For Microsoft Transaction Server,不然會百忙一場。)

    21 TransactionOptions options = new TransactionOptions();

    22 options.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;

    23 options.Timeout = new TimeSpan(0, 2, 0);

    24 using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, options))

    25 {

    26     try

    27      {

    28          using (SqlConnection sqlCn = new SqlConnection("Data Source=(local); User Id=scott; Password=tigger; Initial Catalog=Lab"))

    29           {

    30               SqlCommand cmd = new SqlCommand("INSERT INTO tblAccount (Account, Password, Username) VALUES (@acct, @pwd, @name)", sqlCn);

    31                cmd.Parameters.Add("@acct", SqlDbType.NVarChar).Value = "EMP" + DateTime.Now.ToString("HHmmss");

    32                cmd.Parameters.Add("@pwd", SqlDbType.NVarChar).Value = "PWD";

    33                cmd.Parameters.Add("@name", SqlDbType.NVarChar).Value = "NAME" + DateTime.Now.ToString("HHmmss");

    34                sqlCn.Open();

    35                cmd.ExecuteNonQuery();

    36           }

    37          //** 實測結果,ODP.NET 9207不Support TransactionScope,必須用.NET 2.0的System.Data.OracleClient

    38          using (OracleConnection oraCn = new OracleConnection("Data Source=MYORA; User Id=scott; Password=tigger;"))

    39           {

    40               OracleCommand cmd = new OracleCommand("INSERT INTO MYTABLE.tblAccount (Account, Password, Username) VALUES (:acct, :pwd, :name)", oraCn);

    41                cmd.Parameters.Add(":acct", OracleType.VarChar).Value = "EMP" + DateTime.Now.ToString("HHmmss")

    42                cmd.Parameters.Add(":pwd", OracleType.VarChar).Value = "PWD";

    43                cmd.Parameters.Add(":name", OracleType.VarChar).Value = "NAME" + DateTime.Now.ToString("HHmmss");

    44                oraCn.Open();

    45                cmd.ExecuteNonQuery();

    46           }

    47          //在scope.Complete();後才算Commit!

    48           scope.Complete();

    49      }

    50     catch (Exception ex)

    51      {

    52          //只要沒有scope.Complete(),先前的動作都會Rollback

    53           Response.Write(ex.Message);

    54      }

    55 }

很簡短吧? (有寫過ServicedComponent的人才能感受出它的好呀!!) 為了證實Transaction效果,我在48行設Breakpoint,則中斷未scope.Complete前,SQL的tblAccount會被鎖 定無法SELECT,而ORACLE中則SELECT不到新增的資料(可見SQL用的是Lock大法,ORACLE專攻 Snapshot);scope.Complete後,二者的新資料就都出現了。

同時,我還試 了故意ORACLE新增動作失敗或不做scope.Complete(),則SQL的tblAccount的新增資料就不會出現。由此,可以驗證以上的程 式的確實踐了分散式交易!! 而TransactionScope的確比.NET 1.x的ServicedComponent方便多了,大幅減少異質資料庫包成交易的複雜度。

我曾試著用ODP.NET 9207取代System.Data.OracleClient,測試結果顯示ODP.NET 9207無法參與TrasactionScope物件所建立的交易。後來找到Oracle的ODP.NET FAQ,提到了從ODP.NET 10.2.0.2.20起才支援.NET 2.0的System.Transactions,想用ODP.NET的人要留意。

最後還有一點要注意,如果你的SQL在遠端主機上,用的又是Windows 2003平台,則還有好幾個關節要打通。包含了MSDTC在Windows 2003 SP1上有些選項要調整。還有,你可能會連線失敗,並得到以下這類訊息:
Communication with the underlying transaction manager has failed. 
The transaction has already been implicitly or explicitly committed or aborted.

經驗中多半是防火牆的傑作,我的私房解法是在Windows Firewall上開放MSDTC.EXE程式的所有對外連線,問題就可解決。微軟有篇專題文章,介紹MSDTC與Firewall間的愛恨情仇,有興趣的人也可以去挖挖寶。

【一】加入System.Transactions參考
於專案中加入參考,選擇[.NET]頁籤,往下拖拉便可以看到System.Transactions如下圖:




【二】開啟MSDTC的服務
從[控制台]→[系統管理工具]→ 開啟[服務],可以看到有個【Distributed Transaction Coordinator】服務把它啟動
(PS:如果不啟動,程式執行時會發生【伺服器...上的MSDTC無法使用...】的訊息)

来源:http://eatpockyboy.blog.163.com/blog/static/116734640201010631937236/



展开阅读全文

数据库事务提交问题!!

10-15

在VB中通过调用COM+实现跨数据库的事务提交(引用COM+ Services Type Library),如果这两个数据库在同一网段内,则能正常运行,如果这两个数据库不在同一网段,则事务不能提交,每次都要回滚。其中一个函数的代码如下:rnrnPublic Function Set_DownFwFromDc(ByVal chrzzjgbm As String, ByVal DcZzjgbm As String, ByVal intgwlsh As Long, ByVal strConnOa As String, ByVal strConnDc As String)rn rn On Error GoTo ErrHandle:rn rn Dim ConnDc As New ADODB.Connectionrn Dim ConnOa As New ADODB.Connectionrn Dim RsDc As New ADODB.Recordsetrn Dim RsDc2 As New ADODB.Recordsetrn Dim RsOa As New ADODB.Recordsetrn Dim RsOa2 As New ADODB.Recordsetrn Dim strSql As Stringrn Dim intOagwlsh As Longrn Dim intgwfjlsh As Longrn rn #If value = 1 Thenrn Dim objContext As COMSVCSLib.ObjectContextrn Set objContext = GetObjectContext()rn objContext.EnableCommitrn #End Ifrn rn If ConnOa.State <> 0 Then ConnOa.Closern If ConnDc.State <> 0 Then ConnDc.Closern ConnOa.Open strConnOarn ConnDc.Open strConnDcrn rn '1、将重庆市工商局zfw、fjb表中的记录取到本单位OA的sbw、fjb中rn '1.1添加到sbwrn strSql = "select * from zfw where intgwlsh=" & CStr(intgwlsh)rn RsDc.Open strSql, ConnDc, adOpenForwardOnly, adLockReadOnlyrn If Not RsDc.EOF Thenrn '先取得sbw表的流水号rn strSql = "Execute prcGetAllLsh 'gwlsh'"rn Set RsOa2 = ConnOa.Execute(strSql)rn intOagwlsh = RsOa2.Fields(0).valuern RsOa2.Closern rn strSql = "Select top 0 * from sbw"rn RsOa.Open strSql, ConnOa, 1, 3rn RsOa.AddNewrn RsOa.Fields("intgwlsh").value = intOagwlshrn RsOa.Fields("chrgwz").value = RsDc.Fields("chrgwz").valuern RsOa.Fields("intgwnh").value = RsDc.Fields("intgwnh").valuern RsOa.Fields("intgwqh").value = RsDc.Fields("intgwqh").valuern RsOa.Fields("chrzzjgbm").value = Trim(DcZzjgbm)rn RsOa.Fields("dtmlwrq").value = Format(Now, "yyyy-mm-dd hh:mm")rn RsOa.Fields("chrlwbt").value = Trim(RsDc.Fields("chrgwbt").value)rn RsOa.Fields("intlwfs").value = 1rn RsOa.Fields("intmjbm").value = RsDc.Fields("intmjbm").valuern RsOa.Fields("intflbm").value = 0rn RsOa.Fields("chrlwsy").value = ""rn 'RsOa.Fields("txtzw").value = Nullrn RsOa.Fields("intsxh").value = 0rn 'RsOa.Fields("chrsjr").value = Nullrn 'RsOa.Fields("chrcbcs").value = Nullrn 'RsOa.Fields("chrcbr").value = Nullrn 'RsOa.Fields("txtcljg").value = Nullrn 'RsOa.Fields("dtmbjrq").value = Nullrn If IsNull(RsDc.Fields("chrztc")) = False Thenrn RsOa.Fields("chrztc").value = Trim(RsDc.Fields("chrztc").value)rn End Ifrn 'RsOa.Fields("txtlbyj").value = Nullrn RsOa.Fields("inthjcdbm").value = RsDc.Fields("inthjcdbm").valuern RsOa.Fields("chrtzfs").value = "1"rn RsOa.Fields("chrzzdz").value = "2"rn RsOa.Fields("chrqbbz").value = "1"rn 'RsOa.Fields("chrlwqfr").value = Nullrn 'RsOa.Fields("chrtsfjbz").value = Nullrn RsOa.Fields("chrGdbz").value = "0"rn RsOa.Updatern RsOa.Closern End Ifrn RsDc.Closern rn '1.2添加到fjbrn '正文稿的最后一稿rn strSql = "select top 1 * from fjb where intgwlsh=" & CStr(intgwlsh) & " and intfjbh=1 order by intbbbh desc"rn RsDc.Open strSql, ConnDc, adOpenForwardOnly, adLockReadOnlyrn Do While Not RsDc.EOFrn '先取得附件的流水号rn strSql = "Execute prcGetAllLsh 'intgwfjlsh'"rn Set RsOa2 = ConnOa.Execute(strSql)rn intgwfjlsh = RsOa2.Fields(0).valuern RsOa2.Closern rn strSql = "Select top 0 * from fjb"rn RsOa.Open strSql, ConnOa, 1, 3rn RsOa.AddNewrn RsOa.Fields("intgwfjlsh").value = intgwfjlshrn RsOa.Fields("intgwlsh").value = intOagwlshrn RsOa.Fields("chrfjlxbm").value = RsDc.Fields("chrfjlxbm").valuern RsOa.Fields("intfjbh").value = RsDc.Fields("intfjbh").valuern RsOa.Fields("chrfjmc").value = Trim(RsDc.Fields("chrfjmc").value)rn RsOa.Fields("txtfj").value = RsDc.Fields("txtfj").valuern RsOa.Fields("intbbbh").value = 1rn RsOa.Updatern RsOa.Closern' If IsNull(RsDc.Fields("txtfj").value) = False Thenrn' strSql = "select * from fjb where intgwfjlsh=" & CStr(intgwfjlsh)rn' RsOa.Open strSql, ConnOa, adOpenKeyset, adLockOptimisticrn' RsOa.Fields("txtfj").AppendChunk RsDc.Fields("txtfj").valuern' RsOa.Updatern' RsOa.Closern' End Ifrn RsDc.MoveNextrn Looprn RsDc.Closern rn '2、修改重庆市工商局fw的记录rn strSql = "update fw set chrxzbz='1' where intgwlsh=" & CStr(intgwlsh) & " and chrzzjgbm='" & Trim(chrzzjgbm) & "'"rn ConnDc.Execute (strSql)rnrn #If value = 1 Thenrn If Not objContext Is Nothing Then objContext.SetCompletern If Not objContext Is Nothing Then Set objContext = Nothingrn #End Ifrnrn Set_DownFwFromDc = 1rn rn Set RsDc = Nothingrn Set RsDc2 = Nothingrn Set RsOa = Nothingrn Set RsOa2 = Nothingrn If ConnOa.State <> 0 Then ConnOa.Closern If ConnDc.State <> 0 Then ConnDc.Closern Set ConnOa = Nothingrn Set ConnDc = Nothingrn rn Exit FunctionrnrnErrHandle:rn WriteWrongLog Now(), Err.Number, Err.Description, strSql, "Set_DownFwFromDc"rn Err.Clearrn #If value = 1 Thenrn If Not objContext Is Nothing Then objContext.SetAbortrn If Not objContext Is Nothing Then Set objContext = Nothingrn #End Ifrnrn Set_DownFwFromDc = 0rn rn Set RsDc = Nothingrn Set RsDc2 = Nothingrn Set RsOa = Nothingrn Set RsOa2 = Nothingrn If ConnOa.State <> 0 Then ConnOa.Closern If ConnDc.State <> 0 Then ConnDc.Closern Set ConnOa = Nothingrn Set ConnDc = Nothingrn rnEnd Function 论坛

没有更多推荐了,返回首页