这个存储过程的主要原因是选择和更新行,这样我们就不会同时得到多个处理器选择的同一行。
CREATE DEFINER=`admin`@`%` PROCEDURE `select_and_start_non_started`(
IN p_companyId INT(11),
IN p_howMany INT,
IN p_instance varchar(50),
IN p_status varchar(50),
IN p_updateBy varchar(50)
)
BEGIN
DECLARE v_currentId INT;
DECLARE v_loopDone INT DEFAULT 0;
DECLARE v_loopCounter INT DEFAULT 0;
DECLARE v_idList VARCHAR(1024) DEFAULT NULL;
DECLARE queue_csr CURSOR FOR
SELECT id FROM queue
WHERE (status in (_utf8'NEW' COLLATE utf8_unicode_ci,
_utf8'RESTARTED' COLLATE utf8_unicode_ci,
_utf8'WAITING' COLLATE utf8_unicode_ci,
_utf8'QUEUED' COLLATE utf8_unicode_ci))
AND if(LENGTH(p_companyId) > 0, companyid=p_companyId, true)
LIMIT p_howMany FOR UPDATE;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_loopDone=1;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
SET autocommit = TRUE;
RESIGNAL;
END;
SET v_idList = "";
SET autocommit = FALSE;
START TRANSACTION;
OPEN queue_csr;
iq_loop:LOOP
FETCH queue_csr INTO v_currentId;
IF v_loopDone THEN LEAVE iq_loop; END IF;
UPDATE queue SET status = p_status COLLATE utf8_unicode_ci, updatedDate=NOW(), updatedBy=p_updateBy, recordStatus=p_instance WHERE id = v_currentId;
SET v_idList = CONCAT(v_idList, ",", v_currentId);
SET v_loopCounter=v_loopCounter+1;
IF v_loopCounter > p_howMany THEN LEAVE iq_loop; END IF;
END LOOP iq_loop;
CLOSE queue_csr;
SET v_loopDone=0;
COMMIT;
SET autocommit = TRUE;
SELECT * FROM queue q WHERE FIND_IN_SET(id, v_idList);
END
