Differences of MySQL locking for update with WHERE and IN
So I have a table with id as the primary column. When I do query 1 (Trans1) in a transaction I get 3 rows locked, and when I do query 2 (Trans2) also in a transaction, I do also get 3 rows locked.
Example Table:
+----+--------------------------------------+-------------------------+
| id | some_val | created_at |
+----+--------------------------------------+-------------------------+
| 1 | 33aa186d-e2de-4c41-aa50-000000000001 | 2019-03-17 15:15:21.130 |
| 2 | 33aa186d-e2de-4c41-aa50-000000000002 | 2019-03-17 15:15:31.292 |
| 3 | 33aa186d-e2de-4c41-aa50-000000000003 | 2019-03-17 15:16:01.396 |
+----+--------------------------------------+-------------------------+
Trans1:
SELECT * FROM example WHERE id >= 1 AND id <= 3 FOR UPDATE;
INNODB Status
---TRANSACTION 10750, ACTIVE 4 sec
3 lock struct(s), heap size 360, 4 row lock(s)
MySQL thread id 23, OS thread handle 0x7f41f5153700, query id 11504 172.19.0.1 root
Trans2:
SELECT * FROM example WHERE id IN (1,2,3) FOR UPDATE;
INNODB Status
---TRANSACTION 10854, ACTIVE 11 sec
2 lock struct(s), heap size 360, 3 row lock(s)
MySQL thread id 23, OS thread handle 0x7f41f5153700, query id 11652 172.19.0.1 root
Now, even though the results seem similar, they are quite different in regarding of what's actually locked. For starter, no inserts are possible while Trans1 is in progress, while inserts are possible during Trans2. Also, if table has only 3 rows, how is INNODB showing 4 locks (I guess is the next auto increment, thus no inserts possible).
I would like to understand why does this happen, as I've read about gap locking and next-key lock, but none makes sense about what's happening on Trans1.
mysql locking
asked 9 mins ago
eduardeveduardev
1011
New contributor
eduardev is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
So I have a table with id as the primary column. When I do query 1 (Trans1) in a transaction I get 3 rows locked, and when I do query 2 (Trans2) also in a transaction, I do also get 3 rows locked.
Example Table:
+----+--------------------------------------+-------------------------+
| id | some_val | created_at |
+----+--------------------------------------+-------------------------+
| 1 | 33aa186d-e2de-4c41-aa50-000000000001 | 2019-03-17 15:15:21.130 |
| 2 | 33aa186d-e2de-4c41-aa50-000000000002 | 2019-03-17 15:15:31.292 |
| 3 | 33aa186d-e2de-4c41-aa50-000000000003 | 2019-03-17 15:16:01.396 |
+----+--------------------------------------+-------------------------+
Trans1:
SELECT * FROM example WHERE id >= 1 AND id <= 3 FOR UPDATE;
INNODB Status
---TRANSACTION 10750, ACTIVE 4 sec
3 lock struct(s), heap size 360, 4 row lock(s)
MySQL thread id 23, OS thread handle 0x7f41f5153700, query id 11504 172.19.0.1 root
Trans2:
SELECT * FROM example WHERE id IN (1,2,3) FOR UPDATE;
INNODB Status
---TRANSACTION 10854, ACTIVE 11 sec
2 lock struct(s), heap size 360, 3 row lock(s)
MySQL thread id 23, OS thread handle 0x7f41f5153700, query id 11652 172.19.0.1 root
Now, even though the results seem similar, they are quite different in regarding of what's actually locked. For starter, no inserts are possible while Trans1 is in progress, while inserts are possible during Trans2. Also, if table has only 3 rows, how is INNODB showing 4 locks (I guess is the next auto increment, thus no inserts possible).
I would like to understand why does this happen, as I've read about gap locking and next-key lock, but none makes sense about what's happening on Trans1.
mysql locking
asked 9 mins ago
eduardeveduardev
1011
New contributor
eduardev is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
So I have a table with id as the primary column. When I do query 1 (Trans1) in a transaction I get 3 rows locked, and when I do query 2 (Trans2) also in a transaction, I do also get 3 rows locked.
Example Table:
+----+--------------------------------------+-------------------------+
| id | some_val | created_at |
+----+--------------------------------------+-------------------------+
| 1 | 33aa186d-e2de-4c41-aa50-000000000001 | 2019-03-17 15:15:21.130 |
| 2 | 33aa186d-e2de-4c41-aa50-000000000002 | 2019-03-17 15:15:31.292 |
| 3 | 33aa186d-e2de-4c41-aa50-000000000003 | 2019-03-17 15:16:01.396 |
+----+--------------------------------------+-------------------------+
Trans1:
SELECT * FROM example WHERE id >= 1 AND id <= 3 FOR UPDATE;
INNODB Status
---TRANSACTION 10750, ACTIVE 4 sec
3 lock struct(s), heap size 360, 4 row lock(s)
MySQL thread id 23, OS thread handle 0x7f41f5153700, query id 11504 172.19.0.1 root
Trans2:
SELECT * FROM example WHERE id IN (1,2,3) FOR UPDATE;
INNODB Status
---TRANSACTION 10854, ACTIVE 11 sec
2 lock struct(s), heap size 360, 3 row lock(s)
MySQL thread id 23, OS thread handle 0x7f41f5153700, query id 11652 172.19.0.1 root
Now, even though the results seem similar, they are quite different in regarding of what's actually locked. For starter, no inserts are possible while Trans1 is in progress, while inserts are possible during Trans2. Also, if table has only 3 rows, how is INNODB showing 4 locks (I guess is the next auto increment, thus no inserts possible).
I would like to understand why does this happen, as I've read about gap locking and next-key lock, but none makes sense about what's happening on Trans1.
mysql locking
asked 9 mins ago
eduardeveduardev
1011
New contributor
eduardev is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
So I have a table with id as the primary column. When I do query 1 (Trans1) in a transaction I get 3 rows locked, and when I do query 2 (Trans2) also in a transaction, I do also get 3 rows locked.
Example Table:
+----+--------------------------------------+-------------------------+
| id | some_val | created_at |
+----+--------------------------------------+-------------------------+
| 1 | 33aa186d-e2de-4c41-aa50-000000000001 | 2019-03-17 15:15:21.130 |
| 2 | 33aa186d-e2de-4c41-aa50-000000000002 | 2019-03-17 15:15:31.292 |
| 3 | 33aa186d-e2de-4c41-aa50-000000000003 | 2019-03-17 15:16:01.396 |
+----+--------------------------------------+-------------------------+
Trans1:
SELECT * FROM example WHERE id >= 1 AND id <= 3 FOR UPDATE;
INNODB Status
---TRANSACTION 10750, ACTIVE 4 sec
3 lock struct(s), heap size 360, 4 row lock(s)
MySQL thread id 23, OS thread handle 0x7f41f5153700, query id 11504 172.19.0.1 root
Trans2:
SELECT * FROM example WHERE id IN (1,2,3) FOR UPDATE;
INNODB Status
---TRANSACTION 10854, ACTIVE 11 sec
2 lock struct(s), heap size 360, 3 row lock(s)
MySQL thread id 23, OS thread handle 0x7f41f5153700, query id 11652 172.19.0.1 root
Now, even though the results seem similar, they are quite different in regarding of what's actually locked. For starter, no inserts are possible while Trans1 is in progress, while inserts are possible during Trans2. Also, if table has only 3 rows, how is INNODB showing 4 locks (I guess is the next auto increment, thus no inserts possible).
I would like to understand why does this happen, as I've read about gap locking and next-key lock, but none makes sense about what's happening on Trans1.
mysql locking
mysql locking
asked 9 mins ago
eduardeveduardev
1011
New contributor
eduardev is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 9 mins ago
eduardeveduardev
1011
New contributor
eduardev is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 9 mins ago
eduardeveduardev
1011
New contributor
eduardev is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 9 mins ago
eduardeveduardev
1011
asked 9 mins ago
eduardeveduardev
1011
1011
New contributor
eduardev is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
eduardev is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
eduardev is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
add a comment |
0
active
oldest
votes
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
});
}
});
eduardev is a new contributor. Be nice, and check out our Code of Conduct.
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%2f232483%2fdifferences-of-mysql-locking-for-update-with-where-and-in%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
eduardev is a new contributor. Be nice, and check out our Code of Conduct.
eduardev is a new contributor. Be nice, and check out our Code of Conduct.
eduardev is a new contributor. Be nice, and check out our Code of Conduct.
eduardev is a new contributor. Be nice, and check out our Code of Conduct.
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%2f232483%2fdifferences-of-mysql-locking-for-update-with-where-and-in%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
