Re: Avoiding deadlocks when performing bulk update and delete operations

On Wed, 26 Nov 2014 10:41:56 +1100
Sanjaya Vithanagama <svithanagama(at)gmail(dot)com> wrote:
>
> > * How frequently do deadlocks occur?
>
> We are seeing deadlocks about 2-3 times per day in the production server.
> To reproduce the problem easily we've written a simple Java class with
> multiple threads calling to the stored procedures running the above queries
> inside a loop. This way we can easily recreate a scenario that happens in
> the production.

Don't overcomplicate your solution. Adjust your code to detect the deadlock
and replay the transaction when it happens. At 2-3 deadlocks per day, it's
difficult to justify any other solution (as any other solution would be
more time-consuming to implement, AND would interfere with performance).

I've worked with a number of write-heavy applications that experienced
deadlocks, some of them on the order of hundreds of deadlocks per day.
In some cases, you can adjust the queries to reduce the incidence of
deadlocks, or eliminate the possibility of deadlocks completely. The
situation that you describe is not one of those cases, as the planner
can choose to lock rows in whatever order it thinks it most efficient
and you don't have direct control over that.

The performance hit you'll take 2-3 times a day when a statement has to
be replayed due to deadlock will hardly be noticed (although a statement
that takes 50 seconds will cause raised eyebrows if it runs 2x) but that
will only happen 2-3 times a day, and the solution I'm proposing won't
have any performance impact on the other 13000000 queries per day that
don't deadlock.

2-3 deadlocks per day is normal operation for a heavily contented table,
in my experience.

--
Bill Moran
I need your help to succeed:
http://gamesbybill.com