Board index » delphi » Deadlock when updating a row twice within a single DB transaction

Roy Powel

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Deadlock when updating a row twice within a single DB transaction

I have a Delphi5 application using ODBC that updates the same row twice
(different columns each time) using UPDATE (SQL) statements - both updates
are issued during a single explicit database transaction.  In MS SQL Server,
this causes a deadlock - the first update places an exclusive row lock,
which blocks the second update.  This works fine, however, when using
MS-Access.  Is there a database setting, ODBC setting, or BDE setting that
will allow me to do this in SQL Server.  Any other ideas?
 
Justin Pitt

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:Deadlock when updating a row twice within a single DB transaction


use sql profiler and see if it reveals what the two are doing different
Roy Powel

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:Deadlock when updating a row twice within a single DB transaction


SQL Profiler shows the blocked update on a different connection id from all
of the previous updates.  Any ideas what would cause this?  Thanks.

Note that if I temporarily remove this particular update from the update
sequence, the remaining updates process on the correct connection id.

Team

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:Deadlock when updating a row twice within a single DB transaction


Quote
Roy Powell wrote in message <39c2476c_2@dnews>...
>SQL Profiler shows the blocked update on a different connection id from all
>of the previous updates.  Any ideas what would cause this?  Thanks.

>Note that if I temporarily remove this particular update from the update
>sequence, the remaining updates process on the correct connection id.

Make sure the dataset you are using for this update is 1) a TQuery, not a
TTable, and 2) is set to the same TDatabase component as the other dataset
components.

--
Wayne Niddery (WinWright Inc.)
RADBooks - http://members.home.net/wniddery/
"I have never understood why it is "greed" to want to keep the money you
have earned but not greed to want to take someone else's money." - Thomas
Sowell

Roy Powel

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:Deadlock when updating a row twice within a single DB transaction


I am using a TQuery and it is set to the same TDatabase component as the
other datasets.

"Wayne Niddery (TeamB)" <winwri...@chaffhome.com> wrote in message
news:39c297a9_1@dnews...

Quote
> Roy Powell wrote in message <39c2476c_2@dnews>...
> >SQL Profiler shows the blocked update on a different connection id from
all
> >of the previous updates.  Any ideas what would cause this?  Thanks.

> >Note that if I temporarily remove this particular update from the update
> >sequence, the remaining updates process on the correct connection id.

> Make sure the dataset you are using for this update is 1) a TQuery, not a
> TTable, and 2) is set to the same TDatabase component as the other dataset
> components.

Dave Albisto

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:Deadlock when updating a row twice within a single DB transaction


Roy

I have had the same problem in an app which I had working with InterBase or
SQL Server. I eventually split the transaction into 2 separate
transactions, which is kind of self-defeating.

Maybe a different Isolation setting will solve the problem.

Dave

Roy Powel

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:Deadlock when updating a row twice within a single DB transaction


I figured out what was happening.  Prior to the blocked UPDATE query, I was
opening a few SELECT queries.  Apparently these queries were not all
processing to completion forcing subsequent queries onto another connection.
Adding FetchAll after each Open fixed the problem.

But, while investigating this problem, I noticed that other transactions
within my application are processing on multiple connections.  FetchAll on
all of my Open's did not make any difference (there are no queries being
opened during the database transaction, anyway.)  There are no locking
conflicts within these transactions, but I did notice that some of the
updates on alternate connections are committing when I commit the
transaction, but some of the connections are autocommitting updates - they
will not rollback if something goes wrong.  This is not good.  How is this
possible?

Thanks.

Tomislav Kard

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:Deadlock when updating a row twice within a single DB transaction


Hi Roy!

On Sat, 16 Sep 2000 13:01:18 -0400, "Roy Powell"

Quote
<RPow...@successware21.com> wrote:
>I figured out what was happening.  Prior to the blocked UPDATE query, I was
>opening a few SELECT queries.  Apparently these queries were not all
>processing to completion forcing subsequent queries onto another connection.
>Adding FetchAll after each Open fixed the problem.

Maybe it would be better to add some stored procedure and let the
server do all the processing?

In my applications I allways make all data entries to the server and
then simply call different stored procedures for data manipulation -
from the client side.

tomi.

Other Threads

1. Deadlock in Single Table & Single User

2. DBGrid Locks Row within Interbase DB

3. Cached Updates TQuery Problem with Modify/Delete (within transaction)

4. How to use cached updates within transactions for SQL2000

5. multiples updates against one table within a transaction

6. multiples updates against same table within one transaction

7. Q: Posting an update within a transaction

8. Q: Posting updates within a transaction

9. allowing only single row selections in TStringGrid (but not entire row)

10. Posting DB updates in a transaction (Capability not supported)