Zaar Hai Zaar Hai - 3 months ago 24
MySQL Question

How to avoid mysql from locking ALL tables from SELECT ... INNER JOIN ... FOR UPDATE

I have two mysql tables:

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

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


I have a lot of clients that connect, take one job for a particular server that is
runnable
and set it to
running
.

Here is how I currently do it (in stored procedure):

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 $$


That works just fine, but when number of concurrent clients become large (hundreds), I see that there is a big lock congestion going on
tbl_servers
table. I understand that
FOR UPDATE
statement locks all of the tables, but I'm actually using
tbl_servers
as read-only table.

Question: How to avoid lock congestion (i.e. locking at all) on
tbl_servers
?

The one thing I can think of is to separate my query into two - first convert server name to id and then query just
tbl_jobs
, but in my real application one server name can have many ids (I know it sounds odd, but I've just simplified my application here for illustration). So the second query on
tbl_jobs
would require prepared statement.

I'm sure there is a more elegant solution.

The system:


  • MySQL 5.1.67 on Amazon RDS

  • All tables are InnoDb

  • I do have an index on
    tbl_jobs.server_id


Answer

This should avoid the locking on table 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 $$
Comments