Stored procedure that safely increments a column

[Michael]

I'm quite sure this is the best way, but I just wanted to throw this out there.

I want to increment a number for each user and return the new number, and it also needs to be 100% concurrency safe. It will be hit a LOT, and I want to ensure there's no overlap, or connections/sessions retrieving the same number for a user.

Is there a better solution than this?

CREATE PROCEDURE `get_tick_count`(p_user_id INT)
DETERMINISTIC
BEGIN
START TRANSACTION;

SELECT tick_count INTO @tick_count
FROM user_tick_count
WHERE user_id = p_user_id
FOR UPDATE;

SET @tick_count = @tick_count + 1;

UPDATE user_tick_count
SET tick_count = @tick_count
WHERE user_id = p_user_id;

SELECT @tick_count;

COMMIT;
END

[Axel Schwenke]

Michael <mic...@d3i.com> wrote:

> Is there a better solution than this?

There certainly is.

> CREATE PROCEDURE `get_tick_count`(p_user_id INT)
> DETERMINISTIC

^^^^^^^^^^^^^
This is wrong BTW.

> BEGIN
> START TRANSACTION;
>
> SELECT tick_count INTO @tick_count
> FROM user_tick_count
> WHERE user_id = p_user_id
> FOR UPDATE;
>
> SET @tick_count = @tick_count + 1;
>
> UPDATE user_tick_count
> SET tick_count = @tick_count
> WHERE user_id = p_user_id;
>
> SELECT @tick_count;
>
> COMMIT;
> END

No need to use a procedure. A single statement does the trick:

UPDATE user_tick_count
SET tick_count = (@my_tick_count:=tick_count+1)
WHERE user_id = p_user_id;

This will update the `tick_count` column in the `user_tick_count`
table with an atomic UPDATE and return the new value in the user
variable @my_tick_count.

You can then either SELECT the user variable to get the value into
your application

SELECT @my_tick_count;

or simply use the variable in subsequent SQL statements. This will
work for any engine (though for transactional engines you need an
additional COMMIT). User variables have session scope, so this is
safe for concurrent workload. RTFM on user variables.


XL

[Michael]

That's great, thanks for your help Axel. I've kept it in a stored procedure as I'd like to be able to incr and get the new tick count with just the single call from my app. Also added the ability to start a new count going for a new user!

CREATE PROCEDURE `get_tick_count` (p_user_id INT)

BEGIN
START TRANSACTION;
INSERT INTO user_tick_count (user_id, tick_count)
VALUES (p_user_id, (@tick_count:=1))
ON DUPLICATE KEY UPDATE tick_count = (@tick_count:=tick_count+1);
SELECT @tick_count;
COMMIT;
END

[jhal...@gmail.com]

Now that it is 2018 what would you vote for as a good substitute for RTFM?

For background, it was "Read The Field Manual" however by 2012 it was firmly "READ THE FUCKING MANUAL".

Now that these are softer and gentler times may I suggest we update the rfc to consider uppercase usage of RTFM to be equivalent of "READ THE FUCKING MANUAL" and lowercase usage, rtfm to be equivalent to "Read the Field Manual, Would be my suggestion <pronoun> <person>"

[Tony Mountifield]

In article <aa35e84a-afd2-4d62...@googlegroups.com>,

Well I've just looked in my print copy of "The New Hacker's Dictionary" from 1991, and even then, it gives RTFM
with your 2012 definition. No mention of "Field". And also RTM as a more polite version. UTSL is in there too.

Interestingly, being from 1991, there is of course no mention of "Linux" - times certainly have changed!

Cheers
Tony
--
Tony Mountifield
Work: to...@softins.co.uk - http://www.softins.co.uk
Play: to...@mountifield.org - http://tony.mountifield.org

[The Natural Philosopher]

On 08/06/18 04:30, jhal...@gmail.com wrote:
> Now that it is 2018 what would you vote for as a good substitute for RTFM?
>
> For background, it was "Read The Field Manual" however by 2012 it was firmly "READ THE FUCKING MANUAL".

It was "READ THE FUCKING MANUAL" back in 1985...

--
Any fool can believe in principles - and most of them do!

[vjp...@gmail.com]

How to run this procedure to get an incremented number

[Tony Mountifield]

In article <75641124-9c96-42cb...@googlegroups.com>,

<vjp...@gmail.com> wrote:
> How to run this procedure to get an incremented number

Here is one possibility:

CREATE TABLE seq (num BIGINT UNSIGNED);

INSERT INTO seq VALUES(0);

Then for each time you need a new sequence number:

UPDATE seq SET num=(@n := num+1);

And use @n in the SQL statement that wants the sequence number.
(SELECT @n; will display it)

[Axel Schwenke]

On 23.07.2019 22:39, vjp...@gmail.com wrote:
> How to run this procedure to get an incremented number

What do you mean *this* procedure? You didn't show a procedure.

MySQL has the AUTO_INCREMENT feature to generate a unique running number.
MariaDB additionally has native SEQUENCE objects:

https://mariadb.com/kb/en/library/sequence-overview/

Normally there is no need to encapsulate this into a stored procedure.