SQL READ

What is the SQL READ lock in MySQL?

READ is a lock modifier in the MySQL LOCK TABLES statement that grants shared, read-only access to one or more tables.

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 READ: MySQL, MariaDB

SQL READ Full Explanation

In MySQL and MariaDB, the keyword READ appears only as part of the LOCK TABLES statement. When you lock a table with READ, the current session obtains a shared read lock. Other sessions can still read the table but cannot insert, update, delete, or otherwise modify rows until the lock is released with UNLOCK TABLES or the connection terminates. READ locks guarantee a consistent view of the data for the locking session, which is useful for backups, bulk exports, or long analytical queries.READ locks are session-level and block all write operations (INSERT, UPDATE, DELETE, REPLACE, ALTER, TRUNCATE, LOAD DATA, etc.) by other sessions. They also block DDL that would change table structure. The locking session itself cannot perform writes while holding a READ lock; attempting to do so raises an error. If you need to allow the locking session to write, use the WRITE lock instead.The lock remains active until UNLOCK TABLES is issued explicitly or the session ends. Because READ locks can cause write starvation under heavy traffic, use them sparingly and keep lock durations short.READ is not a standalone SQL statement and does not exist in PostgreSQL, SQL Server, Oracle, or SQLite. Those databases provide different mechanisms such as transaction isolation levels, advisory locks, or explicit lock modes.

SQL READ Syntax

LOCK TABLES
table_name [AS alias] READ [, table_name2 READ ...];
-- release
UNLOCK TABLES;

SQL READ Parameters

Example Queries Using SQL READ

-- Obtain a shared read lock on two tables
LOCK TABLES customers READ, orders READ;

-- Now run a long analytical query safely
SELECT c.id, c.name, SUM(o.total_amount) AS lifetime_value
FROM customers c
JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY lifetime_value DESC;

-- Release the locks when done
UNLOCK TABLES;

Expected Output Using SQL READ

The tables customers and orders become read-locked for the session
Other sessions can run SELECT statements but are blocked from any writes until UNLOCK TABLES executes
The SELECT query returns the requested aggregated data

Use Cases with SQL READ

Generating logical backups without using mysqldump
Exporting large result sets to CSV or external files
Building read-only reports that must remain consistent while data changes elsewhere
Preventing accidental writes during ad-hoc analysis

Common Mistakes with SQL READ

Forgetting to call UNLOCK TABLES, leaving tables locked indefinitely
Attempting INSERT or UPDATE within the same session after acquiring a READ lock
Expecting READ locks to work in non-MySQL systems
Assuming READ locks stop other sessions from reading the table (they do not)

First Introduced In

MySQL 3.23

Frequently Asked Questions

What happens if I forget to UNLOCK TABLES?

The lock stays in place for the lifetime of the session, blocking writes from other sessions. Always run UNLOCK TABLES as soon as your read-only task is complete.

Is READ the same as READ LOCAL?

No. READ LOCAL allows other sessions to perform non-conflicting INSERT operations (for MyISAM) while READ blocks all writes. Use READ LOCAL when slight write flexibility is acceptable.

Does READ work inside a transaction?

LOCK TABLES implicitly commits any active transaction in MySQL. Use START TRANSACTION READ ONLY if you need transactional consistency without explicit table locks.

Can I apply a READ lock to views?

Yes, but the underlying base tables are locked, not the view definition itself.