NodeJS + MySQL:使用连接池导致死锁表 [英] NodeJS + mysql: using connection pool leads to deadlock tables
问题描述
我正在使用NodeJS和mysql
库访问MySQL数据库.
I am using NodeJS and mysql
library to access MySQL database.
当我建立单个连接并重复使用它时,它可以正常工作:
When I establish single connection and repeatedly use it, it works fine:
global.mysql = mysql_module.createConnection({
host: config.mysql.host,
user: config.mysql.user,
password: config.mysql.password
});
当我改用连接池时,在事务中出现ER_LOCK_WAIT_TIMEOUT
错误.
When I use connection pool instead, I get ER_LOCK_WAIT_TIMEOUT
errors in transactions.
global.mysql = mysql_module.createPool({
host: config.mysql.host,
user: config.mysql.user,
password: config.mysql.password,
database : config.mysql.database,
connectionLimit : 50
});
奇怪的是,错误确实在完全相同的时间发生在完全相同的数据上.
IE.我有一个事务,我在其中连续插入三个表中,每次都使用上一个INSERT
语句中的最后插入的ID.对于某些数据,这可以正常工作;对于某些数据,第三个INSERT
会产生ER_LOCK_WAIT_TIMEOUT
错误.当我在NodeJS中使用单个连接时,这可以正常工作,因此这一定是与连接池有关的问题.
Strangely enough, the errors do occur on exactly the same data at exactly the same times.
I.e. I have transaction in which I insert in three tables in a row, each time using last inserted ID from previous INSERT
statement. With some data this works fine, with some data, the third INSERT
produces ER_LOCK_WAIT_TIMEOUT
error. When I use single connection in NodeJS, this works fine, so this must be problem related to connection pool.
任何帮助将不胜感激.
推荐答案
不支持我自己的问题,但是可以通过在池中显式创建连接并将该连接用于事务期间的每个sql语句来解决问题.
No a big fan of asnwering my own questions, but problem is solved by explicitly creating connection from a pool and using it for every sql statement during transaction
pool.getConnection(function(err, connection) {
connection.query( 'START TRANSACTION', function(err, rows) {
// do all sql statements with connection and then
connection.query( 'COMMIT', function(err, rows) {
connection.release();
}
});
});
这篇关于NodeJS + MySQL:使用连接池导致死锁表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!