CREATE TABLE `queue` (
  `object_id` int(10) unsigned NOT NULL,
  `status` enum('W','L','E') NOT NULL DEFAULT 'W',
  `num_attempts` int(1) NOT NULL DEFAULT '0',
  `priority` int(1) unsigned NOT NULL DEFAULT '5',
  `locked_time` datetime DEFAULT NULL,
  `locked_by` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`object_id`),
  KEY `status` (`status`,`priority`)
) ENGINE=InnoDB;

CREATE PROCEDURE `sp_getNextInQueue`(IN lockedBy VARCHAR(100),
                                     OUT objectIdRet INT)
BEGIN
 DECLARE
  lockTimeout,
  objectId,
  maxAttempts INT;
 SET
  lockTimeout = 5,
  objectId = NULL,
  maxAttempts = 3;
 
 # Pirma pakeiciame statusa objektu, kurie stovi eileje per
 # ilgai ir juos apdoroti bandyta daugiau nei nustatyta kartu
 UPDATE queue
  SET
   `status` = 'E'
  WHERE
   `status` = 'L'
   AND TIMESTAMPDIFF(MINUTE, locked_time, NOW()) > lockTimeout
   AND num_attempts >= maxAttempts;
 
 # Pakeiciame objektu statusa, kurie buvo uzrakinti per ilgai
 UPDATE queue
  SET `status` = 'W',
  locked_time = NULL,
  locked_by = NULL
 WHERE
  `status` = 'L'
  AND TIMESTAMPDIFF(MINUTE, locked_time, NOW()) > lockTimeout;
 
 # imame pirma objekta is laukianciuju eiles pagal prioriteta
 START TRANSACTION;
 SELECT object_id INTO objectId
  FROM queue
  WHERE `status` = 'W'
  AND (`num_attempts` < maxAttempts)
  ORDER BY priority LIMIT 1 FOR UPDATE;
 
  # jei toki objekta randame - uzrakiname
  IF (objectId > 0) THEN
   UPDATE queue
    SET `status` = 'L',
    num_attempts = COALESCE(num_attempts, 0) + 1,
    locked_time = NOW(),
    locked_by = lockedBy
    WHERE object_id = objectId;
  END IF;
 
 COMMIT;
 
 # atiduodame objekto ID
 SET objectIdRet = objectId;
END$$
 
DELIMITER ;

SET @objId = NULL;
CALL sp_getNextInQueue('test', @objId);
SELECT @objId;