SQL READ_WRITE

What is SQL READ WRITE?

Marks a transaction as able to perform INSERT, UPDATE, DELETE, and other write operations.

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_WRITE: PostgreSQL 9.6+, MySQL 8.0+, MariaDB 10.4+, Oracle 12c+, SQLite (partial via PRAGMA query_only), Standard SQL. Not supported in SQL Server.

SQL READ_WRITE Full Explanation

READ WRITE is a transaction characteristic defined by the SQL standard. When supplied to START TRANSACTION, BEGIN, or SET TRANSACTION, it tells the database that the current transaction may modify data as well as read it. If omitted, most systems default to READ WRITE, but explicitly declaring it improves clarity and allows code that switches between READ ONLY and READ WRITE for performance or safety reasons.A READ WRITE transaction behaves the same as a default transaction: it acquires any locks required for data changes and is subject to all usual isolation-level rules. The companion keyword READ ONLY tells the optimizer that the transaction will not issue writes, enabling certain optimizations and preventing accidental changes. Switching between the two can be useful in mixed read/write workloads, reporting jobs, and long-running analytical queries.Important caveats:- You must declare READ WRITE (or READ ONLY) before the first query in the transaction block.- Some systems (PostgreSQL, MySQL) allow mixing READ WRITE with ISOLATION LEVEL and access mode in the same statement.- SQL Server does not support the READ WRITE phrase; it relies on implicit write capability or the READ ONLY hint on some statements.- Attempting to change the access mode inside an active transaction usually raises an error.

SQL READ_WRITE Syntax

START TRANSACTION READ WRITE;
-- or
SET TRANSACTION READ WRITE;
-- PostgreSQL also allows
BEGIN READ WRITE;

SQL READ_WRITE Parameters

Example Queries Using SQL READ_WRITE

-- Explicitly start a writable transaction
START TRANSACTION READ WRITE;
INSERT INTO orders (user_id,total) VALUES (42,99.95);
UPDATE users SET last_purchase = NOW() WHERE id = 42;
COMMIT;

-- Switch a session that is defaulting to READ ONLY back to READ WRITE
SET TRANSACTION READ WRITE;
DELETE FROM temp_report WHERE created_at < NOW() - INTERVAL '7 days';
COMMIT;

Expected Output Using SQL READ_WRITE

The database begins a transaction that accepts data modifications
Subsequent write statements execute normally until COMMIT or ROLLBACK

Use Cases with SQL READ_WRITE

Explicitly documenting that a block of work should allow writes after running a READ ONLY transaction
Toggling between read-only analytical queries and write-heavy operations inside stored procedures
Preventing accidental writes in reporting code by defaulting to READ ONLY, then enabling READ WRITE only where required
Clarifying intent in code reviews and improving self-documentation of complex transactions

Common Mistakes with SQL READ_WRITE

Using READ WRITE after the first statement in a transaction, which raises an error
Assuming SQL Server supports the keyword
Forgetting to switch back from READ ONLY, leading to errors when a write is attempted
Believing that READ WRITE changes isolation levels or locking behavior beyond permitting writes

First Introduced In

SQL-92

Frequently Asked Questions

What does READ WRITE add if transactions are writable by default?

Explicitly stating READ WRITE documents intent and contrasts with READ ONLY blocks, making code easier to audit and review.

How do I toggle between READ ONLY and READ WRITE in the same session?

Issue SET TRANSACTION READ ONLY; before running read-only queries, then start a new transaction with START TRANSACTION READ WRITE; for write operations.

Will READ WRITE override an isolation level setting?

No. Access mode (READ WRITE or READ ONLY) is independent of the isolation level chosen for the transaction.

Is there a performance cost to declaring READ WRITE?

Negligible. The database already assumes a writable transaction. Declaring it simply records the attribute in the transaction metadata.