Deadlock errors: cause and resolution

By: Ben Matterson

Abstract: What causes deadlock errors and how to handle them.

Deadlock errors: cause and handling.

When running several database clients this error sometimes comes up.
  deadlock: update conflicts with concurrent update
What is going on, and how can these errors be eliminated?

What is going on:

A deadlock is caused when one transaction attempts to update a record which has been updated by another transaction which is still active (not committed yet).

How to prevent these errors:

Deadlocks are a normal occurance in a multi-user database. The only way to completely prevent deadlock errors is to allow only one user to access the database at one time. This is generally not practical. Deadlocks can be *reduced* by keeping transactions very short. Since deadlocks cannot be eliminate entirely, your application must be written to recognize a deadlock and respond in an appropriate manner.

"Appropriate" usually means reading the record again, checking to see if other transaction's changes have made yours irrelevant, and then doing the update again if it's still relevant. This process may involve showing the user a dialog box with the differences and having the user decide whether the update is required.

Server Response from: ETNASC03