0

How do I achieve the situation where the rows read by one transaction are not allowed to be read by another transaction?

From one of my tables with columnA-F, I need to select the max(columnA). With the max value and based on some calculation, I will have to perform other insert statements.

With serializable isolation level, 2 transactions are reading the same max value. This is resulting in a lock wait for one transaction and deadlock on the other transaction.

wohlstad
  • 12,661
  • 10
  • 26
  • 39
Al Niyas
  • 1
  • 1
  • check out the solution in [this link][1] [1]: http://stackoverflow.com/questions/2051225/how-to-deliberately-lock-a-mysql-row-such-that-even-select-will-return-an-error – Niraj Burde Jul 31 '12 at 19:25

1 Answers1

0

You might not be able to solve this purely based on isolation level.

A design change you could make is to store the max value separately and have a tri-stage flag along with the record saying - max value processed, processing and not processed. So whenever a transaction reads the max value from the table it could check to see if the max value is already being processed. If so it does not perform the other insert statements.

eightyeight
  • 498
  • 3
  • 6