Deadlock Issues with VB6/ADO/SQL 2005

[desmondgreen]

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'