Atomic counter with MySql

Question

With MySql 8.0, I'm handling an "atomic counter" (in a stored procedure), and this simple workaround (I cannot use transactions) works nicely for my purpose :

CREATE PROCEDURE xxx...
   ...
   UPDATE cnt SET value = (@val := value + 1) where id = 1;
   ...

but when I'm compiling the procedure, I get this warning

Setting user variables within expressions is deprecated and will be removed in a future release. ...

How can I avoid the warning message ? (I cannot find out the "new" syntax)

I have no idea what you are trying to do can you provide examples? — P.Salmon, May 16 '19 at 10:19
Well, it's in the name : removing the warning message when I'm doing an "atomic counter" or atomic increment (if you want) — Stef, May 16 '19 at 10:40

score 1 · Accepted Answer · answered May 16 '19 at 17:15

A simulated atomic counter without the need to set variables within expressions can be achieved with

repeat 
  select value + 1 into @value from cnt where id = 1;
  update cnt set value = @value where id = 1 and value = @value - 1;
until (select row_count()) > 0 end repeat;

This is not actually atomic (as a different session can increase the counter between select and update), but it will only update if that didn't happen, otherwise it will retry (potentially indefinitely if you have a really really busy counter). row_count() is used to check if that update happened.

There is no "new syntax" for that deprecated feature - it will, intentionally, not be possible anymore to do this in MySQL 9 (thus the warning), see the change log. The main use case for setting user variables within expressions was simulating CTEs (for example a recursive hierarchical query or the rank() window-function), and with the support of CTEs in MySQL 8, it was possible to deprecate this functionality.

For context, the intended behaviour of your syntax is to make

UPDATE cnt SET value = value + 1 where id = 1; 
SELECT value INTO @val from cnt where id = 1;

behave atomically.

Obviously, the expected way to achieve this is to use a transaction, so there won't be a new syntax to replace your behaviour, like it was required for CTEs; you may however want to check if the reason you cannot use a transaction might be gone in a newer MySQL version (with potentially new capabilities).

Just to clarify: the loop is acting like an atomic counter and is working without the deprecated syntax, and *without transactions*. (The whole 4 lines replacing your single line). The rest just comments on the *I cannot find out the "new" syntax"*: the new syntax for cte simulation -> ctes. the new syntax for transaction simulation: transactions. (And so there is no new syntax planned by the developers to replace your current oneliner with a new oneliner). — Solarflare, May 18 '19 at 06:25
What if _two_ sessions update the value in the moment between your select and your update? Then your code above will revert the incrementation. — Bill Karwin, May 18 '19 at 21:26
@BillKarwin If a different session updated the counter in between, then it will no longer have `value = @value - 1` (for the `where`-condition), so it will not update the row (and `row_count()` will be 0, doing another iteration). — Solarflare, May 19 '19 at 07:12

Bill Karwin · Answer 2 · 2019-05-18T21:24:10.830

0

The warning is about setting session variables as a side-effect of an expression in your UPDATE statement. You can avoid this by moving the variable assignment into a subsequent SELECT statement.

   START TRANSACTION;
   UPDATE cnt SET value = value + 1 where id = 1;
   SELECT value INTO @val FROM cnt WHERE id =1;
   COMMIT;

This won't have a risk of a race condition if you start a transaction before the UPDATE and do the SELECT in the same transaction. The lock acquired by the UPDATE will prevent another concurrent session from updating the same row before you can SELECT the value.

edited May 18 '19 at 21:24

answered May 16 '19 at 17:21

Bill Karwin

538,548
86
673
828

Why can't you use transactions? That's a really fundamental thing to throw away when using a relational database. – Bill Karwin May 17 '19 at 15:19
I agree, but in my case, locks are decreasing the overall performance (among other things) – Stef May 18 '19 at 07:30
Ok you have some misunderstandings I think. MySQL always locks rows for updates, whether you explicitly begin and commit transactions, or just rely on autocommit. In fact, autocommit still uses transactions, they're just committed promptly as soon as the SQL update finishes. But they're still subject to locking. You don't have to keep transactions open for a long time, in fact it's generally best to commit as promptly as possible. But doing two SQL statements back-to-back like I show above shouldn't be too long. Just commit right after that. – Bill Karwin May 18 '19 at 17:41
Also you can have a mix — i.e. default to autocommit in the majority of your app, and just use explicit start and commit when you need to encapsulate the work for a sequence of statements, like the example in my answer. – Bill Karwin May 18 '19 at 17:42
Ok Bill, I know what is a transaction ;) But it is not an option for me. In fact the solution given by Solarflare is quite good. Thanks for you help. – Stef May 18 '19 at 21:08

Atomic counter with MySql

2 Answers2