Bir ADO.NET Uygulaması ile DeadLock Senaryosunun gerçekleştirilmesi

Print Versiyonu

Imports System.Data.SqlClient
Public Class SqlWorker

    Private con1 As New SqlConnection
    Private cmd1 As SqlCommand
    Private tran1 As SqlTransaction

    Public Sub New(ByVal name As String)
        con1.ConnectionString = "server=.;database=northwind;user id=sa;password=;Application Name=TransactionBaglantisi" & name & ";"
    End Sub

    Public Sub BeginTran()

        Try
            con1.Open()
            cmd1 = New SqlCommand("", con1)
            tran1 = con1.BeginTransaction
            cmd1.Transaction = tran1
        Catch ex As Exception
            RaiseEvent WorkerException(ex)
        End Try

    End Sub

    Public Sub Set_DEADLOCK_PRIORITY_LOW()

        Try
            cmd1.CommandText = "SET DEADLOCK_PRIORITY LOW"
            cmd1.ExecuteNonQuery()
            RaiseEvent CommandExecuted(cmd1.CommandText)
        Catch ex As Exception
            RaiseEvent WorkerException(ex)
        End Try

    End Sub

    Public Sub UseProducts()

        Try
            cmd1.CommandText = "Update Products SET ProductName=ProductName WHERE ProductID=1"
            cmd1.ExecuteNonQuery()
            RaiseEvent CommandExecuted(cmd1.CommandText)
        Catch ex As Exception
            RaiseEvent WorkerException(ex)
        End Try

    End Sub

    Public Sub UseCategories()

        Try
            cmd1.CommandText = "Update Categories SET CategoryName=CategoryName WHERE CategoryID=1"
            cmd1.ExecuteNonQuery()
            RaiseEvent CommandExecuted(cmd1.CommandText)
        Catch ex As Exception
            RaiseEvent WorkerException(ex)
        End Try

    End Sub

    Public Sub CommitTran()
        Try
            tran1.Commit()
            con1.Close()
        Catch ex As Exception
            RaiseEvent WorkerException(ex)
        End Try
    End Sub

    Public Event WorkerException(ByVal ex As Exception)
    Public Event CommandExecuted(ByVal str As String)

End Class

    Private Sub logla(ByVal str As String)
        lbActions.Items.Add(str)
        lbActions.SelectedIndex = lbActions.Items.Count - 1
    End Sub

    Dim WithEvents worker1 As SqlWorker
    Dim WithEvents worker2 As SqlWorker

    Private Sub btnTran1_1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnTran1_1.Click

        If worker1 Is Nothing Then worker1 = New SqlWorker("worker1")
        worker1.BeginTran()
        If chkTran1LowPriority.Checked Then
            worker1.Set_DEADLOCK_PRIORITY_LOW()
        End If
        logla("Transaction1 başlatıldı.")

    End Sub

    Private Sub btnTran1_2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnTran1_2.Click

        worker1.UseProducts()
        logla("Transaction1 : Products tablosu üzerinde işlem gerçekleştirildi.")

    End Sub

    Private Sub btnTran1_3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnTran1_3.Click

        Dim th As New Threading.Thread(AddressOf worker1.UseCategories)
        th.Start()
        logla("Transaction1 : Categories tablosu üzerinde işlem gerçekleştirildi.")

    End Sub

    Private Sub btnTran1_4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnTran1_4.Click

        worker1.CommitTran()
        logla("Transaction1 sonladırıldı. İlgili connection kapandı.")

    End Sub

    Private Sub btnTran2_1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnTran2_1.Click

        If worker2 Is Nothing Then worker2 = New SqlWorker("worker2")
        worker2.BeginTran()
        If chkTran2LowPriority.Checked Then
            worker2.Set_DEADLOCK_PRIORITY_LOW()
        End If
        logla("Transaction2 başlatıldı.")
    End Sub

    Private Sub btnTran2_2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnTran2_2.Click

        worker2.UseCategories()
        logla("Transaction2 : Categories tablosu üzerinde işlem gerçekleştirildi.")

    End Sub

    Private Sub btnTran2_3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnTran2_3.Click

        Dim th As New Threading.Thread(AddressOf worker2.UseProducts)
        th.Start()
        logla("Transaction2 : Products tablosu üzerinde işlem gerçekleştirildi.")

    End Sub

    Private Sub btnTran2_4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnTran2_4.Click

        worker2.CommitTran()
        logla("Transaction2 sonladırıldı. İlgili connection kapandı.")

    End Sub

    Private Sub worker1_WorkerException(ByVal ex As System.Exception) Handles worker1.WorkerException

        Dim oldColor As Color = GroupBox1.BackColor
        GroupBox1.BackColor = Color.Red
        logla(ex.Message)
        MessageBox.Show(ex.Message, "worker1")
        worker1 = Nothing
        worker2.CommitTran()
        lbActions.Items.Clear()
        GroupBox1.BackColor = oldColor

    End Sub

    Private Sub worker2_WorkerException(ByVal ex As System.Exception) Handles worker2.WorkerException

        Dim oldColor As Color = GroupBox2.BackColor
        GroupBox2.BackColor = Color.Red
        logla(ex.Message)
        MessageBox.Show(ex.Message, "worker2")
        worker2 = Nothing
        worker1.CommitTran()
        lbActions.Items.Clear()
        GroupBox2.BackColor = oldColor

    End Sub

    Private Sub worker1_CommandExecuted(ByVal str As String) Handles worker1.CommandExecuted
        logla("worker1 : " & str)
    End Sub
    Private Sub worker2_CommandExecuted(ByVal str As String) Handles worker2.CommandExecuted
        logla("worker2 : " & str)
    End Sub