Re: Stored procedure that safely increments a column

From: <jhallbox_at_gmail.com>
Date: Thu, 7 Jun 2018 20:30:39 -0700 (PDT)
Message-ID: <aa35e84a-afd2-4d62-bc1e-a709bb0fb1bf_at_googlegroups.com>


[Quoted] On Tuesday, June 4, 2013 at 2:42:35 PM UTC-7, Axel Schwenke wrote:
> Michael <michael_at_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 _at_tick_count
> > FROM user_tick_count
> > WHERE user_id = p_user_id
> > FOR UPDATE;
> >
> > SET _at_tick_count = @tick_count + 1;
> >
> > UPDATE user_tick_count
> > SET tick_count = _at_tick_count
> > WHERE user_id = p_user_id;
> >
> > SELECT _at_tick_count;
> >
> > COMMIT;
> > END
>
> No need to use a procedure. A single statement does the trick:
>
> UPDATE user_tick_count
> SET tick_count = (_at_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 _at_my_tick_count.
>
> You can then either SELECT the user variable to get the value into
> your application
>
> SELECT _at_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

[Quoted] [Quoted] 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>" Received on Fri Jun 08 2018 - 05:30:39 CEST

Original text of this message