Mixing
InnoDB Deadlock History
After a change in code, my database reports a lot of deadlock incidents, which are resolved after a while ( 1 < x < 5 minutes). I use SHOW ENGINE INNODB STATUS to view what happened but the information is not correct: statements and tables reported here are different from what I see in INNODB_LOCKS table (when it's not resolved yet).
The question is: How can I get a history, a log, of what deadlocks happened? not just the most recent one.
mariadb innodb database-deadlocks
asked Nov 20 '18 at 11:45
Amir MAmir M
10613
add a comment |
After a change in code, my database reports a lot of deadlock incidents, which are resolved after a while ( 1 < x < 5 minutes). I use SHOW ENGINE INNODB STATUS to view what happened but the information is not correct: statements and tables reported here are different from what I see in INNODB_LOCKS table (when it's not resolved yet).
The question is: How can I get a history, a log, of what deadlocks happened? not just the most recent one.
mariadb innodb database-deadlocks
asked Nov 20 '18 at 11:45
Amir MAmir M
10613
add a comment |
After a change in code, my database reports a lot of deadlock incidents, which are resolved after a while ( 1 < x < 5 minutes). I use SHOW ENGINE INNODB STATUS to view what happened but the information is not correct: statements and tables reported here are different from what I see in INNODB_LOCKS table (when it's not resolved yet).
The question is: How can I get a history, a log, of what deadlocks happened? not just the most recent one.
mariadb innodb database-deadlocks
asked Nov 20 '18 at 11:45
Amir MAmir M
10613
After a change in code, my database reports a lot of deadlock incidents, which are resolved after a while ( 1 < x < 5 minutes). I use SHOW ENGINE INNODB STATUS to view what happened but the information is not correct: statements and tables reported here are different from what I see in INNODB_LOCKS table (when it's not resolved yet).
The question is: How can I get a history, a log, of what deadlocks happened? not just the most recent one.
mariadb innodb database-deadlocks
mariadb innodb database-deadlocks
asked Nov 20 '18 at 11:45
Amir MAmir M
10613
asked Nov 20 '18 at 11:45
Amir MAmir M
10613
asked Nov 20 '18 at 11:45
Amir MAmir M
10613
asked Nov 20 '18 at 11:45
Amir MAmir M
10613
asked Nov 20 '18 at 11:45
Amir MAmir M
10613
10613
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
innodb_print_all_deadlocks = ON
SHOW ENGINE INNODB STATUS is transient; the above should persist it by writing to a log file.
I recommend a few things to decrease the number of deadlocks:
- Do thing is the same order in different transactions. This includes which rows are touched.
- Speed up the code. (Better indexes, often 'composite', reformulate queries, etc)
- See if anything can reasonably be pulled out of the
BEGIN...COMMIT.
For further discussion, please show us the SQL in a transaction, plus SHOW CREATE TABLE for the relevant tables.
In any case, test for errors throughout each transaction and be ready to replay when you hit a deadlock.
Note: lock_wait_timeout is a related item. It defaults to an unreasonably high 50 seconds. If you raise that you are asking for more trouble. Decreasing it (to, say, 5) is not a real solution, but it may change the problems in interesting ways. Again, test for errors and react to them. Hitting this "timeout" is as bad as a "deadlock". Not hitting it, but waiting, is a silent way that InnoDB resolves conflicts successfully.
answered Nov 20 '18 at 16:44
Rick JamesRick James
67.1k55899
add a comment |
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%2f53392324%2finnodb-deadlock-history%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
innodb_print_all_deadlocks = ON
SHOW ENGINE INNODB STATUS is transient; the above should persist it by writing to a log file.
I recommend a few things to decrease the number of deadlocks:
- Do thing is the same order in different transactions. This includes which rows are touched.
- Speed up the code. (Better indexes, often 'composite', reformulate queries, etc)
- See if anything can reasonably be pulled out of the
BEGIN...COMMIT.
For further discussion, please show us the SQL in a transaction, plus SHOW CREATE TABLE for the relevant tables.
In any case, test for errors throughout each transaction and be ready to replay when you hit a deadlock.
Note: lock_wait_timeout is a related item. It defaults to an unreasonably high 50 seconds. If you raise that you are asking for more trouble. Decreasing it (to, say, 5) is not a real solution, but it may change the problems in interesting ways. Again, test for errors and react to them. Hitting this "timeout" is as bad as a "deadlock". Not hitting it, but waiting, is a silent way that InnoDB resolves conflicts successfully.
answered Nov 20 '18 at 16:44
Rick JamesRick James
67.1k55899
add a comment |
innodb_print_all_deadlocks = ON
SHOW ENGINE INNODB STATUS is transient; the above should persist it by writing to a log file.
I recommend a few things to decrease the number of deadlocks:
- Do thing is the same order in different transactions. This includes which rows are touched.
- Speed up the code. (Better indexes, often 'composite', reformulate queries, etc)
- See if anything can reasonably be pulled out of the
BEGIN...COMMIT.
For further discussion, please show us the SQL in a transaction, plus SHOW CREATE TABLE for the relevant tables.
In any case, test for errors throughout each transaction and be ready to replay when you hit a deadlock.
Note: lock_wait_timeout is a related item. It defaults to an unreasonably high 50 seconds. If you raise that you are asking for more trouble. Decreasing it (to, say, 5) is not a real solution, but it may change the problems in interesting ways. Again, test for errors and react to them. Hitting this "timeout" is as bad as a "deadlock". Not hitting it, but waiting, is a silent way that InnoDB resolves conflicts successfully.
answered Nov 20 '18 at 16:44
Rick JamesRick James
67.1k55899
add a comment |
innodb_print_all_deadlocks = ON
SHOW ENGINE INNODB STATUS is transient; the above should persist it by writing to a log file.
I recommend a few things to decrease the number of deadlocks:
- Do thing is the same order in different transactions. This includes which rows are touched.
- Speed up the code. (Better indexes, often 'composite', reformulate queries, etc)
- See if anything can reasonably be pulled out of the
BEGIN...COMMIT.
For further discussion, please show us the SQL in a transaction, plus SHOW CREATE TABLE for the relevant tables.
In any case, test for errors throughout each transaction and be ready to replay when you hit a deadlock.
Note: lock_wait_timeout is a related item. It defaults to an unreasonably high 50 seconds. If you raise that you are asking for more trouble. Decreasing it (to, say, 5) is not a real solution, but it may change the problems in interesting ways. Again, test for errors and react to them. Hitting this "timeout" is as bad as a "deadlock". Not hitting it, but waiting, is a silent way that InnoDB resolves conflicts successfully.
answered Nov 20 '18 at 16:44
Rick JamesRick James
67.1k55899
innodb_print_all_deadlocks = ON
SHOW ENGINE INNODB STATUS is transient; the above should persist it by writing to a log file.
I recommend a few things to decrease the number of deadlocks:
- Do thing is the same order in different transactions. This includes which rows are touched.
- Speed up the code. (Better indexes, often 'composite', reformulate queries, etc)
- See if anything can reasonably be pulled out of the
BEGIN...COMMIT.
For further discussion, please show us the SQL in a transaction, plus SHOW CREATE TABLE for the relevant tables.
In any case, test for errors throughout each transaction and be ready to replay when you hit a deadlock.
Note: lock_wait_timeout is a related item. It defaults to an unreasonably high 50 seconds. If you raise that you are asking for more trouble. Decreasing it (to, say, 5) is not a real solution, but it may change the problems in interesting ways. Again, test for errors and react to them. Hitting this "timeout" is as bad as a "deadlock". Not hitting it, but waiting, is a silent way that InnoDB resolves conflicts successfully.
answered Nov 20 '18 at 16:44
Rick JamesRick James
67.1k55899
answered Nov 20 '18 at 16:44
Rick JamesRick James
67.1k55899
answered Nov 20 '18 at 16:44
Rick JamesRick James
67.1k55899
answered Nov 20 '18 at 16:44
Rick JamesRick James
67.1k55899
67.1k55899
add a comment |
add a comment |
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%2f53392324%2finnodb-deadlock-history%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
