10 理解事务

10.1 什么是事务

10.2 创建事务

10.2.1 使用T-SQL创建事务

 

SET XACT_ABORT ON
BEGIN TRY
    BEGIN TRANSACTION
    --work code here
    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION
    --cleanup code
END CATCH

 

10.2.2 使用DbTransaction对象创建事务

 

Imports System.Configuration
Imports System.Data.SqlClient
Public Class Form1
    Private Sub Button1_Click(ByVal sender As System.Object,
                              ByVal e As System.EventArgs) Handles Button1.Click
        Dim cnSetting As ConnectionStringSettings =
            ConfigurationManager.ConnectionStrings("NorthwindString")
        Using cn As New SqlConnection
            cn.ConnectionString = cnSetting.ConnectionString
            cn.Open()
            Using tran As SqlTransaction = cn.BeginTransaction
                Try
                    'work code here
                    Using cmd As SqlCommand = cn.CreateCommand
                        cmd.Transaction = tran
                        cmd.CommandText = "SELECT COUNT(*) FROM employees"
                        Dim count As Integer = CInt(cmd.ExecuteScalar)
                        MessageBox.Show(count.ToString)
                    End Using
                    'if we made it this far, commit
                    tran.Commit()
                Catch ex As Exception
                    tran.Rollback()
                    'cleanup code
                    MessageBox.Show(ex.Message)
                End Try
            End Using
        End Using
    End Sub
End Class

 

 

10.3 设置事务的孤立级别

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

 

添加查询提示设置事务的孤立级别

SELECT * FROM CUSTOMERS WITH (NOLOCK)

 

在SqlTransaction类上设置事务孤立级别。

    Private Sub Button2_Click(ByVal sender As System.Object,
                              ByVal e As System.EventArgs) Handles Button2.Click
        Dim cnSetting As ConnectionStringSettings =
            ConfigurationManager.ConnectionStrings("NorthwindString")
        Using cn As New SqlConnection
            cn.ConnectionString = cnSetting.ConnectionString
            cn.Open()
            Using tran As SqlTransaction =
                cn.BeginTransaction(System.Data.IsolationLevel.Serializable)
                Try
                    'work code here
                    Using cmd As SqlCommand = cn.CreateCommand
                        cmd.Transaction = tran
                        cmd.CommandText = "SELECT COUNT(*) FROM employees"
                        Dim count As Integer = CInt(cmd.ExecuteScalar)
                        MessageBox.Show(count.ToString)
                    End Using
                    'if we made it this far, commit
                    tran.Commit()
                Catch ex As Exception
                    tran.Rollback()
                    'cleanup code
                    MessageBox.Show(ex.Message)
                End Try
            End Using
        End Using
    End Sub

  

10.4 System.Transactions命名空间

10.4.1 使用TransactionScope类创建事务

 

Imports System.Transactions

    Private Sub Button3_Click(ByVal sender As System.Object,
                              ByVal e As System.EventArgs) Handles Button3.Click
        Dim cnSetting As ConnectionStringSettings =
            ConfigurationManager.ConnectionStrings("NorthwindString")
        Using ts As New TransactionScope
            Using cn As New SqlConnection
                cn.ConnectionString = cnSetting.ConnectionString
                cn.Open()
                'work code here
                Using cmd As SqlCommand = cn.CreateCommand
                    cmd.CommandText = "SELECT COUNT(*) FROM employees"
                    Dim count As Integer = CInt(cmd.ExecuteScalar)
                    MessageBox.Show(count.ToString)
                End Using
                'if we made it this far, commit
                ts.Complete()
            End Using
        End Using
    End Sub

 

10.4.2 设置事务选项

 

    Private Sub Button4_Click(ByVal sender As System.Object,
                              ByVal e As System.EventArgs) Handles Button4.Click
        Dim cnSetting As ConnectionStringSettings =
            ConfigurationManager.ConnectionStrings("NorthwindString")
        Dim opt As New TransactionOptions
        opt.IsolationLevel = IsolationLevel.Serializable
        Using ts As New TransactionScope(TransactionScopeOption.Required, opt)
            Using cn As New SqlConnection
                cn.ConnectionString = cnSetting.ConnectionString
                cn.Open()
                'work code here
                Using cmd As SqlCommand = cn.CreateCommand
                    cmd.CommandText = "SELECT COUNT(*) FROM employees"
                    Dim count As Integer = CInt(cmd.ExecuteScalar)
                    MessageBox.Show(count.ToString)
                End Using
            End Using
            'if we made it this far, commit
            ts.Complete()
        End Using
    End Sub

 

10.4.3 处理分布式事务

10.4.4 建立自己的事务资源管理器