deadlock error

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
swright
Posts: 28
Joined: Wed 23 Dec 2009 12:19

deadlock error

Post by swright » Fri 09 Jul 2010 09:11

I have a data collection app that is inserting into my DB and whilst this is inserting on another app running a select query i get the error

Transaction (Process ID nn) was deadlocked on lock resources with with another process and had been chosen as the deadlock victm. Rerun the transaction.

How should i deal with this?

Thanks

Challenger
Devart Team
Posts: 925
Joined: Thu 17 Nov 2005 10:53

Post by Challenger » Fri 09 Jul 2010 14:22

Please specify the isolation level of your transactions in both applications. You can also take a look at this article.

swright
Posts: 28
Joined: Wed 23 Dec 2009 12:19

Post by swright » Fri 09 Jul 2010 16:01

In both cases it is ilReadCommitted and only one process is in a transaction inserting records. The other is a TMSQuery 'select'

Challenger
Devart Team
Posts: 925
Joined: Thu 17 Nov 2005 10:53

Post by Challenger » Wed 14 Jul 2010 15:54

It seems that this is not a SDAC problem. Could you please try to read data using SQL Server Management Studio.

swright
Posts: 28
Joined: Wed 23 Dec 2009 12:19

Post by swright » Tue 20 Jul 2010 15:43

I assume the error is returned by the engine. problem is how do I handle it.

Only one of the apps is using a transaction to insert records into the database.

The second "victim" app is running a select query (reading).

the error is still presented, even if i place a try/except around the query.open

Dimon
Devart Team
Posts: 2885
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Wed 21 Jul 2010 14:13

To solve the problem try to set the TMSQuery.ReadOnly property to True.

swright
Posts: 28
Joined: Wed 23 Dec 2009 12:19

Post by swright » Wed 21 Jul 2010 16:07

Thanks for the reply.

I tried that and it did not work.

I also tried setting the locktype to ltnolock.

The select statement had some aggregate select statements, I added WITH (NOLOCK) to each of these and I think it may have solved it.

regards

Steve

Dimon
Devart Team
Posts: 2885
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Thu 22 Jul 2010 07:43

Was the problem solved?
If not, please try to compose a small sample to demonstrate the problem and send it to dmitryg*devart*com.

Post Reply