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.

Irfan DANISH
  • 8,349
  • 12
  • 42
  • 67
  • If you use the received data in follow-up select, insert or update then you have to use [FOR UPDATE or FOR SHARE](https://dev.mysql.com/doc/refman/8.0/en/innodb-locking-reads.html) within the transaction. – t.niese Nov 19 '18 at 10:06
  • Possible duplicate of [Locking a row in a MYSQL table during a transaction](https://stackoverflow.com/questions/49149355/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?](https://stackoverflow.com/questions/40749730/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 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

0 Answers0