Is this atomic set and get in MariaDB / MySQL: `UPDATE t SET col = @my_var := col + 1 WHERE id = 123`?

Question

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

Answer 1

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.