By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,347 Members | 2,120 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,347 IT Pros & Developers. It's quick & easy.

Deadlock Issues with VB6/ADO/SQL 2005

P: 1
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.microsoft.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 NextTableIdentity (Id integer IDENTITY(1,1),TableName varchar(255),NextIdentity decimal(21,0) null)
create unique clustered index NextTableIdentity_TableName_Index on NextTableIdentity (TableName)
create unique index NextTableIdentity_Id_Index on NextTableIdentity (Id)
GO
insert into NextTableIdentity (TableName,NextIdentity) values ('Ccs_Q_Sections',1)
go

2. Create a new VB6 exe, with a button and the following code:

Option Explicit

Private mobjConn As ADODB.Connection

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.BeginTrans

Set objRS = ExecuteSQL("SELECT NextIdentity, TableName, ID FROM NextTableIdentity WHERE TableName = 'Ccs_Q_Sections'")

objRS(0).Value = objRS(0).Value + 1
lngID = objRS(0).Value

objRS.Update
mobjConn.CommitTrans
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
.ConnectionString = Me.ConnectionString.Text
.ConnectionTimeout = 10000
.CommandTimeout = 10000
.CursorLocation = ADODB.CursorLocationEnum.adUseServer
.Open
End With
End Sub

Private Sub CloseDB()
With mobjConn
'.CommitTrans()
.Close
End With
Set mobjConn = Nothing
End Sub

Private Function ExecuteSQL(ByVal pstrSQL As String) As ADODB.Recordset

Dim objRecordset As ADODB.Recordset

Set objRecordset = New ADODB.Recordset
With objRecordset

.CursorLocation = ADODB.CursorLocationEnum.adUseServer
.Open pstrSQL, mobjConn, ADODB.CursorTypeEnum.adOpenStatic, _
ADODB.LockTypeEnum.adLockPessimistic, -1

Set ExecuteSQL = objRecordset
End With

End Function

Private Sub Form_Load()
Me.ConnectionString.Text = "PROVIDER=SQLOLEDB;Data Source=myservername;Initial Catalog='TestConcurrency';Locale Identifier=1033;User ID='sa';Password='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'
Jul 1 '08 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.