Performance Monitoring and Tuning · Topic 7 of 16
DML Locking, Concurrency and Transaction Monitoring
Understand Snowflake DML locking and concurrency, apply best practices for UPDATE, DELETE, MERGE and INSERT, and monitor or abort transactions.
- 4 min
- Only READ COMMITTED isolation exists; table locks are held until commit or rollback.
UPDATE,DELETEandMERGEblock each other on the same table;INSERTandCOPYgenerally do not.- Hybrid tables lock rows, not tables.
What it is
Snowflake uses transactions to keep data consistent, and locks to stop two transactions from changing the same table in conflicting ways. Reads never wait for writes, but some writes wait for other writes.
The only table isolation level supported is READ COMMITTED. A statement sees data committed before it started, plus uncommitted changes made earlier in its own transaction. The exam asks which statements block each other, why a statement is waiting, and how to find and stop the blocker.
How it works
DML concurrency considerations
On standard tables, locks are taken at table level for statements that modify existing rows.
| Statement A | Statement B | Result |
|---|---|---|
UPDATE | UPDATE on the same table | B waits for A |
DELETE or MERGE | UPDATE, DELETE or MERGE on the same table | They block one another |
INSERT or COPY INTO | INSERT or COPY INTO | Generally run in parallel |
| Any write | SELECT | No blocking; reader sees the last committed data |
| Any DML | DML on a different table | No conflict |
Hybrid tables work differently: they lock individual rows, so changes to different rows do not block each other.
Other facts to know:
AUTOCOMMITis on by default, so a single statement outside an explicit transaction commits by itself.- A DDL statement implicitly commits any open transaction and cannot be rolled back.
- Transactions cannot be nested.
- If a DML statement fails inside a transaction, only that statement is undone and the transaction stays open until committed or rolled back. The parameter
TRANSACTION_ABORT_ON_ERROR(defaultFALSE) changes this when set toTRUE. - Deadlocks can happen with explicit multi statement transactions. Snowflake chooses the most recent deadlocked statement as the victim and rolls it back.
LOCK_TIMEOUTsets how long a statement waits for a lock, default 43200 seconds (12 hours). A statement that exceeds it fails.
Best practices for DML locking and concurrency
- Prefer
INSERTandCOPYfor loading. They do not block each other, so many loaders can write to one table at the same time. - Batch changes. One
MERGEorUPDATEthat handles many rows beats thousands of small ones, because each small statement takes the table lock and rewrites micro partitions. - Keep explicit transactions short. Commit as soon as the work is done; never leave a transaction open waiting for a person.
- Serialize or schedule conflicting jobs. Run competing
MERGEjobs on the same table one after another with tasks, rather than in parallel. - Set
LOCK_TIMEOUTlow for interactive work so users get a quick error instead of a long wait. - Split very contended data across tables, or use hybrid tables for row level, transactional workloads.
- Use more warehouses for read concurrency, but remember that locks belong to the table, so a second warehouse does not help two conflicting writers.
When two ETL jobs run MERGE into the same table and one is always waiting, the answer is to schedule them in sequence or batch their changes, not to enlarge the warehouse.
Monitor and manage transaction activity
| Need | Tool |
|---|---|
| List open transactions | SHOW TRANSACTIONS (with IN ACCOUNT for all users, as an administrator) |
| List held and waiting locks | SHOW LOCKS (with IN ACCOUNT) |
| Details of one transaction | DESCRIBE TRANSACTION <id> |
| Current transaction ID | CURRENT_TRANSACTION() |
| Stop a transaction | SELECT SYSTEM$ABORT_TRANSACTION(<id>) |
| History of blocked statements | SNOWFLAKE.ACCOUNT_USAGE.LOCK_WAIT_HISTORY and QUERY_HISTORY |
SHOW TRANSACTIONS IN ACCOUNT;
SHOW LOCKS IN ACCOUNT;
SELECT SYSTEM$ABORT_TRANSACTION(1721234567890);
Snowflake also cleans up on its own. A transaction that blocks others and has been idle for 5 minutes is aborted automatically, and a non blocking transaction that has run longer than 4 hours is aborted. For queries stuck in the queue, the related STATEMENT_QUEUED_TIMEOUT_IN_SECONDS parameter applies instead of a lock timeout.
Common mistakes
Assuming reads block on writes. Under READ COMMITTED a SELECT sees the last committed data and does not wait.
Thinking INSERT jobs queue behind each other. Most inserts and COPY loads run in parallel.
Using a bigger warehouse to fix write conflicts. The lock is on the table, not on compute.
Leaving an explicit BEGIN open. It holds locks until commit, rollback or the automatic abort.
What to remember for the exam
- Only READ COMMITTED isolation exists; table locks are held until commit or rollback.
UPDATE,DELETEandMERGEblock each other on the same table;INSERTandCOPYgenerally do not.- Hybrid tables lock rows, not tables.
LOCK_TIMEOUTdefault is 43200 seconds.SHOW TRANSACTIONS,SHOW LOCKSandSYSTEM$ABORT_TRANSACTIONfind and clear blockers;LOCK_WAIT_HISTORYis the history view.
Check yourself
Two jobs run
UPDATEon the same table at once. What happens?The second waits until the first commits or rolls back, or until
LOCK_TIMEOUT.Which command ends a stuck transaction?
SYSTEM$ABORT_TRANSACTIONwith the transaction ID.Why does a larger warehouse not fix two conflicting writers?
The lock is held on the table, so compute size does not change who must wait.
Topic 7 of 16 in Performance Monitoring and Tuning
Test yourself on Performance Monitoring and Tuning
The full Snowflake ADA-C02 question bank has 814 questions, with timed practice exams and an explanation for every answer.
This study guide is independent and is not affiliated with, endorsed by, or sponsored by Snowflake. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.