SQL Server

soccers_guy10 · August 20th, 2003, 03:56 AM

I 've got a problem with concerrency in SQL Server with VB6 Application.
I got a message about 'deadlock' error when some transaction is made at the same time .. How can I prevent this error? there're about 10 users using this application at the same time ..
Thanks

pgtips · August 20th, 2003, 04:12 AM

You just need to trap the error and retry the operation. Here's the error number to look out for:

Code:

Public Const cErrDeadlock As Long = &H80004005
Public Const cErrTimeout As Long = &H80040E31 ' this is useful too

and here's an example of code to put in an error handler (retries 3 times):

Code:

' retry any deadlocks or lock timeouts
Dim nRetryCount As Integer
If nRetryCount < 3 And (Err.Number = cErrDeadlock Or Err.Number = cErrTimeout) Then
    nRetryCount = nRetryCount + 1
    Resume
Else
    ' handle other errors here
End If

Jeff Mason · August 20th, 2003, 11:30 AM

You can minimize, (though not necessarily eliminate) deadlocks by doing two things. First, make sure your transactions are as short as possible. Don't start a transaction then hold it open while you are waiting for user input, for example.

Second, insure that you always access objects in the same order. For example, If one procedure consists of:

Begin Transaction
UPDATE OrderItems ...
UPDATE Orders ...
Commit Transaction

and a second process does:

Begin Transaction
UPDATE Orders ...
UPDATE OrderItems ...
Commit Transaction

this will pretty much guarantee a deadlock eventually. If both processes obtain the locks in the same order, then the second will happily wait until the locks are released by the first's commit.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

jlick · August 20th, 2003, 12:07 PM

I agree with both of the above responses. I would readup on locking in the SQL Books online. (Help from query analyser.)

You should look at how your transactions are processing. Don't use distributed transactions when you are only updating one table.

Consider using NOLOCK on your select statements. (This allows the select to read uncommitted data, but stops it from locking the table.)

Don't create transactions for multiple select statements unless your really need to.

John R Lick
[email protected]

soccers_guy10 · August 21st, 2003, 02:05 AM

Thanks for every one to help me solved this problem.

highquestion · December 18th, 2004, 01:22 PM

Upon starting Windows ME, I recieve the system error &H80004005(-2147767259). unspecified error. When I click Ok on the error message It goes away but windows never starts up past a blueish screen.Can somebody please help me?