1

mysql 5.7, mybatis 3.4

i have a table tableA whose id is the pk, outstanding is decimal and status is int.

in my java code, there are following steps(they are in one transaction):

  1. select * from tableA for update
  2. update tableA set outstanding = outstanding - #{amount}, status=IF(outstanding = 0, 2, 3) where id=#{id}

what i want is, e.g. : if outstanding is 10, and the amount is 10, then after run the code above, the outstanding should be 0, and status should be 2(represent full settled); if amount is 5,then after run the code above, the outstanding should be 5, and status should be 3(partial settle).

However, i encounter one time that outstanding = 0, but status = 3, this is incorrect.(in most cases, it works, outstanding = 0, status = 2).

so is there potential problem for update tableA set outstanding = outstanding - #{amount}, status=IF(outstanding = 0, 2, 3) where id=#{id}? if yes, why?

frank
  • 1,169
  • 18
  • 43
  • You should know that MySQL behaves [differently](https://stackoverflow.com/q/2203202/1261766) from the other DBs (or the SQL standard). MySQL doc [states](https://dev.mysql.com/doc/refman/8.0/en/update.html) that "Single-table UPDATE assignments are generally evaluated from left to right. For multiple-table updates, there is no guarantee that assignments are carried out in any particular order". The word "generally" seems to imply there is an exception even for a single-table UPDATE. – ave Feb 13 '22 at 08:59
  • @ave Thanks for your hint. For my problem, after checking my code, i thought the most likely reason is this `single table update`, but cannot find the evidence to prove there is an exception even for a single-table update. – frank Feb 14 '22 at 02:29
  • As you select the values with `for update` first (I assume the actual SQL includes `WHERE id = #{id}`), it would be better to calculate the values of `outstanding` and `status` in Java code. This way, the SQL is dead simple `... set outstanding=#{outstanding}, status=#{status}` and there is no ambiguity. – ave Feb 14 '22 at 04:33
  • @ave yes, I changed it to `set outstanding=#{outstanding},status=#{status}`. Thanks! – frank Feb 18 '22 at 01:21

0 Answers0