update a random row and get the row result back in MySQL

I have this table

CREATE TABLE `pcodes` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `product_id` int(10) unsigned NOT NULL,
  `code` varchar(100) NOT NULL,
  `used` int(10) unsigned NOT NULL DEFAULT '0',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
)

and an insert command is the following:

INSERT INTO `pcodes` (`product_id`, `code`) VALUES ('1', 'test2');

The table contains random codes for each product_id. I want to get one unused code randomly (LIMIT 1 is ok for the job), mark the code as used and return it to the next layer.

So far I did this:

SELECT * FROM pcodes where product_id=1 and used=0 LIMIT 1
UPDATE pcodes SET used= 1 WHERE (id = 2);

but this does not work well when multiple threads request the first unused code. What is the optimal solution to do this query? I would like to avoid stored procedures.

Answer

Possible solution.

Assumes that there aren’t other predefined values stored in used column except 0 and 1.

CREATE PROCEDURE select_one_random_row (OUT rowid BIGINT)
BEGIN
    REPEAT
        UPDATE pcodes SET used = CONNECTION_ID() WHERE used = 0 LIMIT 1;
        SELECT id INTO rowid FROM pcodes WHERE used = CONNECTION_ID();
    UNTIL rowid IS NOT NULL END REPEAT;
    UPDATE pcodes SET used = 1 WHERE used = CONNECTION_ID();
END

To prevent indefinite loop (for example when no rows with used=0) add some counter which increments in REPEAT cycle and breaks it after some reasonable iteration attempts.

The code may be converted to FUNCTION which returns selected rowid.

It is possible that the procedure/function fails (by some external reasons), and a row will stay “selected by current CONNECTION_ID()” whereas the connection is broken itself. So you need in service procedure executed by Event Scheduler which will garbage the rows which belongs to non-existed connections and clear their used value back to zero returning such rows to unused pool.