mySql innoDB write queries locked for long time by read queries
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;
I have a mySql innoDB database with a table (with auto increment primary key). I have many processes performing a read operation on the table. I have other (few) processes that need to write in the same table within a time interval (less than 30 seconds). The write processes sometimes experience a timeout cause they find the table locked (for more than 30 seconds) by the read operations.
Is there a way to prevent this behavior and allow the write processes to write even if other processes are reading?
mysql innodb table-locking
asked Nov 15 '18 at 14:15
AleCat83AleCat83
42931026
add a comment |
I have a mySql innoDB database with a table (with auto increment primary key). I have many processes performing a read operation on the table. I have other (few) processes that need to write in the same table within a time interval (less than 30 seconds). The write processes sometimes experience a timeout cause they find the table locked (for more than 30 seconds) by the read operations.
Is there a way to prevent this behavior and allow the write processes to write even if other processes are reading?
mysql innodb table-locking
asked Nov 15 '18 at 14:15
AleCat83AleCat83
42931026
add a comment |
I have a mySql innoDB database with a table (with auto increment primary key). I have many processes performing a read operation on the table. I have other (few) processes that need to write in the same table within a time interval (less than 30 seconds). The write processes sometimes experience a timeout cause they find the table locked (for more than 30 seconds) by the read operations.
Is there a way to prevent this behavior and allow the write processes to write even if other processes are reading?
mysql innodb table-locking
asked Nov 15 '18 at 14:15
AleCat83AleCat83
42931026
I have a mySql innoDB database with a table (with auto increment primary key). I have many processes performing a read operation on the table. I have other (few) processes that need to write in the same table within a time interval (less than 30 seconds). The write processes sometimes experience a timeout cause they find the table locked (for more than 30 seconds) by the read operations.
Is there a way to prevent this behavior and allow the write processes to write even if other processes are reading?
mysql innodb table-locking
mysql innodb table-locking
asked Nov 15 '18 at 14:15
AleCat83AleCat83
42931026
asked Nov 15 '18 at 14:15
AleCat83AleCat83
42931026
asked Nov 15 '18 at 14:15
AleCat83AleCat83
42931026
asked Nov 15 '18 at 14:15
AleCat83AleCat83
42931026
asked Nov 15 '18 at 14:15
AleCat83AleCat83
42931026
42931026
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
The main cure for such is to speed up the reads. Let's see some of them, together with SHOW CREATE TABLE. Often it is as simple as adding a 'composite' index.
If this is a Data Warehouse application, and the reads are big "reports" that read lots of rows and do a GROUP BY, then an excellent solution is to build and maintain Summary Table(s).
answered Nov 15 '18 at 21:25
Rick JamesRick James
71k567106
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%2f53321402%2fmysql-innodb-write-queries-locked-for-long-time-by-read-queries%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
The main cure for such is to speed up the reads. Let's see some of them, together with SHOW CREATE TABLE. Often it is as simple as adding a 'composite' index.
If this is a Data Warehouse application, and the reads are big "reports" that read lots of rows and do a GROUP BY, then an excellent solution is to build and maintain Summary Table(s).
answered Nov 15 '18 at 21:25
Rick JamesRick James
71k567106
add a comment |
The main cure for such is to speed up the reads. Let's see some of them, together with SHOW CREATE TABLE. Often it is as simple as adding a 'composite' index.
If this is a Data Warehouse application, and the reads are big "reports" that read lots of rows and do a GROUP BY, then an excellent solution is to build and maintain Summary Table(s).
answered Nov 15 '18 at 21:25
Rick JamesRick James
71k567106
add a comment |
The main cure for such is to speed up the reads. Let's see some of them, together with SHOW CREATE TABLE. Often it is as simple as adding a 'composite' index.
If this is a Data Warehouse application, and the reads are big "reports" that read lots of rows and do a GROUP BY, then an excellent solution is to build and maintain Summary Table(s).
answered Nov 15 '18 at 21:25
Rick JamesRick James
71k567106
The main cure for such is to speed up the reads. Let's see some of them, together with SHOW CREATE TABLE. Often it is as simple as adding a 'composite' index.
If this is a Data Warehouse application, and the reads are big "reports" that read lots of rows and do a GROUP BY, then an excellent solution is to build and maintain Summary Table(s).
answered Nov 15 '18 at 21:25
Rick JamesRick James
71k567106
answered Nov 15 '18 at 21:25
Rick JamesRick James
71k567106
answered Nov 15 '18 at 21:25
Rick JamesRick James
71k567106
answered Nov 15 '18 at 21:25
Rick JamesRick James
71k567106
71k567106
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%2f53321402%2fmysql-innodb-write-queries-locked-for-long-time-by-read-queries%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
