SQL ACCESS

What is the SQL ACCESS keyword?

ACCESS is a reserved SQL keyword that appears inside the ACCESS MODE clause to declare whether a transaction is READ ONLY or READ WRITE.

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 ACCESS: PostgreSQL, MySQL, MariaDB, SQL Server, Oracle, SQLite (with limited syntax support)

SQL ACCESS Full Explanation

ACCESS by itself is not an executable statement. In the SQL standard it is reserved for the phrase ACCESS MODE, which can be supplied in transaction control statements such as SET TRANSACTION, START TRANSACTION, and SET SESSION CHARACTERISTICS. The ACCESS MODE tells the database engine whether the current or future transactions are allowed to change data (READ WRITE) or must not perform data-modifying operations (READ ONLY).Because ACCESS is reserved, unquoted use of the word as a table, column, or alias name may raise an error in compliant engines. If you must use it as an identifier, quote or escape it according to the dialect rules (e.g., "ACCESS" in PostgreSQL, `ACCESS` in MySQL).

SQL ACCESS Syntax

-- Setting the access mode for the current transaction
SET TRANSACTION
    ACCESS MODE READ ONLY;

-- Equivalent shortcut in many engines
START TRANSACTION READ ONLY;

SQL ACCESS Parameters

Example Queries Using SQL ACCESS

-- Example 1: Run a reporting query in a read-only transaction
START TRANSACTION READ ONLY;
SELECT * FROM orders WHERE order_date >= CURRENT_DATE - INTERVAL '7 days';
COMMIT;

-- Example 2: Switch to read-write mode explicitly
SET TRANSACTION ACCESS MODE READ WRITE;
UPDATE products SET price = price * 1.05 WHERE category = 'Widgets';
COMMIT;

-- Example 3: Default access mode for all future transactions in the session
SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY;

Expected Output Using SQL ACCESS

The transaction starts in the specified mode
In READ ONLY mode any attempt to INSERT, UPDATE, DELETE, or MERGE will result in an error until the transaction commits or rolls back
In READ WRITE mode both read and write operations are allowed

Use Cases with SQL ACCESS

Establish safe, non-mutating sessions for dashboards and ad-hoc analytics
Guard critical reporting jobs from accidental data changes
Ensure long-running ETL validation steps do not hold write locks
Optimize performance by letting the planner choose lighter locking strategies for read-only workloads

Common Mistakes with SQL ACCESS

Treating ACCESS as a standalone SQL command
Using ACCESS unquoted as an identifier and receiving a syntax error
Assuming READ ONLY transactions can still create temporary tables or sequences (varies by engine)
Forgetting to commit a READ ONLY transaction, which can still hold shared locks indefinitely

First Introduced In

SQL-92

Frequently Asked Questions

What does ACCESS MODE mean?

It is a clause inside SET TRANSACTION, START TRANSACTION, or SET SESSION CHARACTERISTICS that identifies the transaction as READ ONLY or READ WRITE.

Why would I use READ ONLY transactions?

They prevent accidental data changes, reduce locking overhead, and allow replicas configured as read-only to serve traffic.

How do I change back to READ WRITE?

Issue SET TRANSACTION ACCESS MODE READ WRITE inside the same transaction, or simply start a new transaction without the READ ONLY attribute.

Is ACCESS supported in all databases?

Most modern engines recognize ACCESS inside transaction clauses, but exact syntax varies. Always consult your database documentation.