2

I have two tables t1 and t2 with primary key id and integer amount each. I need to "move" amounts from t2 to t1, but not lost any amounts and not get extra. Is it possible by single update with autocommit and without transaction isolations?

I mean something like this:

update t1 join t2 using (id)
    set t1.amount = t1.amount + t2.amount, t2.amount = 0;

Is it guaranteed that total amount will not be changed by this statement? Or it's possible (if change t2.amount during this execution or interrupt it) that t1.amount increase by not the same value that t2.amount decrease?

Pavel G
  • 21
  • 4
  • https://dev.mysql.com/doc/refman/8.0/en/ansi-diff-update.html – Lukasz Szozda May 25 '19 at 17:25
  • 1
    What's the problem with transactions? This is what those were designed for. – Shadow May 25 '19 at 17:31
  • 1
    @LukaszSzozda That link is for single table update. I've read somewhere that the evaluation order of columns in SET clause is "undetermined" for multi-table updates - But can't find the source. – Paul Spiegel May 25 '19 at 17:46
  • [here it is](https://dev.mysql.com/doc/refman/8.0/en/update.html): "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. " – Paul Spiegel May 25 '19 at 17:49
  • @Shadow I'm afraid about exception in code within transaction that cause continue using the same dbi connection by another code of the process without commit or rollback the interrupted transaction. So I create a new dbi connection for each transaction, and it's slower than autocommit statements with existing connection. I know it's not the best solution but it's out of scope here. – Pavel G May 26 '19 at 06:54
  • @PavelG then it would be better if you learnt how to handle exceptions in your application code, rather than trying to do hacks in the database layer. – Shadow May 26 '19 at 17:34

1 Answers1

1

Though it seems to work as expected, it's documented as "unreliable":

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.

(MySQL 8.0 Reference Manual / ... / UPDATE Syntax )

But you can calculate the sum in a derived table:

update t1
join t2 using (id)
join (
  select id, t1.amount + t2.amount as amount
  from t1 join t2 using(id)
) x using (id)
set t1.amount = x.amount,
    t2.amount = 0

In this case the order in the SET clause doesn't matter since the assignments don't interfere.

For InnoDB a single UPDATE statement is atomic. It's either executed completly or not at all. If something goes wrong during the execution (eg. server crash or data type overflow) all changes will be rolled back or just not commited.

Paul Spiegel
  • 30,925
  • 5
  • 44
  • 53
  • Thank you for your reply. I created a test with these tables, 1000 records in each and 2 simultaneous processes, one increasing amounts in t2 and second move amounts from t2 to t1 in loop, 1000 iterations each. I got correct result with "simple" update (but it's not guaranteed by mysql) and error `Deadlock found when trying to get lock` with temp table x. Tested on mariadb 10.1. – Pavel G May 26 '19 at 06:29