|
|
Imports System.Threading
Imports lightbox.db
Imports System.Data.OracleClient
Public Class Form1
Private stopFlgLeft As Integer = 0
Private stopFlgRight As Integer = 0
Private deadSequence As Integer = 0
Private count As Integer = 0
' ************************************************************
' 初期処理
' ************************************************************
Private Sub Form1_Load(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles MyBase.Load
Me.Left = 0
Me.Top = 0
Me.LboxGrid1.AddColumn("NO", "実行順序")
Me.LboxGrid1.AddColumn("LEFT", "左側スレッド")
Me.LboxGrid1.AddColumn("RIGHT", "右側スレッド")
' 左スレッド開始
Dim ThreadLeft As New Thread(AddressOf ThreadEntryLeft)
ThreadLeft.Start()
' 右スレッド開始
Dim ThreadRight As New Thread(AddressOf ThreadEntryRight)
ThreadRight.Start()
End Sub
' ************************************************************
' スレッドの入り口( 左 )
' ************************************************************
Private Sub ThreadEntryLeft()
Dim dbLeft As New DbOracle("night/XE", "ora01", "ora01")
Dim Bridge As New ProcBridge(AddressOf SetGridData)
Dim Query As String = Nothing
If Not dbLeft.Connect() Then
Me.Invoke(Bridge, New Object() {1, "接続に失敗しました"})
Return
End If
Me.Invoke(Bridge, New Object() {1, "Oracle に接続しました"})
Dim tr As OracleTransaction = dbLeft.myCon.BeginTransaction()
Me.Invoke(Bridge, New Object() {1, "トランザクションを開始しました"})
' stopFlgLeft と 0( 右端 ) と比較して 等しい場合は 0 と置き換える
' つまり、0 の場合は常に 0 を戻して 0 のままである
' 1 になった時のみループが終了する
Do While Interlocked.CompareExchange(stopFlgLeft, 0, 0) = 0
If Interlocked.CompareExchange(deadSequence, 0, 0) = 0 Then
Query = "update 社員マスタ set 給与 = 給与 + 1 where 社員コード = '0001'"
dbLeft.ExecuteTransaction(Query, tr)
Me.Invoke(Bridge, New Object() {1, "0001 を更新(ロック)"})
Interlocked.Exchange(deadSequence, 1)
End If
Thread.Sleep(1000)
If Interlocked.CompareExchange(deadSequence, 2, 2) = 2 Then
Query = "update 社員マスタ set 給与 = 給与 + 1 where 社員コード = '0002'"
Me.Invoke(Bridge, New Object() {1, "0002 を更新します(待機)"})
Dim ret As Integer = dbLeft.ExecuteTransaction(Query, tr)
If ret = -2 Then
Me.Invoke(Bridge, New Object() {1, dbLeft.myError})
tr.Rollback()
Exit Do
Else
Me.Invoke(Bridge, _
New Object() {1, "0002 を更新しました(相手が終了)"})
Exit Do
End If
End If
Loop
Try
tr.Commit()
Catch ex As Exception
End Try
tr.Dispose()
dbLeft.Close()
Me.Invoke(Bridge, New Object() {1, "DB 接続終了"})
Me.Invoke(Bridge, New Object() {1, "スレッド 終了"})
End Sub
' ************************************************************
' スレッドの入り口( 右 )
' ************************************************************
Private Sub ThreadEntryRight()
Dim dbRight As New DbOracle("night/XE", "ora01", "ora01")
Dim Bridge As New ProcBridge(AddressOf SetGridData)
Dim Query As String = Nothing
If Not dbRight.Connect() Then
Me.Invoke(Bridge, New Object() {2, "接続に失敗しました"})
Return
End If
Me.Invoke(Bridge, New Object() {2, "Oracle に接続しました"})
Dim tr As OracleTransaction = dbRight.myCon.BeginTransaction()
dbRight.myCommand.Transaction = tr
Me.Invoke(Bridge, New Object() {2, "トランザクションを開始しました"})
' stopFlgLeft と 0( 右端 ) と比較して 等しい場合は 0 と置き換える
' つまり、0 の場合は常に 0 を戻して 0 のままである
' 1 になった時のみループが終了する
Do While Interlocked.CompareExchange(stopFlgRight, 0, 0) = 0
If Interlocked.CompareExchange(deadSequence, 1, 1) = 1 Then
Query = "update 社員マスタ set 給与 = 給与 + 1 where 社員コード = '0002'"
dbRight.ExecuteTransaction(Query, tr)
Me.Invoke(Bridge, New Object() {2, "0002 を更新(ロック)"})
Interlocked.Exchange(deadSequence, 2)
End If
Thread.Sleep(1000)
If Interlocked.CompareExchange(deadSequence, 2, 2) = 2 Then
Query = "update 社員マスタ set 給与 = 給与 + 1 where 社員コード = '0001'"
Me.Invoke(Bridge, New Object() {2, "0001 を更新します(待機)"})
Dim ret As Integer = dbRight.ExecuteTransaction(Query, tr)
If ret = -2 Then
Me.Invoke(Bridge, New Object() {2, dbRight.myError})
tr.Rollback()
Exit Do
Else
Me.Invoke(Bridge, _
New Object() {2, "0001 を更新しました(相手が終了)"})
Exit Do
End If
End If
Loop
Try
tr.Commit()
Catch ex As Exception
End Try
tr.Dispose()
dbRight.Close()
Me.Invoke(Bridge, New Object() {2, "DB 接続終了"})
Me.Invoke(Bridge, New Object() {2, "スレッド 終了"})
End Sub
' ************************************************************
' 橋渡し用デリゲート
' ************************************************************
Delegate Sub ProcBridge(ByVal type As Integer, ByVal str As String)
' ************************************************************
' 橋渡し用実体
' ************************************************************
Private Sub SetGridData(ByVal type As Integer, ByVal str As String)
Me.LboxGrid1.AddRow()
count += 1
Me.LboxGrid1.SetColumnText("NO", count.ToString())
If type = 1 Then
Me.LboxGrid1.SetColumnText("LEFT", str)
End If
If type = 2 Then
Me.LboxGrid1.SetColumnText("RIGHT", str)
End If
End Sub
' ************************************************************
' 左スレッド終了
' ************************************************************
Private Sub Button1_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button1.Click
Interlocked.Exchange(stopFlgLeft, 1)
End Sub
' ************************************************************
' 右スレッド終了
' ************************************************************
Private Sub Button2_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button2.Click
Interlocked.Exchange(stopFlgRight, 1)
End Sub
End Class
| |
|
|
SELECT でデッドロックが発生するようにするには |
|
変更前(左)
|
Query = "update 社員マスタ set 給与 = 給与 + 1 where 社員コード = '0001'"
| |
|
変更後(左)
|
Query = "select * from 社員マスタ where 社員コード = '0001' for update"
| |
|
変更前(右)
|
Query = "update 社員マスタ set 給与 = 給与 + 1 where 社員コード = '0002'"
| |
|
変更後(右)
|
Query = "select * from 社員マスタ where 社員コード = '0002' for update"
| |
|
|
|
|
Imports System.Threading
Imports lightbox.db
Imports System.Data.OracleClient
Public Class Form1
Private stopFlgLeft As Integer = 0
Private stopFlgRight As Integer = 0
Private deadSequence As Integer = 0
Private count As Integer = 0
' ************************************************************
' 初期処理
' ************************************************************
Private Sub Form1_Load(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles MyBase.Load
Me.Left = 0
Me.Top = 0
Me.LboxGrid1.AddColumn("NO", "実行順序")
Me.LboxGrid1.AddColumn("LEFT", "左側スレッド")
Me.LboxGrid1.AddColumn("RIGHT", "右側スレッド")
' 左スレッド開始
Dim ThreadLeft As New Thread(AddressOf ThreadEntryLeft)
ThreadLeft.Start()
' 右スレッド開始
Dim ThreadRight As New Thread(AddressOf ThreadEntryRight)
ThreadRight.Start()
End Sub
' ************************************************************
' スレッドの入り口( 左 )
' ************************************************************
Private Sub ThreadEntryLeft()
Dim dbLeft As New DbOracle("PED0-034/ORCL", "ora_01", "ora_01")
Dim Bridge As New ProcBridge(AddressOf SetGridData)
Dim Query As String = Nothing
If Not dbLeft.Connect() Then
Me.Invoke(Bridge, New Object() {1, "接続に失敗しました"})
Return
End If
Me.Invoke(Bridge, New Object() {1, "Oracle に接続しました"})
Dim tr As OracleTransaction = dbLeft.myCon.BeginTransaction()
dbLeft.myCommand.Transaction = tr
Me.Invoke(Bridge, New Object() {1, "トランザクションを開始しました"})
' stopFlgLeft と 0( 右端 ) と比較して 等しい場合は 0 と置き換える
' つまり、0 の場合は常に 0 を戻して 0 のままである
' 1 になった時のみループが終了する
Do While Interlocked.CompareExchange(stopFlgLeft, 0, 0) = 0
If Interlocked.CompareExchange(deadSequence, 0, 0) = 0 Then
Query = "select * from 社員マスタ where 社員コード = '0001' for update"
dbLeft.QueryAndRead(Query)
Me.Invoke(Bridge, New Object() {1, "0001の給与:" + dbLeft.GetValue("給与")})
Query = "update 社員マスタ set 給与 = 給与 + 1 where 社員コード = '0001'"
dbLeft.ExecuteTransaction(Query, tr)
Me.Invoke(Bridge, New Object() {1, "0001 を更新(ロック)"})
Query = "select * from 社員マスタ where 社員コード = '0001'"
dbLeft.QueryAndRead(Query)
Me.Invoke(Bridge, New Object() {1, "0001の給与:" + dbLeft.GetValue("給与")})
Interlocked.Exchange(deadSequence, 1)
End If
Thread.Sleep(1000)
If Interlocked.CompareExchange(deadSequence, 2, 2) = 2 Then
Query = "select * from 社員マスタ where 社員コード = '0001'"
dbLeft.QueryAndRead(Query)
Me.Invoke(Bridge, New Object() {1, "0001の給与:" + dbLeft.GetValue("給与")})
Query = "select * from 社員マスタ where 社員コード = '0002'"
dbLeft.QueryAndRead(Query)
Me.Invoke(Bridge, New Object() {1, "0002の給与:" + dbLeft.GetValue("給与")})
Query = "update 社員マスタ set 給与 = 給与 + 1 where 社員コード = '0002'"
Me.Invoke(Bridge, New Object() {1, "0002 を更新します(待機)"})
Dim ret As Integer = dbLeft.ExecuteTransaction(Query, tr)
If ret = -2 Then
Me.Invoke(Bridge, New Object() {1, dbLeft.myError})
tr.Rollback()
Exit Do
Else
Me.Invoke(Bridge, _
New Object() {1, "0002 を更新しました(相手が終了)"})
Exit Do
End If
End If
Loop
Try
tr.Commit()
Catch ex As Exception
End Try
tr.Dispose()
dbLeft.Close()
Me.Invoke(Bridge, New Object() {1, "DB 接続終了"})
Me.Invoke(Bridge, New Object() {1, "スレッド 終了"})
End Sub
' ************************************************************
' スレッドの入り口( 右 )
' ************************************************************
Private Sub ThreadEntryRight()
Dim dbRight As New DbOracle("PED0-034/ORCL", "ora_01", "ora_01")
Dim Bridge As New ProcBridge(AddressOf SetGridData)
Dim Query As String = Nothing
If Not dbRight.Connect() Then
Me.Invoke(Bridge, New Object() {2, "接続に失敗しました"})
Return
End If
Me.Invoke(Bridge, New Object() {2, "Oracle に接続しました"})
Dim tr As OracleTransaction = dbRight.myCon.BeginTransaction()
dbRight.myCommand.Transaction = tr
Me.Invoke(Bridge, New Object() {2, "トランザクションを開始しました"})
' stopFlgLeft と 0( 右端 ) と比較して 等しい場合は 0 と置き換える
' つまり、0 の場合は常に 0 を戻して 0 のままである
' 1 になった時のみループが終了する
Do While Interlocked.CompareExchange(stopFlgRight, 0, 0) = 0
If Interlocked.CompareExchange(deadSequence, 1, 1) = 1 Then
Query = "select * from 社員マスタ where 社員コード = '0002' for update"
dbRight.QueryAndRead(Query)
Me.Invoke(Bridge, New Object() {2, "0002の給与:" + dbRight.GetValue("給与")})
Query = "update 社員マスタ set 給与 = 給与 + 1 where 社員コード = '0002'"
dbRight.ExecuteTransaction(Query, tr)
Me.Invoke(Bridge, New Object() {2, "0002 を更新(ロック)"})
Query = "select * from 社員マスタ where 社員コード = '0002'"
dbRight.QueryAndRead(Query)
Me.Invoke(Bridge, New Object() {2, "0002の給与:" + dbRight.GetValue("給与")})
Interlocked.Exchange(deadSequence, 2)
End If
Thread.Sleep(1000)
If Interlocked.CompareExchange(deadSequence, 2, 2) = 2 Then
Query = "select * from 社員マスタ where 社員コード = '0001'"
dbRight.QueryAndRead(Query)
Me.Invoke(Bridge, New Object() {2, "0001の給与:" + dbRight.GetValue("給与")})
Query = "select * from 社員マスタ where 社員コード = '0002'"
dbRight.QueryAndRead(Query)
Me.Invoke(Bridge, New Object() {2, "0002の給与:" + dbRight.GetValue("給与")})
Query = "update 社員マスタ set 給与 = 給与 + 1 where 社員コード = '0001'"
Me.Invoke(Bridge, New Object() {2, "0001 を更新します(待機)"})
Dim ret As Integer = dbRight.ExecuteTransaction(Query, tr)
If ret = -2 Then
Me.Invoke(Bridge, New Object() {2, dbRight.myError})
tr.Rollback()
Exit Do
Else
Me.Invoke(Bridge, _
New Object() {2, "0001 を更新しました(相手が終了)"})
Exit Do
End If
End If
Loop
Try
tr.Commit()
Catch ex As Exception
End Try
tr.Dispose()
dbRight.Close()
Me.Invoke(Bridge, New Object() {2, "DB 接続終了"})
Me.Invoke(Bridge, New Object() {2, "スレッド 終了"})
End Sub
' ************************************************************
' 橋渡し用デリゲート
' ************************************************************
Delegate Sub ProcBridge(ByVal type As Integer, ByVal str As String)
' ************************************************************
' 橋渡し用実体
' ************************************************************
Private Sub SetGridData(ByVal type As Integer, ByVal str As String)
Me.LboxGrid1.AddRow()
count += 1
Me.LboxGrid1.SetColumnText("NO", count.ToString())
If type = 1 Then
Me.LboxGrid1.SetColumnText("LEFT", str)
End If
If type = 2 Then
Me.LboxGrid1.SetColumnText("RIGHT", str)
End If
End Sub
' ************************************************************
' 左スレッド終了
' ************************************************************
Private Sub Button1_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button1.Click
Interlocked.Exchange(stopFlgLeft, 1)
End Sub
' ************************************************************
' 右スレッド終了
' ************************************************************
Private Sub Button2_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button2.Click
Interlocked.Exchange(stopFlgRight, 1)
End Sub
End Class
| |
|
|
|