MySQL - lock row for reading
1
I am using socket.io server and multiple clients are making requests to this server. Server checks a MySQL innoDB table for data and if there is some data it updates its bit/column in table and send data to requested client.
But the problem is my multiple clients are occasionally getting same data.
Reason I feel is, 2 clients make requests to server exactly same point in time and both get same MySQL table row record before it was updated by other client.
mysql socket.io innodb
asked Nov 19 '18 at 10:04
Irfan DANISHIrfan DANISH
4,88283155
add a comment |
1
I am using socket.io server and multiple clients are making requests to this server. Server checks a MySQL innoDB table for data and if there is some data it updates its bit/column in table and send data to requested client.
But the problem is my multiple clients are occasionally getting same data.
Reason I feel is, 2 clients make requests to server exactly same point in time and both get same MySQL table row record before it was updated by other client.
mysql socket.io innodb
asked Nov 19 '18 at 10:04
Irfan DANISHIrfan DANISH
4,88283155
If you use the received data in follow-up select, insert or update then you have to use FOR UPDATE or FOR SHARE within the transaction.
– t.niese
Nov 19 '18 at 10:06
Possible duplicate of Locking a row in a MYSQL table during a transaction
– t.niese
Nov 19 '18 at 10:10
How to properly use transactions and locks to ensure database integrity?
– t.niese
Nov 19 '18 at 10:10
@t.niese - the link you shared is something different. Those locking doesn't lock a row for reading. I want to lock read, means the row my one connection is processing should not be read by other connection.
– Irfan DANISH
Nov 19 '18 at 10:43
If you useFOR UPDATEthenOther transactions are blocked from updating those rows, from doing SELECT ... FOR SHARE, or from reading the data in certain transaction isolation levels.. So if your isolation level is set up correctly then you can't have two transactions having aSELECT .. FOR UPDATEon the same row. So either the second transaction fails, or waits until the firstFOR UPDATEfinished.
– t.niese
Nov 19 '18 at 12:25
add a comment |
1
1
1
I am using socket.io server and multiple clients are making requests to this server. Server checks a MySQL innoDB table for data and if there is some data it updates its bit/column in table and send data to requested client.
But the problem is my multiple clients are occasionally getting same data.
Reason I feel is, 2 clients make requests to server exactly same point in time and both get same MySQL table row record before it was updated by other client.
mysql socket.io innodb
asked Nov 19 '18 at 10:04
Irfan DANISHIrfan DANISH
4,88283155
I am using socket.io server and multiple clients are making requests to this server. Server checks a MySQL innoDB table for data and if there is some data it updates its bit/column in table and send data to requested client.
But the problem is my multiple clients are occasionally getting same data.
Reason I feel is, 2 clients make requests to server exactly same point in time and both get same MySQL table row record before it was updated by other client.
mysql socket.io innodb
mysql socket.io innodb
asked Nov 19 '18 at 10:04
Irfan DANISHIrfan DANISH
4,88283155
asked Nov 19 '18 at 10:04
Irfan DANISHIrfan DANISH
4,88283155
asked Nov 19 '18 at 10:04
Irfan DANISHIrfan DANISH
4,88283155
asked Nov 19 '18 at 10:04
Irfan DANISHIrfan DANISH
4,88283155
asked Nov 19 '18 at 10:04
Irfan DANISHIrfan DANISH
4,88283155
4,88283155
If you use the received data in follow-up select, insert or update then you have to use FOR UPDATE or FOR SHARE within the transaction.
– t.niese
Nov 19 '18 at 10:06
Possible duplicate of Locking a row in a MYSQL table during a transaction
– t.niese
Nov 19 '18 at 10:10
How to properly use transactions and locks to ensure database integrity?
– t.niese
Nov 19 '18 at 10:10
@t.niese - the link you shared is something different. Those locking doesn't lock a row for reading. I want to lock read, means the row my one connection is processing should not be read by other connection.
– Irfan DANISH
Nov 19 '18 at 10:43
If you useFOR UPDATEthenOther transactions are blocked from updating those rows, from doing SELECT ... FOR SHARE, or from reading the data in certain transaction isolation levels.. So if your isolation level is set up correctly then you can't have two transactions having aSELECT .. FOR UPDATEon the same row. So either the second transaction fails, or waits until the firstFOR UPDATEfinished.
– t.niese
Nov 19 '18 at 12:25
add a comment |
If you use the received data in follow-up select, insert or update then you have to use FOR UPDATE or FOR SHARE within the transaction.
– t.niese
Nov 19 '18 at 10:06
Possible duplicate of Locking a row in a MYSQL table during a transaction
– t.niese
Nov 19 '18 at 10:10
How to properly use transactions and locks to ensure database integrity?
– t.niese
Nov 19 '18 at 10:10
@t.niese - the link you shared is something different. Those locking doesn't lock a row for reading. I want to lock read, means the row my one connection is processing should not be read by other connection.
– Irfan DANISH
Nov 19 '18 at 10:43
If you useFOR UPDATEthenOther transactions are blocked from updating those rows, from doing SELECT ... FOR SHARE, or from reading the data in certain transaction isolation levels.. So if your isolation level is set up correctly then you can't have two transactions having aSELECT .. FOR UPDATEon the same row. So either the second transaction fails, or waits until the firstFOR UPDATEfinished.
– t.niese
Nov 19 '18 at 12:25
If you use the received data in follow-up select, insert or update then you have to use FOR UPDATE or FOR SHARE within the transaction.
– t.niese
Nov 19 '18 at 10:06
If you use the received data in follow-up select, insert or update then you have to use FOR UPDATE or FOR SHARE within the transaction.
– t.niese
Nov 19 '18 at 10:06
Possible duplicate of Locking a row in a MYSQL table during a transaction
– t.niese
Nov 19 '18 at 10:10
Possible duplicate of Locking a row in a MYSQL table during a transaction
– t.niese
Nov 19 '18 at 10:10
How to properly use transactions and locks to ensure database integrity?
– t.niese
Nov 19 '18 at 10:10
How to properly use transactions and locks to ensure database integrity?
– t.niese
Nov 19 '18 at 10:10
@t.niese - the link you shared is something different. Those locking doesn't lock a row for reading. I want to lock read, means the row my one connection is processing should not be read by other connection.
– Irfan DANISH
Nov 19 '18 at 10:43
@t.niese - the link you shared is something different. Those locking doesn't lock a row for reading. I want to lock read, means the row my one connection is processing should not be read by other connection.
– Irfan DANISH
Nov 19 '18 at 10:43
If you use
FOR UPDATE then Other transactions are blocked from updating those rows, from doing SELECT ... FOR SHARE, or from reading the data in certain transaction isolation levels.. So if your isolation level is set up correctly then you can't have two transactions having a SELECT .. FOR UPDATE on the same row. So either the second transaction fails, or waits until the first FOR UPDATE finished.– t.niese
Nov 19 '18 at 12:25
If you use
FOR UPDATE then Other transactions are blocked from updating those rows, from doing SELECT ... FOR SHARE, or from reading the data in certain transaction isolation levels.. So if your isolation level is set up correctly then you can't have two transactions having a SELECT .. FOR UPDATE on the same row. So either the second transaction fails, or waits until the first FOR UPDATE finished.– t.niese
Nov 19 '18 at 12:25
add a comment |
0
active
oldest
votes
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
});
}
});
draft saved
draft discarded
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%2f53372245%2fmysql-lock-row-for-reading%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
draft saved
draft discarded
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.
draft saved
draft discarded
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%2f53372245%2fmysql-lock-row-for-reading%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
If you use the received data in follow-up select, insert or update then you have to use FOR UPDATE or FOR SHARE within the transaction.
– t.niese
Nov 19 '18 at 10:06
Possible duplicate of Locking a row in a MYSQL table during a transaction
– t.niese
Nov 19 '18 at 10:10
How to properly use transactions and locks to ensure database integrity?
– t.niese
Nov 19 '18 at 10:10
@t.niese - the link you shared is something different. Those locking doesn't lock a row for reading. I want to lock read, means the row my one connection is processing should not be read by other connection.
– Irfan DANISH
Nov 19 '18 at 10:43
If you use
FOR UPDATEthenOther transactions are blocked from updating those rows, from doing SELECT ... FOR SHARE, or from reading the data in certain transaction isolation levels.. So if your isolation level is set up correctly then you can't have two transactions having aSELECT .. FOR UPDATEon the same row. So either the second transaction fails, or waits until the firstFOR UPDATEfinished.– t.niese
Nov 19 '18 at 12:25