0

I want to atomically update a single row and get its value.

I discovered today that MariaDB / MySQL has this additional way of setting user variables using the assignment operator :=, described e.g. here.

I wonder therefore if the following statement, followed by checking the value of @my_var, accomplishes what I want:

UPDATE t SET col = @my_var := col + 1 WHERE id = 123;

It seems it should, but I'd like to confirm with the pundits.


(I know there's also SELECT .. FOR UPDATE.)

5
  • I don't mean to be disrespectful, but can't you test this? I think transactions might be your friend in this case? – Vérace May 27 at 10:51
  • @Vérace - Thank you for the suggestion - not disrespectful at all. For one, writing code testing concurrency reliably is a non-trivial task, and certainly much longer than asking SO experts, who probably know off-hand. For two, I prefer to leave a trace of this online so that the next person with this issue doesn't have to test it too. Again, asking on SO as opposed to testing it myself, after which I may or likely may not have the time to post the results somewhere, seems better. – Jan Żankowski May 27 at 11:10
  • Ah, yes, a record of what happened... That's what this site aims to be. Well, if you throw together a [fiddle](dbfiddle.uk) and can't solve it yourself, ping me back and I'll take a look (no guarantees! :-) ) - and if you can, answer your own question... – Vérace May 27 at 11:24
  • a user defined variable is a ssessipon variable, so evbery update would overwrite this variable. and the last one who set it, is the value you get – nbk May 27 at 11:38
  • Show us what else is involved. @variables may be disallowed in this context in a subsequent version. And there may be a better way to do the task at hand. See also IODKU and LAST_INSERT_ID(). – Rick James May 28 at 1:44
0

I confirm that yes, this is atomic.


This is what I wanted to avoid, but in the end, I wrote a Python script testing this. I'm pasting it below for reference. The prerequisite is a schema my_schema with the following table:

CREATE TABLE `t` (
 `id` int(10) NOT NULL AUTO_INCREMENT,
 `col` int(10) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB;

INSERT INTO `t` (id, col) VALUES (1, -1);

Python script below. Note that you need to comment / uncomment one of the two 'SELECT' lines as needed.

import mysql.connector
import random
import string
import threading
import time

# How many threads to run.
num_worker_threads = 30
# Flag to signal to threads that they should finish.
should_threads_finish = False
values = []

def main():
    global should_threads_finish    

    # Start threads.
    print('Starting threads.')
    threads = []
    for i in range(num_worker_threads):
        t = threading.Thread(target=worker, kwargs={'i':i})
        t.start()
        threads.append(t)

    # Let threads run for a while.
    time.sleep(8)

    # Stop threads.
    print('Stopping threads.')
    should_threads_finish = True
    for t in threads:
        t.join()

    # Verify that values queried from DB form a sequence increasing by 1.
    values_sorted = sorted(values)
    for i in range(len(values_sorted)):
        if i != values_sorted[i]:
            print(values_sorted)
            raise RuntimeError('Set and get was not atomic: ' + str(i) + ' vs ' + str(values_sorted[i]))

# What each thread should do.
def worker(i):
    print('Worker ' + str(i) + ' starting.')
    while True:
        if should_threads_finish:
            print('Worker ' + str(i) + ' finishing.')
            break
        # Do work.
        cnx = mysql.connector.connect(host='localhost', database='mysql', user='root', password='root')
        cur = cnx.cursor()

        # Get random name for user variable.
        var_name = ''.join(random.choice(string.ascii_uppercase + string.digits) for _ in range(10))

        # Do the UPDATE.
        cur.execute("UPDATE my_schema.t SET col = @%s := col + 1 WHERE id = 1", (var_name,))
        cnx.commit()

        # Get either the user variable value or actually select from the table.
        cur.execute("SELECT @%s", (var_name,))
        # cur.execute("SELECT col FROM my_schema.t WHERE id = 1")
        row = cur.fetchone()
        values.append(int(row[0]))

        cnx.close()

if __name__ == "__main__":
    main()

As expected, when selecting the user variable, I'm getting no exception, meaning that the sequence of values queried increased exactly by 1. For my use case, this is a good enough check that this is atomic.

Also as expected, when selecting the value directly from the table (not using the user variable), I'm getting exceptions, signifying non atomicity.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.