MySQL - lock row for reading
up vote
1
down vote
favorite
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 yesterday
Irfan DANISH
4,77783054
add a comment |
up vote
1
down vote
favorite
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 yesterday
Irfan DANISH
4,77783054
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
yesterday
Possible duplicate of Locking a row in a MYSQL table during a transaction
– t.niese
yesterday
How to properly use transactions and locks to ensure database integrity?
– t.niese
yesterday
@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
yesterday
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
yesterday
add a comment |
up vote
1
down vote
favorite
up vote
1
down vote
favorite
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 yesterday
Irfan DANISH
4,77783054
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 yesterday
Irfan DANISH
4,77783054
asked yesterday
Irfan DANISH
4,77783054
asked yesterday
Irfan DANISH
4,77783054
asked yesterday
Irfan DANISH
4,77783054
asked yesterday
Irfan DANISH
4,77783054
4,77783054
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
yesterday
Possible duplicate of Locking a row in a MYSQL table during a transaction
– t.niese
yesterday
How to properly use transactions and locks to ensure database integrity?
– t.niese
yesterday
@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
yesterday
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
yesterday
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
yesterday
Possible duplicate of Locking a row in a MYSQL table during a transaction
– t.niese
yesterday
How to properly use transactions and locks to ensure database integrity?
– t.niese
yesterday
@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
yesterday
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
yesterday
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
yesterday
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
yesterday
Possible duplicate of Locking a row in a MYSQL table during a transaction
– t.niese
yesterday
Possible duplicate of Locking a row in a MYSQL table during a transaction
– t.niese
yesterday
How to properly use transactions and locks to ensure database integrity?
– t.niese
yesterday
How to properly use transactions and locks to ensure database integrity?
– t.niese
yesterday
@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
yesterday
@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
yesterday
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
yesterday
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
yesterday
add a comment |
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
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
yesterday
Possible duplicate of Locking a row in a MYSQL table during a transaction
– t.niese
yesterday
How to properly use transactions and locks to ensure database integrity?
– t.niese
yesterday
@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
yesterday
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
yesterday