Mixing
Node.js mysql createPool Deadlock sometimes… but why?
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
I have a problem: I changed mysql from createConnection to createPool. But sometimes when many queries will run, I get deadlocks.
Here's the pool
var db_config = {
connectionLimit : 1000,
host: 'localhost',
user: '******',
password: '******',
database: '******.com'
};
pool = mysql.createPool(db_config);
pool.getConnection(function(err, connection) {
if(err) {
logger.error('[ERROR] Connecting to database "' + err.toString() + '"');
setTimeout(function() { database_connection(); }, 2500);
}
else
{
pool.query('SET NAMES utf8');
pool.query('SET CHARACTER SET utf8');
logger.trace('[INFO] Connected to database and set utf8!');
}
});
That's the database connection
But for any reason, when many queries send at same time or something, I get deadlock message
Deadlock found when trying to get lock; try restarting transaction
Every query that used for the queries looks like that
pool.query('SELECT * users');
I read that query combines connection + the query + release, that is what i read.
But I don't know why so much times deadlock... anyone has any ideas? :/
Its InnoDB MySQL Tables
mysql node.js deadlock mysqljs
edited Jan 12 at 15:04
marc_s
585k13011251272
asked Jan 3 at 11:48
ReudigaReudiga
43
|
show 2 more comments
I have a problem: I changed mysql from createConnection to createPool. But sometimes when many queries will run, I get deadlocks.
Here's the pool
var db_config = {
connectionLimit : 1000,
host: 'localhost',
user: '******',
password: '******',
database: '******.com'
};
pool = mysql.createPool(db_config);
pool.getConnection(function(err, connection) {
if(err) {
logger.error('[ERROR] Connecting to database "' + err.toString() + '"');
setTimeout(function() { database_connection(); }, 2500);
}
else
{
pool.query('SET NAMES utf8');
pool.query('SET CHARACTER SET utf8');
logger.trace('[INFO] Connected to database and set utf8!');
}
});
That's the database connection
But for any reason, when many queries send at same time or something, I get deadlock message
Deadlock found when trying to get lock; try restarting transaction
Every query that used for the queries looks like that
pool.query('SELECT * users');
I read that query combines connection + the query + release, that is what i read.
But I don't know why so much times deadlock... anyone has any ideas? :/
Its InnoDB MySQL Tables
mysql node.js deadlock mysqljs
edited Jan 12 at 15:04
marc_s
585k13011251272
asked Jan 3 at 11:48
ReudigaReudiga
43
Don't deadlocks occur only when writing to the database? I don't see any code in your question that's doing that.
– robertklep
Jan 3 at 12:00
@robertklep i think so. Thats the only point when i insert like 50 times in a loop into a table. Doesnt see that deadlock on update or something. Only found that error on insert when there much insertions running
– Reudiga
Jan 3 at 12:07
I think it's more of a MySQL issue than a Node.js issue. Perhaps read up on how to prevent deadlocks in MySQL.
– robertklep
Jan 3 at 12:15
@robertklep but what can i do there? I dont know why mysql send deadlocks
– Reudiga
Jan 3 at 12:22
That's why it's advisable to find some documentation on when and why MySQL deadlocks (I don't know either), and see if there are any mitigations that will prevent (or at least limit) deadlocks.
– robertklep
Jan 3 at 12:23
|
show 2 more comments
I have a problem: I changed mysql from createConnection to createPool. But sometimes when many queries will run, I get deadlocks.
Here's the pool
var db_config = {
connectionLimit : 1000,
host: 'localhost',
user: '******',
password: '******',
database: '******.com'
};
pool = mysql.createPool(db_config);
pool.getConnection(function(err, connection) {
if(err) {
logger.error('[ERROR] Connecting to database "' + err.toString() + '"');
setTimeout(function() { database_connection(); }, 2500);
}
else
{
pool.query('SET NAMES utf8');
pool.query('SET CHARACTER SET utf8');
logger.trace('[INFO] Connected to database and set utf8!');
}
});
That's the database connection
But for any reason, when many queries send at same time or something, I get deadlock message
Deadlock found when trying to get lock; try restarting transaction
Every query that used for the queries looks like that
pool.query('SELECT * users');
I read that query combines connection + the query + release, that is what i read.
But I don't know why so much times deadlock... anyone has any ideas? :/
Its InnoDB MySQL Tables
mysql node.js deadlock mysqljs
edited Jan 12 at 15:04
marc_s
585k13011251272
asked Jan 3 at 11:48
ReudigaReudiga
43
I have a problem: I changed mysql from createConnection to createPool. But sometimes when many queries will run, I get deadlocks.
Here's the pool
var db_config = {
connectionLimit : 1000,
host: 'localhost',
user: '******',
password: '******',
database: '******.com'
};
pool = mysql.createPool(db_config);
pool.getConnection(function(err, connection) {
if(err) {
logger.error('[ERROR] Connecting to database "' + err.toString() + '"');
setTimeout(function() { database_connection(); }, 2500);
}
else
{
pool.query('SET NAMES utf8');
pool.query('SET CHARACTER SET utf8');
logger.trace('[INFO] Connected to database and set utf8!');
}
});
That's the database connection
But for any reason, when many queries send at same time or something, I get deadlock message
Deadlock found when trying to get lock; try restarting transaction
Every query that used for the queries looks like that
pool.query('SELECT * users');
I read that query combines connection + the query + release, that is what i read.
But I don't know why so much times deadlock... anyone has any ideas? :/
Its InnoDB MySQL Tables
mysql node.js deadlock mysqljs
mysql node.js deadlock mysqljs
edited Jan 12 at 15:04
marc_s
585k13011251272
asked Jan 3 at 11:48
ReudigaReudiga
43
edited Jan 12 at 15:04
marc_s
585k13011251272
asked Jan 3 at 11:48
ReudigaReudiga
43
edited Jan 12 at 15:04
marc_s
585k13011251272
edited Jan 12 at 15:04
marc_s
585k13011251272
edited Jan 12 at 15:04
marc_s
585k13011251272
585k13011251272
asked Jan 3 at 11:48
ReudigaReudiga
43
asked Jan 3 at 11:48
ReudigaReudiga
43
asked Jan 3 at 11:48
ReudigaReudiga
43
43
Don't deadlocks occur only when writing to the database? I don't see any code in your question that's doing that.
– robertklep
Jan 3 at 12:00
@robertklep i think so. Thats the only point when i insert like 50 times in a loop into a table. Doesnt see that deadlock on update or something. Only found that error on insert when there much insertions running
– Reudiga
Jan 3 at 12:07
I think it's more of a MySQL issue than a Node.js issue. Perhaps read up on how to prevent deadlocks in MySQL.
– robertklep
Jan 3 at 12:15
@robertklep but what can i do there? I dont know why mysql send deadlocks
– Reudiga
Jan 3 at 12:22
That's why it's advisable to find some documentation on when and why MySQL deadlocks (I don't know either), and see if there are any mitigations that will prevent (or at least limit) deadlocks.
– robertklep
Jan 3 at 12:23
|
show 2 more comments
Don't deadlocks occur only when writing to the database? I don't see any code in your question that's doing that.
– robertklep
Jan 3 at 12:00
@robertklep i think so. Thats the only point when i insert like 50 times in a loop into a table. Doesnt see that deadlock on update or something. Only found that error on insert when there much insertions running
– Reudiga
Jan 3 at 12:07
I think it's more of a MySQL issue than a Node.js issue. Perhaps read up on how to prevent deadlocks in MySQL.
– robertklep
Jan 3 at 12:15
@robertklep but what can i do there? I dont know why mysql send deadlocks
– Reudiga
Jan 3 at 12:22
That's why it's advisable to find some documentation on when and why MySQL deadlocks (I don't know either), and see if there are any mitigations that will prevent (or at least limit) deadlocks.
– robertklep
Jan 3 at 12:23
Don't deadlocks occur only when writing to the database? I don't see any code in your question that's doing that.
– robertklep
Jan 3 at 12:00
Don't deadlocks occur only when writing to the database? I don't see any code in your question that's doing that.
– robertklep
Jan 3 at 12:00
@robertklep i think so. Thats the only point when i insert like 50 times in a loop into a table. Doesnt see that deadlock on update or something. Only found that error on insert when there much insertions running
– Reudiga
Jan 3 at 12:07
@robertklep i think so. Thats the only point when i insert like 50 times in a loop into a table. Doesnt see that deadlock on update or something. Only found that error on insert when there much insertions running
– Reudiga
Jan 3 at 12:07
I think it's more of a MySQL issue than a Node.js issue. Perhaps read up on how to prevent deadlocks in MySQL.
– robertklep
Jan 3 at 12:15
I think it's more of a MySQL issue than a Node.js issue. Perhaps read up on how to prevent deadlocks in MySQL.
– robertklep
Jan 3 at 12:15
@robertklep but what can i do there? I dont know why mysql send deadlocks
– Reudiga
Jan 3 at 12:22
@robertklep but what can i do there? I dont know why mysql send deadlocks
– Reudiga
Jan 3 at 12:22
That's why it's advisable to find some documentation on when and why MySQL deadlocks (I don't know either), and see if there are any mitigations that will prevent (or at least limit) deadlocks.
– robertklep
Jan 3 at 12:23
That's why it's advisable to find some documentation on when and why MySQL deadlocks (I don't know either), and see if there are any mitigations that will prevent (or at least limit) deadlocks.
– robertklep
Jan 3 at 12:23
|
show 2 more comments
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54021691%2fnode-js-mysql-createpool-deadlock-sometimes-but-why%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54021691%2fnode-js-mysql-createpool-deadlock-sometimes-but-why%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Don't deadlocks occur only when writing to the database? I don't see any code in your question that's doing that.
– robertklep
Jan 3 at 12:00
@robertklep i think so. Thats the only point when i insert like 50 times in a loop into a table. Doesnt see that deadlock on update or something. Only found that error on insert when there much insertions running
– Reudiga
Jan 3 at 12:07
I think it's more of a MySQL issue than a Node.js issue. Perhaps read up on how to prevent deadlocks in MySQL.
– robertklep
Jan 3 at 12:15
@robertklep but what can i do there? I dont know why mysql send deadlocks
– Reudiga
Jan 3 at 12:22
That's why it's advisable to find some documentation on when and why MySQL deadlocks (I don't know either), and see if there are any mitigations that will prevent (or at least limit) deadlocks.
– robertklep
Jan 3 at 12:23