SQL LOW_PRIORITY

What is SQL LOW_PRIORITY?

LOW_PRIORITY forces a write or select statement to wait until no other clients are reading or writing the affected table before it acquires its lock.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL LOW_PRIORITY: Supported: MySQL, MariaDB Not supported: PostgreSQL, SQL Server, Oracle, SQLite, Standard SQL

SQL LOW_PRIORITY Full Explanation

LOW_PRIORITY is a MySQL and MariaDB keyword that can be prefixed to INSERT, REPLACE, UPDATE, DELETE, or SELECT statements. When specified, the statement does not immediately request the lock it normally needs. Instead, it waits until other sessions have released conflicting locks, giving existing traffic precedence.For modification statements, LOW_PRIORITY delays the acquisition of the write lock until there are no pending read requests on the table. This reduces read blocking at the cost of slower writes. For SELECT, it yields to pending writes so that long-running analytics queries do not starve transactional updates.With InnoDB, which uses row-level locks, the effect is limited because InnoDB already allows concurrent reads and writes. LOW_PRIORITY still controls the order in which the lock metadata is granted, but only when a higher granularity lock (such as AUTO-INC or metadata lock) is required.LOW_PRIORITY has no impact inside explicit transactions that already hold locks, and it is ignored by most other database engines. It cannot be combined with HIGH_PRIORITY in the same statement.

SQL LOW_PRIORITY Syntax

INSERT LOW_PRIORITY INTO table_name ...;
REPLACE LOW_PRIORITY INTO table_name ...;
UPDATE LOW_PRIORITY table_name SET ... WHERE ...;
DELETE LOW_PRIORITY FROM table_name WHERE ...;
SELECT LOW_PRIORITY column_list FROM table_name ...;

SQL LOW_PRIORITY Parameters

Example Queries Using SQL LOW_PRIORITY

-- Defer a bulk update until idle time
UPDATE LOW_PRIORITY orders
SET status = 'archived'
WHERE order_date < DATE_SUB(CURDATE(), INTERVAL 1 YEAR);

-- Insert rows without blocking readers
INSERT LOW_PRIORITY INTO audit_log (event, created_at)
VALUES ('PURGE_OLD_ORDERS', NOW());

-- Reporting query that should not starve OLTP writes
SELECT LOW_PRIORITY *
FROM large_fact_table
WHERE event_date >= '2024-01-01';

Expected Output Using SQL LOW_PRIORITY

The statements run normally but will wait to obtain their required locks until no other sessions hold conflicting locks
Readers continue uninterrupted while writes wait, or vice versa for LOW_PRIORITY SELECT

Use Cases with SQL LOW_PRIORITY

Bulk maintenance or archival jobs that should not block customer reads
Append-only logging where latency is less important than uptime
Long analytical SELECT queries on busy OLTP tables
Nightly ETL scripts that must coexist with interactive traffic

Common Mistakes with SQL LOW_PRIORITY

Expecting dramatic impact with InnoDB row-level locking - effect is minor
Using LOW_PRIORITY inside a transaction and wondering why it still waits (the outer transaction holds locks)
Assuming other databases support the keyword
Combining LOW_PRIORITY and HIGH_PRIORITY in the same statement

First Introduced In

MySQL 3.22

Frequently Asked Questions

What statements support LOW_PRIORITY?

INSERT, REPLACE, UPDATE, DELETE, and SELECT in MySQL and MariaDB accept the modifier.

Does LOW_PRIORITY slow down my query?

Yes. The statement waits for other sessions to finish, so its start time can be delayed. The execution speed after the lock is granted is unchanged.

Is LOW_PRIORITY useful on a replica?

Often. Reporting replicas can run long SELECT LOW_PRIORITY queries without impacting asynchronous replication writes.

How do I remove LOW_PRIORITY from a query?

Simply delete the keyword. The statement reverts to default lock acquisition behavior.