Background
First of all, let's quickly outline what a database deadlock is and how it happens.
Suppose you have a transaction (TrA) in progress. TrA is merrily progressing through its queries when transaction B commences.
TrB needs and is granted a lock on a table, but its next query requires a lock on a table that TrA holds and hasn't yet released. So it waits for TrA to release the lock.
But TrA's next query needs a lock on the table that TrB holds.
So TrB is waiting for TrA to progress, and TrA is waiting for TrB to progress. Neither transaction can progress, so we say they are deadlocked.
This can happen very easily, with just a few UPDATE
statements!
In general we can minimise the chance of a deadlock in a few ways. We can make transactions short so that locks aren't held for long periods of time. We can also look closely at the application logic to try to arrange transactions to be more compatible with each other. But we can't eliminate deadlocks entirely.
Deadlocks are a normal situation in a database and inevitable in a high transaction system. If you're not getting deadlocks, it really just means you don't have much traffic going through your database connection.
So how do we handle deadlocks?
We can't prevent deadlocks entirely, so we always need to be able to handle them when they do arise.
When the database encounters pair of deadlocked transactions, it will roll back one transaction to let the other progress. The application will receive some kind of error from the rolled back transaction, which we can catch.
When this happens, the correct thing to do is to try again. As deadlocks are usually because of bad luck in the timing, re-running the failed transaction is usually sufficient.
A code example
Let's look at some code, using Node.js and the mysql npm package.
We'll expose a function called tx, which runs a transaction (as a function) on a given connection (i.e something returned by the mysql package's connection pool).
Deadlocks are caught and transactions re-scheduled using a retry() function. The retry waiter is hard coded to 3 seconds, which you might want to adapt. One approach is to have the first retry fire immediately and increase the wait period every subsequent retry.
(Note: I tend to use promises with async/await because I think it makes control flow a lot easier to read. Apologies if you are used to callbacks. You could adapt this to use callbacks if that's what you prefer)
// File: db.js.
const mysql = require('mysql');
const { promisify } = require('util');
async function retry(func, count, limit) {
// retry waiter in milliseconds.
const wait = 3000;
count = count || 0;
limit = limit || 10;
if (count >= limit) {
throw new Error('Retry limit exceeded');
}
try {
let result = await func();
if (count) {
console.log('Retry succeeded after %d attempts', count);
}
return result;
}
catch (err) {
if (err.code === 'ER_LOCK_DEADLOCK') {
return new Promise((resolve, reject) => {
setTimeout(async () => {
let response;
try {
response = await retry(func, count + 1, limit)
}
catch(err) {
return reject(err);
}
resolve(response);
}, wait);
})
}
else {
throw err;
}
}
}
async function tx(connection, func) {
const f = () =>{
return new Promise(async (resolve, reject) => {
let commit = promisify(connection.commit.bind(connection));
let rollback = promisify(connection.rollback.bind(connection));
connection.beginTransaction(async () => {
try {
await func(connection);
let response = await commit();
resolve(response);
}
catch (err) {
console.error('DB error ' + err + ', rolling back');
await rollback();
reject(err);
}
});
});
};
return await retry(f)
}
exports.tx = tx;
Then usage is something like this:
const mysql = require('mysql');
const db = require('./db');
var pool = mysql.createPool({
connectionLimit : 10,
host : 'localhost',
user : 'user',
password : 'password',
database : 'database'
});
pool.getConnection(async (err, connection) => {
let newId = await db.tx(connection, async (connection) => {
let query = promisify(connection.query.bind(connection));
let res = await query('INSERT INTO myTable(colA, colB) VALUES(?, ?)', [1, 2]);
await query('INSERT INTO myOtherTable(myTableId, colB) VALUES(?, ?)', [res.insertId, 'some value']);
return insertId;
});
})