Deadlock Problems

Deadlock Problems

Post by Peter McDona » Thu, 10 Oct 2002 22:25:53



We have been having Locking errors.

We turned on replication server and have been seeing alot of deadlocks
in the error log.  We are getting the deadlock error
on inserts and updates with the commit as the very next statement.
This is the same as having AutoCommit on as far as I know.  

We are wondering if maybe replication server is doing a 2 phase commit
and might be the problem. Is there any way to prove this.

Turning off replication server is not an option unless we can prove it is not
because of increased volume on the server.

What should be our next step

thnks for any help

**********************************************************************
The information in this Internet e-mail is confidential and may be legally privileged.  
It is intended solely for the addressee.  Access to this Internet e-mail by anyone else
is unauthorized and any disclosure, copying, distribution or any action taken or omitted
to be taken in reliance on it, is prohibited and may be unlawful.

When addressed to our clients any opinions or advice contained in this Internet e-mail are
subject to the terms and conditions expressed in any applicable documentation or market
practices governing the relationship between ICAP PLC  and its clients.
Any views expressed in this message are those of the individual sender except where they
are stated to be the views of ICAP PLC.
**********************************************************************

 
 
 

Deadlock Problems

Post by Michael L » Thu, 10 Oct 2002 23:06:30



Quote:>We have been having Locking errors.

>We turned on replication server and have been seeing alot of deadlocks
>in the error log.  We are getting the deadlock error
>on inserts and updates with the commit as the very next statement.
>This is the same as having AutoCommit on as far as I know.

Nope.  Not the same.  The big difference is timing.  When autocommit is
on, there is only one trip to the server per transaction.  For an insert,
this usually means the server does your entire transaction in one "quanta".

Even on a multiprocessor, this greatly reduces your chances of deadlock,
but only under light/medium load.

Autocommit has other problems, like only allowing one cursor open at a time.

What I hate most about autocommit is that "commit" and "rollback" don't
throw errors when autocommit is on.  This gives a mis-configured application
the false hope it is performing transactions.  I never understood this.

Quote:>We are wondering if maybe replication server is doing a 2 phase commit
>and might be the problem. Is there any way to prove this.

Dunno.  I always thought it used message queues, but I could be wrong.

Quote:>Turning off replication server is not an option unless we can prove it is not
>because of increased volume on the server.

It is most likely a combination of the two.  Replicator works well under
light load, and tends to have problems under heavy load.  This is generally
observed by everyone.

Quote:>What should be our next step

Increase the number of locks, locks per transaction, and maxlocks in your
system.  This might prevent lock escalation, which will surely be the death
of you if Replicator is running.

Quote:>thnks for any help

No problem.

Cheers,


Caribou Lake Software  http://www.cariboulake.com  Ingres, JDBC

Specializing in Ingres/Oracle integration and conversion