We have a problem with transaction management/concurrency when using ADO to update a database on a SQL 2005 database. We have a test application, to isolate and demonstrate the problem, where a VB6 client application gets a recordset and performs a simple update within the scope of an ADO transaction. When running multiple instances of the test application, failure is very quickly reached with a 'Transaction (Process ID x) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction'
We have tried the following:
1. Testing on SQL 2000, which generally seems to work successfully.
2. The hotfix at http://support.microso ft.com/kb/953948, which accurately describes the issue, but does not seem to solve it.
3. Changing the isolation level of the connection (and consequently the transaction) to read uncommitted. Currently, the isolation level is not explicitly set and assumes the default setting of read committed. This solves the issue, but we are not happy to make this change because it has data integrity implications.
To recreate the issue, do the following:
1. Create a new SQL database on an instance of SQL 2005, consisting of one very simple table.
USE [master]
GO
CREATE DATABASE [TestConcurrency]
GO
USE [TestConcurrency]
GO
CREATE TABLE NextTableIdenti ty (Id integer IDENTITY(1,1),T ableName varchar(255),Ne xtIdentity decimal(21,0) null)
create unique clustered index NextTableIdenti ty_TableName_In dex on NextTableIdenti ty (TableName)
create unique index NextTableIdenti ty_Id_Index on NextTableIdenti ty (Id)
GO
insert into NextTableIdenti ty (TableName,Next Identity) values ('Ccs_Q_Section s',1)
go
2. Create a new VB6 exe, with a button and the following code:
Option Explicit
Private mobjConn As ADODB.Connectio n
Private Sub Command1_Click( )
Dim lngIndex As Long
Label1.Caption = "Started "
DoEvents
For lngIndex = 0 To 300
GetID
Next
Label1.Caption = "Finished"
DoEvents
End Sub
Private Sub GetID()
Dim objRS As ADODB.Recordset
Dim lngID As Long
OpenDB
mobjConn.BeginT rans
Set objRS = ExecuteSQL("SEL ECT NextIdentity, TableName, ID FROM NextTableIdenti ty WHERE TableName = 'Ccs_Q_Sections '")
objRS(0).Value = objRS(0).Value + 1
lngID = objRS(0).Value
objRS.Update
mobjConn.Commit Trans
objRS.Close
CloseDB
Label1.Caption = "(Select) Table : Ccs_Q_Sections ... ID updated <" & lngID & "> successfully."
End Sub
Private Sub OpenDB()
Set mobjConn = New Connection
With mobjConn
.ConnectionStri ng = Me.ConnectionSt ring.Text
.ConnectionTime out = 10000
.CommandTimeout = 10000
.CursorLocation = ADODB.CursorLoc ationEnum.adUse Server
.Open
End With
End Sub
Private Sub CloseDB()
With mobjConn
'.CommitTrans()
.Close
End With
Set mobjConn = Nothing
End Sub
Private Function ExecuteSQL(ByVa l pstrSQL As String) As ADODB.Recordset
Dim objRecordset As ADODB.Recordset
Set objRecordset = New ADODB.Recordset
With objRecordset
.CursorLocation = ADODB.CursorLoc ationEnum.adUse Server
.Open pstrSQL, mobjConn, ADODB.CursorTyp eEnum.adOpenSta tic, _
ADODB.LockTypeE num.adLockPessi mistic, -1
Set ExecuteSQL = objRecordset
End With
End Function
Private Sub Form_Load()
Me.ConnectionSt ring.Text = "PROVIDER=SQLOL EDB;Data Source=myserver name;Initial Catalog='TestCo ncurrency';Loca le Identifier=1033 ;User ID='sa';Passwor d='mypassword'"
End Sub
3. Compile the VB6 exe.
4. Start up 2 or 3 copies of the exe. Quickly press the command button on the form to start the processing within each instance.
5. Very quickly, the following error occurs:
'Transaction (Process ID x) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction'
We have tried the following:
1. Testing on SQL 2000, which generally seems to work successfully.
2. The hotfix at http://support.microso ft.com/kb/953948, which accurately describes the issue, but does not seem to solve it.
3. Changing the isolation level of the connection (and consequently the transaction) to read uncommitted. Currently, the isolation level is not explicitly set and assumes the default setting of read committed. This solves the issue, but we are not happy to make this change because it has data integrity implications.
To recreate the issue, do the following:
1. Create a new SQL database on an instance of SQL 2005, consisting of one very simple table.
USE [master]
GO
CREATE DATABASE [TestConcurrency]
GO
USE [TestConcurrency]
GO
CREATE TABLE NextTableIdenti ty (Id integer IDENTITY(1,1),T ableName varchar(255),Ne xtIdentity decimal(21,0) null)
create unique clustered index NextTableIdenti ty_TableName_In dex on NextTableIdenti ty (TableName)
create unique index NextTableIdenti ty_Id_Index on NextTableIdenti ty (Id)
GO
insert into NextTableIdenti ty (TableName,Next Identity) values ('Ccs_Q_Section s',1)
go
2. Create a new VB6 exe, with a button and the following code:
Option Explicit
Private mobjConn As ADODB.Connectio n
Private Sub Command1_Click( )
Dim lngIndex As Long
Label1.Caption = "Started "
DoEvents
For lngIndex = 0 To 300
GetID
Next
Label1.Caption = "Finished"
DoEvents
End Sub
Private Sub GetID()
Dim objRS As ADODB.Recordset
Dim lngID As Long
OpenDB
mobjConn.BeginT rans
Set objRS = ExecuteSQL("SEL ECT NextIdentity, TableName, ID FROM NextTableIdenti ty WHERE TableName = 'Ccs_Q_Sections '")
objRS(0).Value = objRS(0).Value + 1
lngID = objRS(0).Value
objRS.Update
mobjConn.Commit Trans
objRS.Close
CloseDB
Label1.Caption = "(Select) Table : Ccs_Q_Sections ... ID updated <" & lngID & "> successfully."
End Sub
Private Sub OpenDB()
Set mobjConn = New Connection
With mobjConn
.ConnectionStri ng = Me.ConnectionSt ring.Text
.ConnectionTime out = 10000
.CommandTimeout = 10000
.CursorLocation = ADODB.CursorLoc ationEnum.adUse Server
.Open
End With
End Sub
Private Sub CloseDB()
With mobjConn
'.CommitTrans()
.Close
End With
Set mobjConn = Nothing
End Sub
Private Function ExecuteSQL(ByVa l pstrSQL As String) As ADODB.Recordset
Dim objRecordset As ADODB.Recordset
Set objRecordset = New ADODB.Recordset
With objRecordset
.CursorLocation = ADODB.CursorLoc ationEnum.adUse Server
.Open pstrSQL, mobjConn, ADODB.CursorTyp eEnum.adOpenSta tic, _
ADODB.LockTypeE num.adLockPessi mistic, -1
Set ExecuteSQL = objRecordset
End With
End Function
Private Sub Form_Load()
Me.ConnectionSt ring.Text = "PROVIDER=SQLOL EDB;Data Source=myserver name;Initial Catalog='TestCo ncurrency';Loca le Identifier=1033 ;User ID='sa';Passwor d='mypassword'"
End Sub
3. Compile the VB6 exe.
4. Start up 2 or 3 copies of the exe. Quickly press the command button on the form to start the processing within each instance.
5. Very quickly, the following error occurs:
'Transaction (Process ID x) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction'