Mysql: Deadlocked crash

[wmertens]

Doing lots of insertions. No clue why this happened.

I'm using the connection pool if that makes a difference. For each insert I get a connection from the pool, do the insert and end the connection.

Error: ER_LOCK_DEADLOCK: Deadlock found when trying to get lock; try restarting transaction
  at Query.Sequence._packetToError (/home/techzone/tz-stats/node_modules/mysql/lib/protocol/sequences/Sequence.js:32:14)
  at Query.ErrorPacket (/home/techzone/tz-stats/node_modules/mysql/lib/protocol/sequences/Query.js:82:18)
  at Protocol._parsePacket (/home/techzone/tz-stats/node_modules/mysql/lib/protocol/Protocol.js:172:24)
  at Parser.write (/home/techzone/tz-stats/node_modules/mysql/lib/protocol/Parser.js:62:12)
  at Protocol.write (/home/techzone/tz-stats/node_modules/mysql/lib/protocol/Protocol.js:37:16)
  at Socket.ondata (stream.js:51:26)
  at Socket.EventEmitter.emit (events.js:117:20)
  at Socket.<anonymous> (_stream_readable.js:736:14)
  at Socket.EventEmitter.emit (events.js:92:17)
  at emitReadable_ (_stream_readable.js:408:10)
  at emitReadable (_stream_readable.js:404:5)
  at readableAddChunk (_stream_readable.js:165:9)
  at Socket.Readable.push (_stream_readable.js:127:10)
  at TCP.onread (net.js:526:21)
    --------------------
  at Query.Sequence (/home/techzone/tz-stats/node_modules/mysql/lib/protocol/sequences/Sequence.js:15:20)
  at new Query (/home/techzone/tz-stats/node_modules/mysql/lib/protocol/sequences/Query.js:12:12)
  at Function.Connection.createQuery (/home/techzone/tz-stats/node_modules/mysql/lib/Connection.js:48:10)
  at Connection.query (/home/techzone/tz-stats/node_modules/mysql/lib/Connection.js:100:26)
From previous event:
  at /home/techzone/tz-stats/db.coffee:30:14, <js>:47:18

[wmertens]

Using v2.0.0-alpha8 on NodeJS v0.10.14

[felixge]

This error is coming from the mysql server - seems like you need to report the bug to them.

[wmertens]

Turns out it was because I went from a single connection to a connection pool and parallel inserts caused locking contention, even on separate rows. :cry:

I set the max concurrent connections to 1 for now.

Any tips on handling this in a way that still allows 10 parallel connections? Wrap the queries in something that intercepts the deadlock error and retries 3 times?

[felixge]

Any tips on handling this in a way that still allows 10 parallel connections? Wrap the queries in something that intercepts the deadlock error and retries 3 times?

Haven't run into this myself, it seems like the mysql server community / stack overflow might be a better place for discussing this. (I'm not opposed to discussing it here, I just think that it's not really related to the driver one is using)

[wmertens]

Well, the fact that NodeJS allows easy parallelism makes it more likely to encounter the issue and some concrete workarounds might be nice - perhaps even a driver setting. OTOH Stack Overflow lets you get very specific too.

In any case, I just found a bug in my code that influenced the code flow and might have caused overlapping updates . I'll do some more isolating, try the SO route and then come back here if I think something driver-side could be useful.

Thanks for your swift responses!

[felixge]

Well, the fact that NodeJS allows easy parallelism makes it more likely to encounter the issue and some concrete workarounds might be nice

You will get the same issues with other languages if multiple processes / machines are trying to perform inserts.

perhaps even a driver setting.

I don't see what the driver should be doing here. A deadlock seems like a bug in the mysql server storage engine you're using.

[dougwilson]

@wmertens it depends on your INSERT statements. The typical cause is from deadlocks when locking keys in the table in different orders at the same time. You can either look at and correct your queries (also SELECT and other queries will lock keys) and have them locks the keys in the same order, or you can simply retry the INSERTs multiple times if you like.

I ran into it in my own application and those were the approaches I used. Also note, the MySQL documentation notes that you should expect a deadlock error, as it is a natural part of transactional databases (like InnoDB).

[wmertens]

@dougwilson thanks for that, that was indeed the reason.

Seeing this, I think there might be room for a "retryOnDeadlock" parameter in node-mysql queries...

[cbshakumar]

I ran into a similar problem and found it to be due to indexes/key constraints.
It is possible to get a partial lock based on having composite indexes or composite keys.

http://stackoverflow.com/questions/15412838/deadlock-issue-when-transaction-tries-to-accuire-a-lock-its-already-holding

I solved this by changing the table schema to a primary key with a single column.

[umutm]

I experienced the same error and Google got me here :).

MySQL has a suggestion for handling them (https://dev.mysql.com/doc/refman/5.6/en/error-messages-server.html#error_er_lock_deadlock).

[tapasudenia]

I have the same deadlock issue, I want to delete the rows by some ID and then add in a transaction. How do I restart the transaction or change the concurrent connections?

module.exports.updateTableAByProjectID = async (
projectID,
insertValuesFormat,
insertValues
) => {
log.debug('Entering updateTableAByProjectID ');
await util.executeTransaction(async (connection) => {
await util.executeChangeQuery(
'SELECT * FROM TableA where projectID = ? FOR UPDATE',
[projectID],
connection
);

// Delete Users
await util.executeChangeQuery(
    'DELETE FROM TableA WHERE projectID = ?',
    [projectID],
    connection
);

// This is bulk insert, insertValuesFormat - (?,?,?), (?,?,?) format
const insertQuery =
  'INSERT IGNORE INTO TableA (colA, projectID, ColC) VALUES ' +
  insertValuesFormat;
await util.executeChangeQuery(insertQuery, insertValues, connection);

});
};

module.exports.executeChangeQuery = async (query, params, connection) => {
log.debug('Entering executeChangeQuery');
let rows;
try {
if (connection) {
log.debug('executeChangeQuery Query ' + query);
rows = await connection.execute(query, params);
} else {
rows = await mysqlPool.execute(query, params);
}
// Checking for rows[0] which is an extra layer on ROLLBACK.
if (rows && Array.isArray(rows[0])) {
rows = rows[0];
}
return rows;
} catch (err) {
log.error(err);
throw err;
}
};

module.exports.executeTransaction = async (func) => {
log.debug('Entering executeTransaction');
const connection = await mysqlPool.getConnection();
try {
await connection.beginTransaction();
const result = await func(connection);
await connection.commit();
connection.release();
return result;
} catch (err) {
log.error(err);
await connection.rollback();
connection.release();
throw err;
}
};