mysql - 如何避免 mysql 锁定 SELECT ... INNER JOIN ... FOR UPDATE 中的所有表

Question

我有两个 mysql 表:

tbl_jobs (
    id INTEGER PRIMARY KEY,
    status ENUM("runnable", "running", "finished"),
    server_id INTEGER
)

tbl_servers (
    is INTEGER PRIMARY KEY,
    name VARCHAR(50)
)

我有很多连接的客户端，为可运行的特定服务器获取一项作业并将其设置为正在运行。

这是我目前的做法(在存储过程中):

DELIMITER $$
CREATE PROCEDURE GET_JOB(serverName VARCHAR(50))
BEGIN
    DECLARE jobId INTEGER DEFAULT NULL;
    SELECT id FROM tbl_jobs j INNER JOIN tbl_servers s on j.server_id=s.id
              WHERE j.state='runnable' AND s.server_name=serverName
              ORDER BY job_id ASC LIMIT 1
              INTO jobId FOR UPDATE;
    IF IFNULL(jobId, 0) = 0 THEN
        SELECT 0;
    END IF;
    UPDATE tbl_jobs SET state='running' WHERE job_id=jobId;
    SELECT jobId;
END $$

这工作得很好，但是当并发客户端数量变大(数百)时，我发现tbl_servers表上存在很大的锁拥塞。我知道 FOR UPDATE 语句会锁定所有表，但实际上我使用 tbl_servers 作为只读表。

问题:如何避免 tbl_servers 上的锁拥塞(即完全锁定)？

我能想到的一件事是将我的查询分成两部分 - 首先将服务器名称转换为 id，然后仅查询 tbl_jobs，但在我的实际应用程序中，一个服务器名称可以有多个 id (我知道这听起来很奇怪，但我只是在这里简化了我的应用程序以供说明)。因此，对 tbl_jobs 的第二个查询需要准备好的语句。

我确信有一个更优雅的解决方案。

系统:

• Amazon RDS 上的 MySQL 5.1.67
• 所有表都是 InnoDb
• 我确实在 tbl_jobs.server_id 上有索引

Answer 1

这应该避免锁定表tbl_servers

DELIMITER $$
CREATE PROCEDURE GET_JOB(serverName VARCHAR(50))
BEGIN
    DECLARE jobId INTEGER DEFAULT NULL;

    -- get the server id's
    CREATE TEMPORARY TABLE tmp_srvID 
    SELECT id
    INTO srvID 
    FROM tbl_servers
    WHERE name = serverName;

    SELECT id 
    INTO jobId 
    FROM tbl_jobs 
    WHERE state='runnable'

      AND server_id IN ( SELECT id FROM tmp_srvID ) 

    ORDER BY job_id ASC 
    LIMIT 1
    FOR UPDATE;

    DROP TABLE tmp_srvID;

    IF IFNULL(jobId, 0) = 0 THEN
        SELECT 0;
    ELSE
        UPDATE tbl_jobs SET state='running' WHERE job_id=jobId;
        SELECT jobId;
    END IF;
END $$