FOR UPDATE SKIP LOCKED - Oracle to MySQL Migration

In Oracle, FOR UPDATE SKIP LOCKED clause is usually used to select and process tasks from a queue by multiple concurrent sessions. It allows a session to query the queue table, skip rows currently locked by other sessions, select the next unlocked row, and lock it for processing.

This clause is not supported by MySQL, so you have to re-design the queue processing (see below for details).

Let's assume there is the following queue table in Oracle:

Oracle:

  -- Tasks queue
  CREATE TABLE tasks
  (
     id NUMBER(10) NOT NULL,
     name VARCHAR2(70),
     status CHAR(1),
     updated TIMESTAMP
  );
 
  -- Let's add a few tasks
  INSERT INTO tasks (id, name) VALUES (1, 'Sales report');
  INSERT INTO tasks (id, name) VALUES (2, 'Financial report');
  COMMIT;

Now let's run 2 concurrent sessions to process tasks one by one:

Oracle (Session 1):

  -- Select one task
  SELECT * FROM tasks 
  WHERE status IS NULL AND rownum = 1
  FOR UPDATE SKIP LOCKED; 
  # 1   Sales Report   NULL   NULL
 
  -- Set status to S (selected)
  UPDATE tasks
  SET status = 'S', updated = SYSTIMESTAMP
  WHERE id = 1;

Now the second session selects a task:

Oracle (Session 2):

  -- Select one task
  SELECT * FROM tasks 
  WHERE status IS NULL AND rownum = 1
  FOR UPDATE SKIP LOCKED; 
  # no rows selected

Note the second session did not select a task as rownum counts locked and unlocked rows.

So in Oracle you cannot use rownum = 1 to select tasks one by one in multiple concurrent sessions, and typically you have to use a cursor and specify a larger rownum limit to select rows:

Oracle (Session 2):

  -- Select one task (specify rownum <= 2)
  SELECT * FROM tasks 
  WHERE status IS NULL AND rownum <= 2
  FOR UPDATE SKIP LOCKED; 
  # 2   Financial Report   NULL   NULL

Now the second session was able to select the task.

MySQL does not support SKIP LOCKED clause, and to select tasks one by one by multiple concurrent sessions you can use the following approach:

• Start a short transaction to mark the task as selected and immediately complete it
• Run a long-running transaction to process the task without locking the queue table
• Start a short transaction to mark the task as completed
• Have a job to return uncompleted tasks to the queue

Assume we have the following task queue in MySQL:

MySQL:

  -- Tasks queue
  CREATE TABLE tasks
  (
     id INT NOT NULL,
     name VARCHAR(70),
     status CHAR(1),
     updated DATETIME
  );
 
  -- Let's add a few tasks
  INSERT INTO tasks (id, name) VALUES (1, 'Sales report');
  INSERT INTO tasks (id, name) VALUES (2, 'Financial report');
  COMMIT;

Now the first session starts a transaction to select a task, and then a transaction to process it:

MySQL (Session 1)

  -- Short transaction to select a task
  START TRANSACTION;
 
  SELECT * FROM tasks
  WHERE status IS NULL
  LIMIT 1
  FOR UPDATE;
  # 1   Sales Report   NULL   NULL
 
  -- Mark the task as selected
  UPDATE tasks
  SET status = 'S', updated = NOW()
  WHERE id = 1;
 
  -- Terminate the transaction so other sessions can select tasks
  COMMIT;
 
  -- ... Long running task processing without a lock on the queue table

Now the second session can also select a task:

MySQL (Session 2)

  START TRANSACTION;
 
  SELECT * FROM tasks
  WHERE status IS NULL
  LIMIT 1
  FOR UPDATE;
  # 2   Financial Report   NULL   NULL
  ...

If a session abnormally terminates its updates will be rolled back by MySQL and the database will be in a consistent state, but not the task status in the queue. The transaction that set the status to Selected was committed, and you need a routine or job that can reset the status of such tasks so they can be processed again.

• SQLines Data - Data Transfer and Database Schema Migration Tool
• Free Online SQL Conversion Tool

• Oracle to MySQL Migration Reference

SQLines offers services and tools to help you migrate databases and applications. For more information, please contact us at support@sqlines.com.

Written by Dmitry Tolpeko - March 2013.