Ensure that only one record has a specific value Let's say there's a table that stores jobs and their state (something like CREATE TABLE jobs (id INT, status VARCHAR(255)), how to ensure that only one job could be in running state i.e. how a query that tries to start a job should look like?
That can't be performed with single query. Therefore you have to create stored routine like that:

CREATE PROCEDURE `start_job`( IN `pid` INT, IN `state` varchar )
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
BEGIN
   UPDATE * 
     FROM jobs AS w
      SET w.status = "idle"
    WHERE w.status = "running"
   ;
   INSERT INTO jobs (id, status)
   VALUES (pid, state)
       ON DUPLICATE KEY UPDATE
          status = state
   ;
END