1

We have a problem where my code(war file) running in two different serers will try to read same data from single Mysql DB. I want to lock ROWS in a table when one thread reads them. The other should wait until the first thread releases lock on that row. Can I lock ONLY ROWS in MYSQL DB, without locking the entire table. Is there a way to enable ROW level locking in MYSQL. Also, please suggest if we can achieve this behavour in any other way. We are trying to achieve high-availability in our application, where if one server goes down, the other will still read data from DB for assigning jobs. Also, please suggest if we can achieve it without using locking mechanism itself.

Note: MYSQL8 is providing row level locks, but it is not possible for us to upgrade.

harish bollina
  • 43
  • 8
  • Innodb does offer row level locks as a built-in functionality. What's wrong with that? – Shadow Jun 18 '18 at 10:47
  • If i am correct, that is there from Mysql8 . I was using 5.7 and in my observation, it is locking entire table, I was not able to perform update or insert when I run a query with select..for update. – harish bollina Jun 18 '18 at 10:51
  • The duplicate topic is from 2010 and it suggested using innodb... If your query locks the entire table under innodb, then either your query or your indexes are incorrectly defined. – Shadow Jun 18 '18 at 10:56
  • The column which is in where condition of my select..for update query is part of index. Will that create problem. Also, please suggest a blog where I can find most helpful details on the same. – harish bollina Jun 18 '18 at 10:58
  • I cannot possibly tell why an arbitrary query locks the entire table in innodb. The best place to start is MySQL's documentation on innodb locks: https://dev.mysql.com/doc/refman/5.7/en/innodb-locking-transaction-model.html – Shadow Jun 18 '18 at 12:48

0 Answers0