deadlock error in dataobjects with MTS

We have made a simple application with a GUI, a business-object and a data-object, as an example how MTS works. When we start 3 applications (continuesly writing data into a database and retrieving a recordset with the complete database), we get deadlock errors in our data-object.
Is there a simple solution to avoid this problem? Maybe using another cursortype or cursorlocation will help? (we tested some possibilities, but nothing helped so far)

Ofcourse handling the error is a solution to this problem, but we would prefer not to cause the deadlock in the first place.

Any ideas or comments are welcome,

Could it be an issue of open, unused recordsets?

Hi Jason,

Thanks for your reply,
I checked your idea, but all recordsets are probably closed.
Our problem is locking the records by the recordset.Open command. We have reduced the problem by just selecting the first record.
What I try to make is an function to add records to a database with an autonummer ID field. The ID the database gives to the new record must be returned (this way the business-object knows where he can find the record he just saved).
I have added the code below, so you will see what I'm doing.

Public Function AddNew(iSampleType As Integer, sngQn As Single) As Long

Dim conSample As ADOdB.Connection
Dim rsSample As ADOdB.Recordset
Dim ObjContext As ObjectContext
Dim lngCountError As Long
Dim lngRecordSampleID As Long

Set conSample = New ADOdB.Connection
Set rsSample = New ADOdB.Recordset
Set ObjContext = GetObjectContext

lngCountError = 0

'Prepare connection
conSample.Provider = mPROVIDER
conSample.CONNECTIONSTRING = mCONNECTIONSTRING
conSample.Open

rsSample.CursorLocation = adUseClient

rsSample.Open "SELECT TOP 1 * FROM Samples", conSample, adOpenForwardOnly, adLockOptimistic, adCmdText
rsSample.AddNew
rsSample![sampletype] = iSampleType
rsSample![qn] = sngQn
rsSample.Update
lngRecordSampleID = rsSample![SampleID]

AddNew = lngRecordSampleID
rsSample.Close
ObjContext.SetComplete

SetToNothing:
Set rsSample = Nothing
Set conSample = Nothing

End Function

Why don't you do this?:

SELECT * FROM Samples where SampleID = -1

That way, no records will be returned, so no records will be locked. You'll still be able to add a new record.

In my apps that have this situation, I use a stored procedure and I retrieve the return value or use an output parameter for the identity field. No locking problems here.


HTH

Tom

Thanks Tom,

Because my database is pretty big (at this moment 300.000 records, just for testing), the WHERE statement could be slower.
I will test your idea, it will be a better solution than just trapping the deadlock error and trying again.

THANKS!

Fedor

Hi Fedor

if you use this SQL:
SELECT * FROM Samples where SampleID = -1

and the SampleID field is your primary key/autonumber or other indexed field, and there are no records that match that value, it should be a very quick lookup for your DBMS.

There should be no performance problems to worry about in this case