CODEWITHSUNDEEP
mysqltransactions
user140053
user140053

Reputation:

Atomic counter with MySql

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)

Upvotes: 0

Views: 1426

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562428

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.

Upvotes: 0

Solarflare
Solarflare

Reputation: 11106

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).

Upvotes: 1

Related Questions