22

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?

linuxbuild
  • 15,843
  • 6
  • 60
  • 87
Paul Oyster
  • 1,228
  • 6
  • 16
  • 19

6 Answers6

10

try like this

UPDATE `lastid` SET `idnum` =  (SELECT `id` FROM `history` ORDER BY `id` DESC LIMIT 1);

above code worked for me

charles
  • 307
  • 5
  • 5
7

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;
Quassnoi
  • 413,100
  • 91
  • 616
  • 614
  • 1
    Tried something similar. Bottom line: native SQL queries are much much faster than stored procedures. – Paul Oyster Feb 18 '09 at 20:49
  • 3
    In MySQL there is not so much difference as, say, in Oracle. You can't do it in a single query anyway, but the procedure is one call to the server, just as you asked. With a little effort you can event put BEGIN and COMMIT into the procedure, but it's better be done from application, just in case. – Quassnoi Feb 18 '09 at 20:55
  • So basically you are saying that it is not doable in a single call. I was hoping some form of INSERT INTO _tmp SELECT .. would be king enough to allow a mysql_store_results() call as it does allows mysql_affected_rows() – Paul Oyster Feb 18 '09 at 21:06
  • 1
    Calling a procedure IS a single call to the server, if that's what you mean. Calling mysql_store_result() after an INSERT has been long waited for but it's still unimplemented for now, AFAIK. – Quassnoi Feb 18 '09 at 21:14
1

I have the exact same issue. We ended up using PostreSQL instead, and UPDATE ... RETURNING:

The optional RETURNING clause causes UPDATE to compute and return value(s) based on each row actually updated. Any expression using the table's columns, and/or columns of other tables mentioned in FROM, can be computed. The new (post-update) values of the table's columns are used. The syntax of the RETURNING list is identical to that of the output list of SELECT.

Example: UPDATE 'my_table' SET 'status' = 1 WHERE 'status' = 0 LIMIT 1 RETURNING *;

Or, in your case: UPDATE 'tasks' SET 'guid' = %d WHERE 'guid' = 0 LIMIT 1 RETURNING 'params';

Sorry, I know this doesn't answer the question with MySQL, and it might not be easy to just switch to PostgreSQL, but it's the best way we've found to do it. Even 6 years later, MySQL still doesn't support UPDATE ... RETURNING. It might be added at some point in the future, but for now MariaDB only has it for DELETE statements.

Edit: There is a task (low priority) to add UPDATE ... RETURNING support to MariaDB.

Marco Roy
  • 4,004
  • 7
  • 34
  • 50
1

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.

Paulo
  • 4,275
  • 2
  • 20
  • 20
  • The concern is indeed the server overload. My hope was that since the update already 'seek()ed' to the row, a SELECT would be easier for the server within the same statement. – Paul Oyster Feb 18 '09 at 21:11
  • 2
    I am also looking for a single SELECT/UPDATE and found this question. I don't want it for locking though, but to modify the LastAccessed column in the row to NOW() when the row is retrieved – CashCow Feb 28 '11 at 16:00
  • 1
    Hi Paulo, can you explain (or give a link) why procedures would make things slower for DB? I thought that SP are quicker :S – confiq May 08 '13 at 08:21
0
UPDATE tasks
SET guid = %d, params = @params := params
WHERE guid = 0 LIMIT 1;

It will return 1 or 0, depending on whether the values were effectively changed.

SELECT @params AS params;

This one just selects the variable from the connection.

From: here

jogaco
  • 712
  • 8
  • 25
0

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.

Daniel
  • 154
  • 1
  • 6