FirebirdSQL logo
 Data Definition Language (DDL)Procedural SQL (PSQL) 

SKIP LOCKED clause

Adriano dos Santos Fernandes

Tracker ticket: #7350

SKIP LOCKED can be used with SELECT …​ WITH LOCK, UPDATE and DELETE statements.It makes the engine skip records locked by other transactions instead of waiting on them or raise conflict errors.

This is very useful to implement work queues where one or more processes post work to a table and issue an event, while workers listen for events and read/delete items from the table.Using SKIP LOCKED multiple workers can get exclusive work items from the table without conflicts.

Syntax
SELECT
  [FIRST ...]
  [SKIP ...]
  FROM <sometable>
  [WHERE ...]
  [PLAN ...]
  [ORDER BY ...]
  [{ ROWS ... } | {OFFSET ...} | {FETCH ...}]
  [FOR UPDATE [OF ...]]
  [WITH LOCK [SKIP LOCKED]]

UPDATE <sometable>
  SET ...
  [WHERE ...]
  [PLAN ...]
  [ORDER BY ...]
  [ROWS ...]
  [SKIP LOCKED]
  [RETURNING ...]

DELETE FROM <sometable>
  [WHERE ...]
  [PLAN ...]
  [ORDER BY ...]
  [ROWS ...]
  [SKIP LOCKED]
  [RETURNING ...]
Note

If a statement has both SKIP LOCKED and OFFSET/SKIP/ROWS subclauses, locked rows may be skipped before OFFSET/SKIP/ROWS subclause can account for them, thus skipping more rows than specified in OFFSET/SKIP/ROWS.

Examples:

  • Prepare metadata

    create table emails_queue (
    subject varchar(60) not null,
    text blob sub_type text not null
);

set term !;

create trigger emails_queue_ins after insert on emails_queue
as
begin
    post_event('EMAILS_QUEUE');
end!

set term ;!

  • Sender application or routine

    insert into emails_queue (subject, text)
  values ('E-mail subject', 'E-mail text...');
commit;

  • Client application

    -- Client application can listen to event `EMAILS_QUEUE` to actually send e-mails using this query:

delete from emails_queue
  rows 10
  skip locked
  returning subject, text;

    More than one instance of the application may be running, for example to load balance work.

Support for WHEN NOT MATCHED BY SOURCE in the MERGE statement

Adriano dos Santos Fernandes

Tracker ticket: #6681

Syntax
<merge when> ::=
		<merge when matched> |
		<merge when not matched by target> |
		<merge when not matched by source>

<merge when not matched by target> ::=
		WHEN NOT MATCHED [ BY TARGET ] [ AND <condition> ] THEN
			INSERT [ <left paren> <column list> <right paren> ]
				VALUES <left paren> <value list> <right paren>

<merge when not matched by source> ::=
		WHEN NOT MATCHED BY SOURCE [ AND <condition> ] THEN
			{ UPDATE SET <assignment list> | DELETE }

<merge when not matched by target> is called when a source record matches no record in target.INSERT will change the target table.

<merge when not matched by source> is called when a target record matches no record in source.UPDATE or DELETE will change the target table.

Example
MERGE
	INTO customers c
	USING new_customers nc
	ON (c.id = nc.id)
	WHEN MATCHED THEN
		UPDATE SET name = nc.name
	WHEN NOT MATCHED BY SOURCE THEN
		DELETE