Does DDL Block DML? (It Depends on Table-Level Locks)

🧭 What You’ll Learn

● Why DDL may not block DML in YugabyteDB by default
● How behavior changes when table-level locks are enabled (2025.1.1+)
● How to prove blocking using pg_locks
● What this means for PostgreSQL compatibility expectations

❌ Behavior When Table-Level Locks Are NOT Enabled (Default)

By default, table-level locks are disabled in YugabyteDB.

Let’s attempt a classic PostgreSQL-style blocking scenario:

				
					BEGIN;
LOCK TABLE test IN ACCESS EXCLUSIVE MODE;

Result:

				
					ERROR: ACCESS EXCLUSIVE not supported yet

That’s expected… Without table-level locks:

● ACCESS EXCLUSIVE is not supported
● Full PostgreSQL table-level lock semantics are not enforced

❗ Important: DDL Does NOT Block INSERTs in This Mode

Even if you run a DDL operation such as:

				
					DROP INDEX test_c2_idx;

while another session runs:

				
					INSERT INTO test SELECT 1, 'A';

The INSERT does not block. DML continues during DDL operations when table-level locks are disabled.

This behavior is documented in:

💡 What This Means

● PostgreSQL relies on ACCESS EXCLUSIVE locks for non-concurrent DDL.
● YugabyteDB (without table locks) allows higher concurrency.
● But traditional Postgres-style blocking behavior does not occur.

✅ Behavior When Table-Level Locks ARE Enabled (2025.1.1+)

Now let’s enable table-level locks and repeat the test.

Test Environment:

● YugabyteDB 2025.2.0
● Table-level locks enabled

📘 Enabling Table-Level Locks

Table-level locks are disabled by default.

See official docs:

🧪 Demo Scenario

Example table and index:

				
					CREATE test (c1 INT PRIMARY KEY, c2 TEXT);
CREATE INDEX test_c2_idx ON test(c2);

We use three sessions:

🧩 Session	🎯 Purpose
`LOCK_HOLDER`	Holds an ACCESS EXCLUSIVE lock on `test` (simulates the blocking condition).
`DDL_SESSION`	Runs the DDL statement (example: `DROP INDEX`) and demonstrates it waiting.
`DML_SESSION`	Attempts concurrent DML (example: `INSERT`) and demonstrates it being blocked.

Tip: Set application_name in each session so the statements are easy to identify in
pg_stat_activity, pg_locks, and ASH.

🔐 Session #1 – Hold the Lock

				
					SET application_name = 'LOCK_HOLDER';
BEGIN;
LOCK TABLE test IN ACCESS EXCLUSIVE MODE;
SELECT pg_sleep(60);
-- keep the transaction open until you're done observing blocking

🛠 Session #2 – Run DDL

				
					SET application_name = 'DDL_SESSION';
DROP INDEX testc2_idx;

➕ Session #3 – Run DML

				
					SET application_name = 'DML_SESSION';
INSERT INTO test SELECT 1, 'A';

Now both DDL and INSERT hang until the lock is released.

🚨 The Smoking Gun: `pg_locks`

				
					SELECT
  relation::regclass AS rel,
  mode,
  granted,
  waitstart,
  ybdetails->>'transactionid' AS yb_txn_id,
  CASE
    WHEN mode='AccessExclusiveLock' AND granted THEN  'lock held (blocks reads+writes)'
    WHEN mode='AccessExclusiveLock' AND NOT granted THEN 'DDL waiting for lock'
    WHEN mode='RowExclusiveLock' AND NOT granted THEN 'DML waiting (blocked)'
    ELSE 'other'
  END AS interpretation
FROM pg_locks
WHERE relation='public.test'::regclass
ORDER BY granted DESC, mode, waitstart;

Example output:

				
					. rel   |        mode         | granted |           waitstart           |              yb_txn_id               |         interpretation
--------+---------------------+---------+-------------------------------+--------------------------------------+---------------------------------
  test  | AccessExclusiveLock | t       |                               | d2156b92-7a30-4e80-b7d6-6008fecc08ae | lock held (blocks reads+writes)
  test  | AccessExclusiveLock | f       | 2026-02-12 15:04:55.470879+00 | fa779eb5-2d59-4d78-8967-7bd33f5f0bd2 | DROP INDEX waiting for lock
  test  | RowExclusiveLock    | f       | 2026-02-12 15:04:57.647031+00 | 4598a5ee-c92e-478b-ab03-ffac63d62341 | INSERT waiting (DML blocked)
(3 rows)

🔬 What This Proves

● The table is locked with AccessExclusiveLock.
● The DDL statement is waiting for that same lock.
● The INSERT is blocked waiting for RowExclusiveLock.
● With table-level locks enabled, DDL blocks DML.

