Re: SELECT and UPDATE in one query

[Rick James]

The table must be InnoDB, not MyISAM for BEGIN/COMMIT to have any effect.

Do use FOR UPDATE.

There are several alternative ways...

# 1. Capturing RUN_ID:
START TRANSACTION;
  SELECT @run_id := RUN_ID FROM ParametersTable
      WHERE RUN_STATUS=$WAITING_TO_RUN
      ORDER BY RUN_ID
      LIMIT 1
      FOR UPDATE;
  UPDATE ParametersTable
      SET RUN_STATUS=$RUNNING
      WHERE RUN_ID = @run_id;  # This needs to be UNIQUE or PRIMARY KEY
COMMIT;

# 2. UPDATE, then discover what you grabbed:
# No transaction needed:
# MyISAM should work:
  UPDATE ParametersTable
      SET RUN_STATUS=$RUNNING,
          process_id = $$       # extra field (see SELECT)
      WHERE RUN_STATUS=$WAITING_TO_RUN
      ORDER BY RUN_ID
      LIMIT 1;
  SELECT RUN_ID FROM ParametersTable
      WHERE process_id = $$
        AND RUN_STATUS=$RUNNING;

# 3. Put the code in a Perl script instead of bash.

And possibly other techniques.