innodb - How does MySQL handle lock queuing order for SELECT ... FOR UPDATE? - Stack Overflow

I have a scenario where multiple jobs (job1, job2, and job3) may arrive at the same time and need to execute in a serialized manner. I'm using MySQL with the InnoDB engine and a locking mechanism with a lock_table table. Here's the table schema:

CREATE TABLE lock_table (
    key_name VARCHAR(255) NOT NULL,
    UNIQUE KEY (key_name)
) ENGINE = InnoDB;

Here’s the workflow I’m trying to achieve:

1. job1 acquires a lock using:

   SELECT * FROM lock_table WHERE key_name = 'resource1' FOR UPDATE;
   

   This prevents other jobs from accessing the same resource (resource1) until job1 completes.

2. While job1 is running, both job2 and job3 also request the same lock:

   SELECT * FROM lock_table WHERE key_name = 'resource1' FOR UPDATE;
   

   These jobs should wait until job1 finishes.

3. After job1 finishes, I want the jobs in the queue (job2 and job3) to execute in the order they requested the lock.

My questions are:

1. Does MySQL's SELECT ... FOR UPDATE mechanism guarantee that the jobs (job2 and job3) will execute in the order they requested the lock?
2. Is there an official MySQL documentation link that explains how MySQL handles the order of queued lock requests?
3. Is this approach the best practice for serializing job execution, or is there a better way to achieve this?

I am designing a system where multiple jobs (job1, job2, job3, etc.) may attempt to process the same resource concurrently. To ensure serialized execution, I'm using MySQL’s SELECT ... FOR UPDATE to lock the resource based on its key_name.

I have a scenario where multiple jobs (job1, job2, and job3) may arrive at the same time and need to execute in a serialized manner. I'm using MySQL with the InnoDB engine and a locking mechanism with a lock_table table. Here's the table schema:

CREATE TABLE lock_table (
    key_name VARCHAR(255) NOT NULL,
    UNIQUE KEY (key_name)
) ENGINE = InnoDB;

Here’s the workflow I’m trying to achieve:

1. job1 acquires a lock using:

   SELECT * FROM lock_table WHERE key_name = 'resource1' FOR UPDATE;
   

   This prevents other jobs from accessing the same resource (resource1) until job1 completes.

2. While job1 is running, both job2 and job3 also request the same lock:

   SELECT * FROM lock_table WHERE key_name = 'resource1' FOR UPDATE;
   

   These jobs should wait until job1 finishes.

3. After job1 finishes, I want the jobs in the queue (job2 and job3) to execute in the order they requested the lock.

My questions are:

1. Does MySQL's SELECT ... FOR UPDATE mechanism guarantee that the jobs (job2 and job3) will execute in the order they requested the lock?
2. Is there an official MySQL documentation link that explains how MySQL handles the order of queued lock requests?
3. Is this approach the best practice for serializing job execution, or is there a better way to achieve this?

I am designing a system where multiple jobs (job1, job2, job3, etc.) may attempt to process the same resource concurrently. To ensure serialized execution, I'm using MySQL’s SELECT ... FOR UPDATE to lock the resource based on its key_name.

• mysql
• innodb

Share Improve this question Follow edited yesterday Mark Rotteveel 109k224224 gold badges155155 silver badges217217 bronze badges asked yesterday Mandar DhupdhareMandar Dhupdhare 911 bronze badge New contributor Mandar Dhupdhare is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct. 1

• Is that row the only one locked in each transaction? – Rick James Commented 18 hours ago

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

As the MySQL manual on scheduling transactions says:

The CATS algorithm prioritizes waiting transactions by assigning a scheduling weight, which is computed based on the number of transactions that a transaction blocks. For example, if two transactions are waiting for a lock on the same object, the transaction that blocks the most transactions is assigned a greater scheduling weight. If weights are equal, priority is given to the longest waiting transaction.

So, if you can guarantee that the transactions performing the for update statements will always block the same number of transactions, then innodb will grant the for update locks in the order the transactions issue it.

I'm sorry, but your third question is loaded and is impossible to provide an objective answer based on a single known criterion.