📊 Optional: ASH Correlation

To confirm sustained blocking during the lock window:

				
					WITH sessions AS (
  SELECT pid, application_name, state, query
  FROM pg_stat_activity
  WHERE application_name IN ('LOCK_HOLDER','DDL_SESSION','DML_SESSION')
),
ash_summary AS (
  SELECT
    a.pid,
    a.wait_event,
    count(*) AS samples
  FROM yb_active_session_history a
  JOIN sessions s USING (pid)
  GROUP BY a.pid, a.wait_event
),
ash_top AS (
  SELECT DISTINCT ON (pid)
    pid, wait_event, samples
  FROM ash_summary
  ORDER BY pid, samples DESC
)
SELECT
  s.application_name,
  s.state,
  s.query,
  a.wait_event,
  a.samples
FROM sessions s
LEFT JOIN ash_top a USING (pid)
ORDER BY s.application_name;

Example output:

				
					.application_name |   pid   |               query               | wait_event_class | wait_event_type |  wait_event  | samples |           lock_holder_txn            |        drop_waitstart         |       interpretation

------------------+---------+-----------------------------------+------------------+-----------------+--------------+---------+--------------------------------------+-------------------------------+-------------------------------
 DROP_INDEX       | 1937487 | DROP INDEX test_c2_idx;           | YSQLQuery        | Cpu             | OnCpu_Active |     198 | d2156b92-7a30-4e80-b7d6-6008fecc08ae | 2026-02-12 15:04:55.470879+00 | 2026-02-12 15:04:57.647031+00
 INSERT_WAITER    | 1937570 | INSERT INTO test SELECT 1, 'A';   | YSQLQuery        | Cpu             | OnCpu_Active |     196 | d2156b92-7a30-4e80-b7d6-6008fecc08ae | 2026-02-12 15:04:55.470879+00 | 2026-02-12 15:04:57.647031+00
 LOCK_HOLDER      | 1937471 | SELECT pg_sleep(60);              | YSQLQuery        | Timeout         | PgSleep      |      59 | d2156b92-7a30-4e80-b7d6-6008fecc08ae | 2026-02-12 15:04:55.470879+00 | 2026-02-12 15:04:57.647031+00

🔬 What This Proves

● The DDL session and DML session were both sampled repeatedly during the same time window.
● Neither statement completed while the ACCESS EXCLUSIVE lock was held.
● The dominant wait classification confirms sustained activity during the blocking period.
● This corroborates the pg_locks evidence that DDL and DML were waiting on the same table-level lock.

In short: ASH confirms the blocking wasn’t momentary… it was sustained and observable.

🧠 PostgreSQL vs YugabyteDB (Summary)

🧠 System / Mode	🔒 Non-Concurrent DDL Behavior (e.g., `DROP INDEX`)
PostgreSQL	DDL typically takes an `ACCESS EXCLUSIVE` lock and blocks concurrent DML (INSERT/UPDATE/DELETE) until it completes.
YugabyteDB (table-level locks disabled)	`ACCESS EXCLUSIVE` table locks are not supported, and DDL can run while concurrent DML continues (no Postgres-style table lock blocking).
YugabyteDB (table-level locks enabled – 2025.1.1+)	Postgres-style table lock semantics are enforced for this scenario: DDL waits on `AccessExclusiveLock` and blocks concurrent DML.

Note: The YugabyteDB outcomes depend on whether table-level locks are enabled.
Use pg_locks (and optionally ASH) to confirm what’s happening on your cluster.

🧩 Conclusion

Whether DDL blocks concurrent DML in YugabyteDB depends on whether table-level locks are enabled.

● With table-level locks disabled (default), DDL such as DROP INDEX does not block concurrent INSERT/UPDATE/DELETE operations.
● With table-level locks enabled (2025.1.1+), PostgreSQL-style semantics apply, and DDL can block DML via AccessExclusiveLock.

In PostgreSQL, the usual way to avoid blocking writes during an index drop is:

				
					DROP INDEX CONCURRENTLY ...

However, YugabyteDB does not currently support DROP INDEX CONCURRENTLY.

That means once table-level locks are enabled, there is no built-in “concurrent drop” escape hatch.

So the decision becomes operational:

● Want maximum concurrency during DDL? → Leave table-level locks disabled
● Want PostgreSQL lock semantics? → Enable table-level locks
● Need non-blocking index drops with locks enabled? → Plan operationally (maintenance window, traffic control)

The real takeaway is not simply “Does DDL block?”… it’s:

What concurrency model do you want your cluster to enforce?

Have Fun!