InnoDB: will uncommitted transaction produce redo log?
Before MySQL commit a transaction, it will write REDO log first, then commit the transaction, that is write ahead log.
start transaction;
update users set uuid = UUID() from user where id = 1
update users set uuid = UUID() from user where id = 2
update users set uuid = UUID() from user where id = 3
...
...
update users set uuid = UUID() from user where id = 1,000,000
// not yet commit
If a transaction is going to update 1 million records, which takes 100 seconds. During the period of execution, will this uncommitted transaction produce redo log?
mysql innodb transaction-log recovery
asked Dec 21 '18 at 6:09
Ryan LvRyan Lv
6318
add a comment |
Before MySQL commit a transaction, it will write REDO log first, then commit the transaction, that is write ahead log.
start transaction;
update users set uuid = UUID() from user where id = 1
update users set uuid = UUID() from user where id = 2
update users set uuid = UUID() from user where id = 3
...
...
update users set uuid = UUID() from user where id = 1,000,000
// not yet commit
If a transaction is going to update 1 million records, which takes 100 seconds. During the period of execution, will this uncommitted transaction produce redo log?
mysql innodb transaction-log recovery
asked Dec 21 '18 at 6:09
Ryan LvRyan Lv
6318
What index(es) are there?
– Rick James
Dec 27 '18 at 16:04
add a comment |
Before MySQL commit a transaction, it will write REDO log first, then commit the transaction, that is write ahead log.
start transaction;
update users set uuid = UUID() from user where id = 1
update users set uuid = UUID() from user where id = 2
update users set uuid = UUID() from user where id = 3
...
...
update users set uuid = UUID() from user where id = 1,000,000
// not yet commit
If a transaction is going to update 1 million records, which takes 100 seconds. During the period of execution, will this uncommitted transaction produce redo log?
mysql innodb transaction-log recovery
asked Dec 21 '18 at 6:09
Ryan LvRyan Lv
6318
Before MySQL commit a transaction, it will write REDO log first, then commit the transaction, that is write ahead log.
start transaction;
update users set uuid = UUID() from user where id = 1
update users set uuid = UUID() from user where id = 2
update users set uuid = UUID() from user where id = 3
...
...
update users set uuid = UUID() from user where id = 1,000,000
// not yet commit
If a transaction is going to update 1 million records, which takes 100 seconds. During the period of execution, will this uncommitted transaction produce redo log?
mysql innodb transaction-log recovery
mysql innodb transaction-log recovery
asked Dec 21 '18 at 6:09
Ryan LvRyan Lv
6318
asked Dec 21 '18 at 6:09
Ryan LvRyan Lv
6318
edited Dec 21 '18 at 6:48
Ryan Lv
asked Dec 21 '18 at 6:09
Ryan LvRyan Lv
6318
asked Dec 21 '18 at 6:09
Ryan LvRyan Lv
6318
asked Dec 21 '18 at 6:09
Ryan LvRyan Lv
6318
6318
What index(es) are there?
– Rick James
Dec 27 '18 at 16:04
add a comment |
What index(es) are there?
– Rick James
Dec 27 '18 at 16:04
What index(es) are there?
– Rick James
Dec 27 '18 at 16:04
What index(es) are there?
– Rick James
Dec 27 '18 at 16:04
add a comment |
3 Answers
3
active
oldest
votes
innodb produce redo log during transaction and may sync to disk even if the transaction has not committed.
answered Dec 21 '18 at 6:13
depeng jidepeng ji
111
1
Could you please provide the documentation or reference?
– Ryan Lv
Dec 21 '18 at 6:51
add a comment |
The log_buffer is a finite size. When it overflows, stuff will be written to the logfile. Regardless of the value of innodb_flatc, the transaction will eventually be flushed to disk.
answered Dec 21 '18 at 6:14
Rick JamesRick James
41.7k22258
Hi Rick, thanks for you reply. I updated my question to make it more clean.
– Ryan Lv
Dec 21 '18 at 6:42
add a comment |
Based on Jeremy Cole's presentation InnoDB: A journey to the core III in Percona Live MySQL Conference 2015.
When the transaction is first started:
- A transaction ID (TRX_ID) is assigned and may be written to the highest transaction ID field in the TRX_SYS page. A record of the TRX_SYS page modification is redo logged if the field
- A read view is created based on the assigned TRX_ID.
Record modification
Each time the UPDATE modifies a record:
- Undo log space is allocated.
- Previous values from record are copied to undo log.
- Record of undo log modifications are written to redo log.
- Page is modified in buffer pool; rollback pointer is pointed to
previous version written in undo log. - Record of page modifications are written to redo log.
- Page is marked as “dirty” (needs to be flushed to disk).
Therefore the answer is yes.
Transaction commit
When the transaction is committed (implicitly or explicitly):
- Undo log page state is set to “purge” (meaning it can be cleaned up when it’s no longer needed).
- Record of undo log modifications are written to redo log.
- Redo log buffer is flushed to disk (depending on the setting of
innodb_flush_log_at_trx_commit).
Conclusion
Will uncommitted transaction produce redo log?
Yes.
Reference
- https://www.percona.com/live/mysql-conference-2015
- https://docplayer.net/62963586-Innodb-a-journey-to-the-core-ii-jeremy-cole-and-davi-arnaut.html
answered 8 mins ago
Ryan LvRyan Lv
6318
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
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: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
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%2fdba.stackexchange.com%2fquestions%2f225532%2finnodb-will-uncommitted-transaction-produce-redo-log%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
innodb produce redo log during transaction and may sync to disk even if the transaction has not committed.
answered Dec 21 '18 at 6:13
depeng jidepeng ji
111
1
Could you please provide the documentation or reference?
– Ryan Lv
Dec 21 '18 at 6:51
add a comment |
innodb produce redo log during transaction and may sync to disk even if the transaction has not committed.
answered Dec 21 '18 at 6:13
depeng jidepeng ji
111
1
Could you please provide the documentation or reference?
– Ryan Lv
Dec 21 '18 at 6:51
add a comment |
innodb produce redo log during transaction and may sync to disk even if the transaction has not committed.
answered Dec 21 '18 at 6:13
depeng jidepeng ji
111
innodb produce redo log during transaction and may sync to disk even if the transaction has not committed.
answered Dec 21 '18 at 6:13
depeng jidepeng ji
111
answered Dec 21 '18 at 6:13
depeng jidepeng ji
111
answered Dec 21 '18 at 6:13
depeng jidepeng ji
111
answered Dec 21 '18 at 6:13
depeng jidepeng ji
111
111
1
Could you please provide the documentation or reference?
– Ryan Lv
Dec 21 '18 at 6:51
add a comment |
1
Could you please provide the documentation or reference?
– Ryan Lv
Dec 21 '18 at 6:51
1
1
Could you please provide the documentation or reference?
– Ryan Lv
Dec 21 '18 at 6:51
Could you please provide the documentation or reference?
– Ryan Lv
Dec 21 '18 at 6:51
add a comment |
The log_buffer is a finite size. When it overflows, stuff will be written to the logfile. Regardless of the value of innodb_flatc, the transaction will eventually be flushed to disk.
answered Dec 21 '18 at 6:14
Rick JamesRick James
41.7k22258
Hi Rick, thanks for you reply. I updated my question to make it more clean.
– Ryan Lv
Dec 21 '18 at 6:42
add a comment |
The log_buffer is a finite size. When it overflows, stuff will be written to the logfile. Regardless of the value of innodb_flatc, the transaction will eventually be flushed to disk.
answered Dec 21 '18 at 6:14
Rick JamesRick James
41.7k22258
Hi Rick, thanks for you reply. I updated my question to make it more clean.
– Ryan Lv
Dec 21 '18 at 6:42
add a comment |
The log_buffer is a finite size. When it overflows, stuff will be written to the logfile. Regardless of the value of innodb_flatc, the transaction will eventually be flushed to disk.
answered Dec 21 '18 at 6:14
Rick JamesRick James
41.7k22258
The log_buffer is a finite size. When it overflows, stuff will be written to the logfile. Regardless of the value of innodb_flatc, the transaction will eventually be flushed to disk.
answered Dec 21 '18 at 6:14
Rick JamesRick James
41.7k22258
answered Dec 21 '18 at 6:14
Rick JamesRick James
41.7k22258
answered Dec 21 '18 at 6:14
Rick JamesRick James
41.7k22258
answered Dec 21 '18 at 6:14
Rick JamesRick James
41.7k22258
41.7k22258
Hi Rick, thanks for you reply. I updated my question to make it more clean.
– Ryan Lv
Dec 21 '18 at 6:42
add a comment |
Hi Rick, thanks for you reply. I updated my question to make it more clean.
– Ryan Lv
Dec 21 '18 at 6:42
Hi Rick, thanks for you reply. I updated my question to make it more clean.
– Ryan Lv
Dec 21 '18 at 6:42
Hi Rick, thanks for you reply. I updated my question to make it more clean.
– Ryan Lv
Dec 21 '18 at 6:42
add a comment |
Based on Jeremy Cole's presentation InnoDB: A journey to the core III in Percona Live MySQL Conference 2015.
When the transaction is first started:
- A transaction ID (TRX_ID) is assigned and may be written to the highest transaction ID field in the TRX_SYS page. A record of the TRX_SYS page modification is redo logged if the field
- A read view is created based on the assigned TRX_ID.
Record modification
Each time the UPDATE modifies a record:
- Undo log space is allocated.
- Previous values from record are copied to undo log.
- Record of undo log modifications are written to redo log.
- Page is modified in buffer pool; rollback pointer is pointed to
previous version written in undo log. - Record of page modifications are written to redo log.
- Page is marked as “dirty” (needs to be flushed to disk).
Therefore the answer is yes.
Transaction commit
When the transaction is committed (implicitly or explicitly):
- Undo log page state is set to “purge” (meaning it can be cleaned up when it’s no longer needed).
- Record of undo log modifications are written to redo log.
- Redo log buffer is flushed to disk (depending on the setting of
innodb_flush_log_at_trx_commit).
Conclusion
Will uncommitted transaction produce redo log?
Yes.
Reference
- https://www.percona.com/live/mysql-conference-2015
- https://docplayer.net/62963586-Innodb-a-journey-to-the-core-ii-jeremy-cole-and-davi-arnaut.html
answered 8 mins ago
Ryan LvRyan Lv
6318
add a comment |
Based on Jeremy Cole's presentation InnoDB: A journey to the core III in Percona Live MySQL Conference 2015.
When the transaction is first started:
- A transaction ID (TRX_ID) is assigned and may be written to the highest transaction ID field in the TRX_SYS page. A record of the TRX_SYS page modification is redo logged if the field
- A read view is created based on the assigned TRX_ID.
Record modification
Each time the UPDATE modifies a record:
- Undo log space is allocated.
- Previous values from record are copied to undo log.
- Record of undo log modifications are written to redo log.
- Page is modified in buffer pool; rollback pointer is pointed to
previous version written in undo log. - Record of page modifications are written to redo log.
- Page is marked as “dirty” (needs to be flushed to disk).
Therefore the answer is yes.
Transaction commit
When the transaction is committed (implicitly or explicitly):
- Undo log page state is set to “purge” (meaning it can be cleaned up when it’s no longer needed).
- Record of undo log modifications are written to redo log.
- Redo log buffer is flushed to disk (depending on the setting of
innodb_flush_log_at_trx_commit).
Conclusion
Will uncommitted transaction produce redo log?
Yes.
Reference
- https://www.percona.com/live/mysql-conference-2015
- https://docplayer.net/62963586-Innodb-a-journey-to-the-core-ii-jeremy-cole-and-davi-arnaut.html
answered 8 mins ago
Ryan LvRyan Lv
6318
add a comment |
Based on Jeremy Cole's presentation InnoDB: A journey to the core III in Percona Live MySQL Conference 2015.
When the transaction is first started:
- A transaction ID (TRX_ID) is assigned and may be written to the highest transaction ID field in the TRX_SYS page. A record of the TRX_SYS page modification is redo logged if the field
- A read view is created based on the assigned TRX_ID.
Record modification
Each time the UPDATE modifies a record:
- Undo log space is allocated.
- Previous values from record are copied to undo log.
- Record of undo log modifications are written to redo log.
- Page is modified in buffer pool; rollback pointer is pointed to
previous version written in undo log. - Record of page modifications are written to redo log.
- Page is marked as “dirty” (needs to be flushed to disk).
Therefore the answer is yes.
Transaction commit
When the transaction is committed (implicitly or explicitly):
- Undo log page state is set to “purge” (meaning it can be cleaned up when it’s no longer needed).
- Record of undo log modifications are written to redo log.
- Redo log buffer is flushed to disk (depending on the setting of
innodb_flush_log_at_trx_commit).
Conclusion
Will uncommitted transaction produce redo log?
Yes.
Reference
- https://www.percona.com/live/mysql-conference-2015
- https://docplayer.net/62963586-Innodb-a-journey-to-the-core-ii-jeremy-cole-and-davi-arnaut.html
answered 8 mins ago
Ryan LvRyan Lv
6318
Based on Jeremy Cole's presentation InnoDB: A journey to the core III in Percona Live MySQL Conference 2015.
When the transaction is first started:
- A transaction ID (TRX_ID) is assigned and may be written to the highest transaction ID field in the TRX_SYS page. A record of the TRX_SYS page modification is redo logged if the field
- A read view is created based on the assigned TRX_ID.
Record modification
Each time the UPDATE modifies a record:
- Undo log space is allocated.
- Previous values from record are copied to undo log.
- Record of undo log modifications are written to redo log.
- Page is modified in buffer pool; rollback pointer is pointed to
previous version written in undo log. - Record of page modifications are written to redo log.
- Page is marked as “dirty” (needs to be flushed to disk).
Therefore the answer is yes.
Transaction commit
When the transaction is committed (implicitly or explicitly):
- Undo log page state is set to “purge” (meaning it can be cleaned up when it’s no longer needed).
- Record of undo log modifications are written to redo log.
- Redo log buffer is flushed to disk (depending on the setting of
innodb_flush_log_at_trx_commit).
Conclusion
Will uncommitted transaction produce redo log?
Yes.
Reference
- https://www.percona.com/live/mysql-conference-2015
- https://docplayer.net/62963586-Innodb-a-journey-to-the-core-ii-jeremy-cole-and-davi-arnaut.html
answered 8 mins ago
Ryan LvRyan Lv
6318
answered 8 mins ago
Ryan LvRyan Lv
6318
answered 8 mins ago
Ryan LvRyan Lv
6318
answered 8 mins ago
Ryan LvRyan Lv
6318
6318
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- 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%2fdba.stackexchange.com%2fquestions%2f225532%2finnodb-will-uncommitted-transaction-produce-redo-log%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
What index(es) are there?
– Rick James
Dec 27 '18 at 16:04