MySQL UPDATE and SELECT in one pass

Question

I have a MySQL table of tasks to perform, each row having parameters for a single task.
There are many worker apps (possibly on different machines), performing tasks in a loop.
The apps access the database using MySQL's native C APIs.

In order to own a task, an app does something like that:

• Generate a globally-unique id (for simplicity, let's say it is a number)

• UPDATE tasks
SET guid = %d
WHERE guid = 0 LIMIT 1

• SELECT params
FROM tasks
WHERE guid = %d

• If the last query returns a row, we own it and have the parameters to run

Is there a way to achieve the same effect (i.e. 'own' a row and get its parameters) in a single call to the server?

Answer 1

I don't know about the single call part, but what you're describing is a lock. Locks are an essential element of relational databases.

I don't know the specifics of locking a row, reading it, and then updating it in MySQL, but with a bit of reading of the mysql lock documentation you could do all kinds of lock-based manipulations.

The postgres documenation of locks has a great example describing exactly what you want to do: lock the table, read the table, modify the table.

Answer 2

You may create a procedure that does it:

CREATE PROCEDURE prc_get_task (in_guid BINARY(16), OUT out_params VARCHAR(200))
BEGIN

  DECLARE task_id INT;

  SELECT id, out_params
  INTO task_id, out_params
  FROM tasks
  WHERE guid = 0
  LIMIT 1
  FOR UPDATE;

  UPDATE task
  SET guid = in_guid
  WHERE id = task_id;

END;

BEGIN TRANSACTION;

CALL prc_get_task(@guid, @params);

COMMIT;

Answer 3

If you are looking for a single query then it can't happen. The UPDATE function specifically returns just the number of items that were updated. Similarly, the SELECT function doesn't alter a table, only return values.

Using a procedure will indeed turn it into a single function and it can be handy if locking is a concern for you. If your biggest concern is network traffic (ie: passing too many queries) then use the procedure. If you concern is server overload (ie: the DB is working too hard) then the extra overhead of a procedure could make things worse.