What Phenomena does MySQL try to prevent by locking the whole table upon executing Delete statement with the...
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
Using the MySQL isolation level of Repeatable Read.
Given table test having non-indexed column quantity:
id | quantity
--------------------
1 | 10
2 | 20
3 | 30
Tx1 executes 1st, note it is not committed yet, meaning that all the acquired locks are not released yet.
Tx1:
START TRANSACTION;
DELETE FROM test WHERE quantity=10;
Now executing Tx2
Tx2:
START TRANSACTION;
INSERT INTO test(quantity) VALUES (40);
COMMIT;
For Tx2 I get the following result:
Lock wait timeout exceeded; try restarting transaction
I understand that, as the quantity column is not indexed, the delete statement does a full table scan, locks all the rows( doesn't matter the where condition matches or not) and also applies gap locks before every and after the last index record in a Clustered Index resulting in a fully blocked table hence the insert statement from tx2 cannot acquire a lock for a row to be inserted.
From MySQL manual(for Repeatable Read isolation level):
For a unique index with a unique search condition, InnoDB locks only the index record found, not the gap before it.
For other search conditions, InnoDB locks the index range scanned, using gap locks or next-key locks to block insertions by other sessions into the gaps covered by the range (this is used in my case).
Taking into account that the locking in any given isolation level is applied for preventing the phenomenas I'm a little bit confused what is the reason to block the whole table in this case, I mean what kind of phenomena is prevented with blocking the whole table in this case ?
mysql database locking relational-database database-administration
edited Nov 25 '18 at 15:08
Vlad Mihalcea
61.4k14177491
asked Nov 23 '18 at 20:24
Suren AznauryanSuren Aznauryan
370110
add a comment |
Using the MySQL isolation level of Repeatable Read.
Given table test having non-indexed column quantity:
id | quantity
--------------------
1 | 10
2 | 20
3 | 30
Tx1 executes 1st, note it is not committed yet, meaning that all the acquired locks are not released yet.
Tx1:
START TRANSACTION;
DELETE FROM test WHERE quantity=10;
Now executing Tx2
Tx2:
START TRANSACTION;
INSERT INTO test(quantity) VALUES (40);
COMMIT;
For Tx2 I get the following result:
Lock wait timeout exceeded; try restarting transaction
I understand that, as the quantity column is not indexed, the delete statement does a full table scan, locks all the rows( doesn't matter the where condition matches or not) and also applies gap locks before every and after the last index record in a Clustered Index resulting in a fully blocked table hence the insert statement from tx2 cannot acquire a lock for a row to be inserted.
From MySQL manual(for Repeatable Read isolation level):
For a unique index with a unique search condition, InnoDB locks only the index record found, not the gap before it.
For other search conditions, InnoDB locks the index range scanned, using gap locks or next-key locks to block insertions by other sessions into the gaps covered by the range (this is used in my case).
Taking into account that the locking in any given isolation level is applied for preventing the phenomenas I'm a little bit confused what is the reason to block the whole table in this case, I mean what kind of phenomena is prevented with blocking the whole table in this case ?
mysql database locking relational-database database-administration
edited Nov 25 '18 at 15:08
Vlad Mihalcea
61.4k14177491
asked Nov 23 '18 at 20:24
Suren AznauryanSuren Aznauryan
370110
Read the manual & some textbook(s) re locking used with MVCC. MVCC uses locking but it doesn't use it the way that non-MVCC DBMSs do. Its locking is not according to the obvious scheme that corresponds to the SQL phenomena prevention regime. Anyway you should be justifying your exectations by referencing the manual, then we can tell you where you went wrong & right; otherwise we have to just rewrite the manual with a bespoke tutorial, which is "too broad".
– philipxy
Nov 23 '18 at 20:32
What I've wrote in my question about how is locking done in this case(record locks on all records in clustered index plus gap locks before every index record and after last index record) i've read in manual where it is written how innodb does locking in Repeatable Read isolation level, but i didn't find why in this particular case locking is done in a way it is done. I'm asking a concrete question about why in this case locking is done that way, what phenomana Innodb tries to prevent ? Can you bring me an example? There should be a reason why is it locking whole table
– Suren Aznauryan
Nov 24 '18 at 16:25
add a comment |
Using the MySQL isolation level of Repeatable Read.
Given table test having non-indexed column quantity:
id | quantity
--------------------
1 | 10
2 | 20
3 | 30
Tx1 executes 1st, note it is not committed yet, meaning that all the acquired locks are not released yet.
Tx1:
START TRANSACTION;
DELETE FROM test WHERE quantity=10;
Now executing Tx2
Tx2:
START TRANSACTION;
INSERT INTO test(quantity) VALUES (40);
COMMIT;
For Tx2 I get the following result:
Lock wait timeout exceeded; try restarting transaction
I understand that, as the quantity column is not indexed, the delete statement does a full table scan, locks all the rows( doesn't matter the where condition matches or not) and also applies gap locks before every and after the last index record in a Clustered Index resulting in a fully blocked table hence the insert statement from tx2 cannot acquire a lock for a row to be inserted.
From MySQL manual(for Repeatable Read isolation level):
For a unique index with a unique search condition, InnoDB locks only the index record found, not the gap before it.
For other search conditions, InnoDB locks the index range scanned, using gap locks or next-key locks to block insertions by other sessions into the gaps covered by the range (this is used in my case).
Taking into account that the locking in any given isolation level is applied for preventing the phenomenas I'm a little bit confused what is the reason to block the whole table in this case, I mean what kind of phenomena is prevented with blocking the whole table in this case ?
mysql database locking relational-database database-administration
edited Nov 25 '18 at 15:08
Vlad Mihalcea
61.4k14177491
asked Nov 23 '18 at 20:24
Suren AznauryanSuren Aznauryan
370110
Using the MySQL isolation level of Repeatable Read.
Given table test having non-indexed column quantity:
id | quantity
--------------------
1 | 10
2 | 20
3 | 30
Tx1 executes 1st, note it is not committed yet, meaning that all the acquired locks are not released yet.
Tx1:
START TRANSACTION;
DELETE FROM test WHERE quantity=10;
Now executing Tx2
Tx2:
START TRANSACTION;
INSERT INTO test(quantity) VALUES (40);
COMMIT;
For Tx2 I get the following result:
Lock wait timeout exceeded; try restarting transaction
I understand that, as the quantity column is not indexed, the delete statement does a full table scan, locks all the rows( doesn't matter the where condition matches or not) and also applies gap locks before every and after the last index record in a Clustered Index resulting in a fully blocked table hence the insert statement from tx2 cannot acquire a lock for a row to be inserted.
From MySQL manual(for Repeatable Read isolation level):
For a unique index with a unique search condition, InnoDB locks only the index record found, not the gap before it.
For other search conditions, InnoDB locks the index range scanned, using gap locks or next-key locks to block insertions by other sessions into the gaps covered by the range (this is used in my case).
Taking into account that the locking in any given isolation level is applied for preventing the phenomenas I'm a little bit confused what is the reason to block the whole table in this case, I mean what kind of phenomena is prevented with blocking the whole table in this case ?
mysql database locking relational-database database-administration
mysql database locking relational-database database-administration
edited Nov 25 '18 at 15:08
Vlad Mihalcea
61.4k14177491
asked Nov 23 '18 at 20:24
Suren AznauryanSuren Aznauryan
370110
edited Nov 25 '18 at 15:08
Vlad Mihalcea
61.4k14177491
asked Nov 23 '18 at 20:24
Suren AznauryanSuren Aznauryan
370110
edited Nov 25 '18 at 15:08
Vlad Mihalcea
61.4k14177491
edited Nov 25 '18 at 15:08
Vlad Mihalcea
61.4k14177491
edited Nov 25 '18 at 15:08
Vlad Mihalcea
61.4k14177491
61.4k14177491
asked Nov 23 '18 at 20:24
Suren AznauryanSuren Aznauryan
370110
asked Nov 23 '18 at 20:24
Suren AznauryanSuren Aznauryan
370110
asked Nov 23 '18 at 20:24
Suren AznauryanSuren Aznauryan
370110
370110
Read the manual & some textbook(s) re locking used with MVCC. MVCC uses locking but it doesn't use it the way that non-MVCC DBMSs do. Its locking is not according to the obvious scheme that corresponds to the SQL phenomena prevention regime. Anyway you should be justifying your exectations by referencing the manual, then we can tell you where you went wrong & right; otherwise we have to just rewrite the manual with a bespoke tutorial, which is "too broad".
– philipxy
Nov 23 '18 at 20:32
What I've wrote in my question about how is locking done in this case(record locks on all records in clustered index plus gap locks before every index record and after last index record) i've read in manual where it is written how innodb does locking in Repeatable Read isolation level, but i didn't find why in this particular case locking is done in a way it is done. I'm asking a concrete question about why in this case locking is done that way, what phenomana Innodb tries to prevent ? Can you bring me an example? There should be a reason why is it locking whole table
– Suren Aznauryan
Nov 24 '18 at 16:25
add a comment |
Read the manual & some textbook(s) re locking used with MVCC. MVCC uses locking but it doesn't use it the way that non-MVCC DBMSs do. Its locking is not according to the obvious scheme that corresponds to the SQL phenomena prevention regime. Anyway you should be justifying your exectations by referencing the manual, then we can tell you where you went wrong & right; otherwise we have to just rewrite the manual with a bespoke tutorial, which is "too broad".
– philipxy
Nov 23 '18 at 20:32
What I've wrote in my question about how is locking done in this case(record locks on all records in clustered index plus gap locks before every index record and after last index record) i've read in manual where it is written how innodb does locking in Repeatable Read isolation level, but i didn't find why in this particular case locking is done in a way it is done. I'm asking a concrete question about why in this case locking is done that way, what phenomana Innodb tries to prevent ? Can you bring me an example? There should be a reason why is it locking whole table
– Suren Aznauryan
Nov 24 '18 at 16:25
Read the manual & some textbook(s) re locking used with MVCC. MVCC uses locking but it doesn't use it the way that non-MVCC DBMSs do. Its locking is not according to the obvious scheme that corresponds to the SQL phenomena prevention regime. Anyway you should be justifying your exectations by referencing the manual, then we can tell you where you went wrong & right; otherwise we have to just rewrite the manual with a bespoke tutorial, which is "too broad".
– philipxy
Nov 23 '18 at 20:32
Read the manual & some textbook(s) re locking used with MVCC. MVCC uses locking but it doesn't use it the way that non-MVCC DBMSs do. Its locking is not according to the obvious scheme that corresponds to the SQL phenomena prevention regime. Anyway you should be justifying your exectations by referencing the manual, then we can tell you where you went wrong & right; otherwise we have to just rewrite the manual with a bespoke tutorial, which is "too broad".
– philipxy
Nov 23 '18 at 20:32
What I've wrote in my question about how is locking done in this case(record locks on all records in clustered index plus gap locks before every index record and after last index record) i've read in manual where it is written how innodb does locking in Repeatable Read isolation level, but i didn't find why in this particular case locking is done in a way it is done. I'm asking a concrete question about why in this case locking is done that way, what phenomana Innodb tries to prevent ? Can you bring me an example? There should be a reason why is it locking whole table
– Suren Aznauryan
Nov 24 '18 at 16:25
What I've wrote in my question about how is locking done in this case(record locks on all records in clustered index plus gap locks before every index record and after last index record) i've read in manual where it is written how innodb does locking in Repeatable Read isolation level, but i didn't find why in this particular case locking is done in a way it is done. I'm asking a concrete question about why in this case locking is done that way, what phenomana Innodb tries to prevent ? Can you bring me an example? There should be a reason why is it locking whole table
– Suren Aznauryan
Nov 24 '18 at 16:25
add a comment |
1 Answer
1
active
oldest
votes
By default, InnoDB uses consistent snapshots in Repeatable Read isolation level, meaning that you get repeatable reads for both tuples and ranges.
Even if the SQL standard says that Phantom Reads are prevented by Serializable, and that Repeatable Read might not prevent it, it is not a problem if a lower isolation level provides better guarantees than what the standard has it mind.
For more details about how gap locking works, check out this article.
answered Nov 25 '18 at 15:07
Vlad MihalceaVlad Mihalcea
61.4k14177491
So it is done for preventing Phantom Reads, right ? In my case that kind of locking ensures that after theDELETEstatement execution until the end of my transaction whenever I executeSELECT FROM test WHERE quantity=10;I will always see an empty result set.
– Suren Aznauryan
Nov 25 '18 at 17:08
The rear-your-write behavior does not come from locking, but from the MVCC )Multi-Version Concurrency Control) engine employed by InnoDB.
– Vlad Mihalcea
Nov 25 '18 at 19:59
The articles you gave the links for are completely clear to me. The only thing is not clear is why in case I have described the whole table is locked with clustered index row locks and gap locks everywhere. Why some tx2 cannot insert, delete, update any record in a table after tx1 executesdeletestatement with a condition on non-indexed column(assuming that tx1 is still in progress) ? There should be a reason, right ? What could happen otherwise ? I think I need some examples of some possible phenomena(or some bad stuff in general) happening in case if whole table is not locked in this case
– Suren Aznauryan
Nov 25 '18 at 22:34
You can find a detailed explanation of these phenomena in the High-Performance Java Persistence book with examples as well.
– Vlad Mihalcea
Nov 26 '18 at 4:56
I'm reading that book already :) Sorry, maybe I'm not clear on my question, but still I didn't get the response for it. What I'm asking is simple: what phenomena does the "locking the whole table" prevent here ? If it is the Phantom Read phenomena, then it is not clear to me how Phantom Read could possibly occur in this case if the whole table would not be locked.
– Suren Aznauryan
Nov 26 '18 at 16:37
|
show 2 more comments
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%2f53452571%2fwhat-phenomena-does-mysql-try-to-prevent-by-locking-the-whole-table-upon-executi%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
By default, InnoDB uses consistent snapshots in Repeatable Read isolation level, meaning that you get repeatable reads for both tuples and ranges.
Even if the SQL standard says that Phantom Reads are prevented by Serializable, and that Repeatable Read might not prevent it, it is not a problem if a lower isolation level provides better guarantees than what the standard has it mind.
For more details about how gap locking works, check out this article.
answered Nov 25 '18 at 15:07
Vlad MihalceaVlad Mihalcea
61.4k14177491
So it is done for preventing Phantom Reads, right ? In my case that kind of locking ensures that after theDELETEstatement execution until the end of my transaction whenever I executeSELECT FROM test WHERE quantity=10;I will always see an empty result set.
– Suren Aznauryan
Nov 25 '18 at 17:08
The rear-your-write behavior does not come from locking, but from the MVCC )Multi-Version Concurrency Control) engine employed by InnoDB.
– Vlad Mihalcea
Nov 25 '18 at 19:59
The articles you gave the links for are completely clear to me. The only thing is not clear is why in case I have described the whole table is locked with clustered index row locks and gap locks everywhere. Why some tx2 cannot insert, delete, update any record in a table after tx1 executesdeletestatement with a condition on non-indexed column(assuming that tx1 is still in progress) ? There should be a reason, right ? What could happen otherwise ? I think I need some examples of some possible phenomena(or some bad stuff in general) happening in case if whole table is not locked in this case
– Suren Aznauryan
Nov 25 '18 at 22:34
You can find a detailed explanation of these phenomena in the High-Performance Java Persistence book with examples as well.
– Vlad Mihalcea
Nov 26 '18 at 4:56
I'm reading that book already :) Sorry, maybe I'm not clear on my question, but still I didn't get the response for it. What I'm asking is simple: what phenomena does the "locking the whole table" prevent here ? If it is the Phantom Read phenomena, then it is not clear to me how Phantom Read could possibly occur in this case if the whole table would not be locked.
– Suren Aznauryan
Nov 26 '18 at 16:37
|
show 2 more comments
By default, InnoDB uses consistent snapshots in Repeatable Read isolation level, meaning that you get repeatable reads for both tuples and ranges.
Even if the SQL standard says that Phantom Reads are prevented by Serializable, and that Repeatable Read might not prevent it, it is not a problem if a lower isolation level provides better guarantees than what the standard has it mind.
For more details about how gap locking works, check out this article.
answered Nov 25 '18 at 15:07
Vlad MihalceaVlad Mihalcea
61.4k14177491
So it is done for preventing Phantom Reads, right ? In my case that kind of locking ensures that after theDELETEstatement execution until the end of my transaction whenever I executeSELECT FROM test WHERE quantity=10;I will always see an empty result set.
– Suren Aznauryan
Nov 25 '18 at 17:08
The rear-your-write behavior does not come from locking, but from the MVCC )Multi-Version Concurrency Control) engine employed by InnoDB.
– Vlad Mihalcea
Nov 25 '18 at 19:59
The articles you gave the links for are completely clear to me. The only thing is not clear is why in case I have described the whole table is locked with clustered index row locks and gap locks everywhere. Why some tx2 cannot insert, delete, update any record in a table after tx1 executesdeletestatement with a condition on non-indexed column(assuming that tx1 is still in progress) ? There should be a reason, right ? What could happen otherwise ? I think I need some examples of some possible phenomena(or some bad stuff in general) happening in case if whole table is not locked in this case
– Suren Aznauryan
Nov 25 '18 at 22:34
You can find a detailed explanation of these phenomena in the High-Performance Java Persistence book with examples as well.
– Vlad Mihalcea
Nov 26 '18 at 4:56
I'm reading that book already :) Sorry, maybe I'm not clear on my question, but still I didn't get the response for it. What I'm asking is simple: what phenomena does the "locking the whole table" prevent here ? If it is the Phantom Read phenomena, then it is not clear to me how Phantom Read could possibly occur in this case if the whole table would not be locked.
– Suren Aznauryan
Nov 26 '18 at 16:37
|
show 2 more comments
By default, InnoDB uses consistent snapshots in Repeatable Read isolation level, meaning that you get repeatable reads for both tuples and ranges.
Even if the SQL standard says that Phantom Reads are prevented by Serializable, and that Repeatable Read might not prevent it, it is not a problem if a lower isolation level provides better guarantees than what the standard has it mind.
For more details about how gap locking works, check out this article.
answered Nov 25 '18 at 15:07
Vlad MihalceaVlad Mihalcea
61.4k14177491
By default, InnoDB uses consistent snapshots in Repeatable Read isolation level, meaning that you get repeatable reads for both tuples and ranges.
Even if the SQL standard says that Phantom Reads are prevented by Serializable, and that Repeatable Read might not prevent it, it is not a problem if a lower isolation level provides better guarantees than what the standard has it mind.
For more details about how gap locking works, check out this article.
answered Nov 25 '18 at 15:07
Vlad MihalceaVlad Mihalcea
61.4k14177491
answered Nov 25 '18 at 15:07
Vlad MihalceaVlad Mihalcea
61.4k14177491
answered Nov 25 '18 at 15:07
Vlad MihalceaVlad Mihalcea
61.4k14177491
answered Nov 25 '18 at 15:07
Vlad MihalceaVlad Mihalcea
61.4k14177491
61.4k14177491
So it is done for preventing Phantom Reads, right ? In my case that kind of locking ensures that after theDELETEstatement execution until the end of my transaction whenever I executeSELECT FROM test WHERE quantity=10;I will always see an empty result set.
– Suren Aznauryan
Nov 25 '18 at 17:08
The rear-your-write behavior does not come from locking, but from the MVCC )Multi-Version Concurrency Control) engine employed by InnoDB.
– Vlad Mihalcea
Nov 25 '18 at 19:59
The articles you gave the links for are completely clear to me. The only thing is not clear is why in case I have described the whole table is locked with clustered index row locks and gap locks everywhere. Why some tx2 cannot insert, delete, update any record in a table after tx1 executesdeletestatement with a condition on non-indexed column(assuming that tx1 is still in progress) ? There should be a reason, right ? What could happen otherwise ? I think I need some examples of some possible phenomena(or some bad stuff in general) happening in case if whole table is not locked in this case
– Suren Aznauryan
Nov 25 '18 at 22:34
You can find a detailed explanation of these phenomena in the High-Performance Java Persistence book with examples as well.
– Vlad Mihalcea
Nov 26 '18 at 4:56
I'm reading that book already :) Sorry, maybe I'm not clear on my question, but still I didn't get the response for it. What I'm asking is simple: what phenomena does the "locking the whole table" prevent here ? If it is the Phantom Read phenomena, then it is not clear to me how Phantom Read could possibly occur in this case if the whole table would not be locked.
– Suren Aznauryan
Nov 26 '18 at 16:37
|
show 2 more comments
So it is done for preventing Phantom Reads, right ? In my case that kind of locking ensures that after theDELETEstatement execution until the end of my transaction whenever I executeSELECT FROM test WHERE quantity=10;I will always see an empty result set.
– Suren Aznauryan
Nov 25 '18 at 17:08
The rear-your-write behavior does not come from locking, but from the MVCC )Multi-Version Concurrency Control) engine employed by InnoDB.
– Vlad Mihalcea
Nov 25 '18 at 19:59
The articles you gave the links for are completely clear to me. The only thing is not clear is why in case I have described the whole table is locked with clustered index row locks and gap locks everywhere. Why some tx2 cannot insert, delete, update any record in a table after tx1 executesdeletestatement with a condition on non-indexed column(assuming that tx1 is still in progress) ? There should be a reason, right ? What could happen otherwise ? I think I need some examples of some possible phenomena(or some bad stuff in general) happening in case if whole table is not locked in this case
– Suren Aznauryan
Nov 25 '18 at 22:34
You can find a detailed explanation of these phenomena in the High-Performance Java Persistence book with examples as well.
– Vlad Mihalcea
Nov 26 '18 at 4:56
I'm reading that book already :) Sorry, maybe I'm not clear on my question, but still I didn't get the response for it. What I'm asking is simple: what phenomena does the "locking the whole table" prevent here ? If it is the Phantom Read phenomena, then it is not clear to me how Phantom Read could possibly occur in this case if the whole table would not be locked.
– Suren Aznauryan
Nov 26 '18 at 16:37
So it is done for preventing Phantom Reads, right ? In my case that kind of locking ensures that after the
DELETE statement execution until the end of my transaction whenever I execute SELECT FROM test WHERE quantity=10; I will always see an empty result set.– Suren Aznauryan
Nov 25 '18 at 17:08
So it is done for preventing Phantom Reads, right ? In my case that kind of locking ensures that after the
DELETE statement execution until the end of my transaction whenever I execute SELECT FROM test WHERE quantity=10; I will always see an empty result set.– Suren Aznauryan
Nov 25 '18 at 17:08
The rear-your-write behavior does not come from locking, but from the MVCC )Multi-Version Concurrency Control) engine employed by InnoDB.
– Vlad Mihalcea
Nov 25 '18 at 19:59
The rear-your-write behavior does not come from locking, but from the MVCC )Multi-Version Concurrency Control) engine employed by InnoDB.
– Vlad Mihalcea
Nov 25 '18 at 19:59
The articles you gave the links for are completely clear to me. The only thing is not clear is why in case I have described the whole table is locked with clustered index row locks and gap locks everywhere. Why some tx2 cannot insert, delete, update any record in a table after tx1 executes
delete statement with a condition on non-indexed column(assuming that tx1 is still in progress) ? There should be a reason, right ? What could happen otherwise ? I think I need some examples of some possible phenomena(or some bad stuff in general) happening in case if whole table is not locked in this case– Suren Aznauryan
Nov 25 '18 at 22:34
The articles you gave the links for are completely clear to me. The only thing is not clear is why in case I have described the whole table is locked with clustered index row locks and gap locks everywhere. Why some tx2 cannot insert, delete, update any record in a table after tx1 executes
delete statement with a condition on non-indexed column(assuming that tx1 is still in progress) ? There should be a reason, right ? What could happen otherwise ? I think I need some examples of some possible phenomena(or some bad stuff in general) happening in case if whole table is not locked in this case– Suren Aznauryan
Nov 25 '18 at 22:34
You can find a detailed explanation of these phenomena in the High-Performance Java Persistence book with examples as well.
– Vlad Mihalcea
Nov 26 '18 at 4:56
You can find a detailed explanation of these phenomena in the High-Performance Java Persistence book with examples as well.
– Vlad Mihalcea
Nov 26 '18 at 4:56
I'm reading that book already :) Sorry, maybe I'm not clear on my question, but still I didn't get the response for it. What I'm asking is simple: what phenomena does the "locking the whole table" prevent here ? If it is the Phantom Read phenomena, then it is not clear to me how Phantom Read could possibly occur in this case if the whole table would not be locked.
– Suren Aznauryan
Nov 26 '18 at 16:37
I'm reading that book already :) Sorry, maybe I'm not clear on my question, but still I didn't get the response for it. What I'm asking is simple: what phenomena does the "locking the whole table" prevent here ? If it is the Phantom Read phenomena, then it is not clear to me how Phantom Read could possibly occur in this case if the whole table would not be locked.
– Suren Aznauryan
Nov 26 '18 at 16:37
|
show 2 more comments
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%2f53452571%2fwhat-phenomena-does-mysql-try-to-prevent-by-locking-the-whole-table-upon-executi%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
Read the manual & some textbook(s) re locking used with MVCC. MVCC uses locking but it doesn't use it the way that non-MVCC DBMSs do. Its locking is not according to the obvious scheme that corresponds to the SQL phenomena prevention regime. Anyway you should be justifying your exectations by referencing the manual, then we can tell you where you went wrong & right; otherwise we have to just rewrite the manual with a bespoke tutorial, which is "too broad".
– philipxy
Nov 23 '18 at 20:32
What I've wrote in my question about how is locking done in this case(record locks on all records in clustered index plus gap locks before every index record and after last index record) i've read in manual where it is written how innodb does locking in Repeatable Read isolation level, but i didn't find why in this particular case locking is done in a way it is done. I'm asking a concrete question about why in this case locking is done that way, what phenomana Innodb tries to prevent ? Can you bring me an example? There should be a reason why is it locking whole table
– Suren Aznauryan
Nov 24 '18 at 16:25