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
In a nutshell
  • Only READ COMMITTED isolation exists; table locks are held until commit or rollback.
  • UPDATE, DELETE and MERGE block each other on the same table; INSERT and COPY generally 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.

Statement AUPDATE on tableholdsTable lockuntil commit or rollbackblocksStatement BUPDATE on same tablewaits up toLOCK_TIMEOUTthen fails

How it works

DML concurrency considerations

On standard tables, locks are taken at table level for statements that modify existing rows.

Statement AStatement BResult
UPDATEUPDATE on the same tableB waits for A
DELETE or MERGEUPDATE, DELETE or MERGE on the same tableThey block one another
INSERT or COPY INTOINSERT or COPY INTOGenerally run in parallel
Any writeSELECTNo blocking; reader sees the last committed data
Any DMLDML on a different tableNo conflict

Hybrid tables work differently: they lock individual rows, so changes to different rows do not block each other.

Other facts to know:

  • AUTOCOMMIT is 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 (default FALSE) changes this when set to TRUE.
  • Deadlocks can happen with explicit multi statement transactions. Snowflake chooses the most recent deadlocked statement as the victim and rolls it back.
  • LOCK_TIMEOUT sets 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 INSERT and COPY for loading. They do not block each other, so many loaders can write to one table at the same time.
  • Batch changes. One MERGE or UPDATE that 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 MERGE jobs on the same table one after another with tasks, rather than in parallel.
  • Set LOCK_TIMEOUT low 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.
Exam tip

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

NeedTool
List open transactionsSHOW TRANSACTIONS (with IN ACCOUNT for all users, as an administrator)
List held and waiting locksSHOW LOCKS (with IN ACCOUNT)
Details of one transactionDESCRIBE TRANSACTION <id>
Current transaction IDCURRENT_TRANSACTION()
Stop a transactionSELECT SYSTEM$ABORT_TRANSACTION(<id>)
History of blocked statementsSNOWFLAKE.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.

Users report waitsblocked statementsSHOW LOCKSFind blockertransaction idDESCRIBE TRANSACTIONDecidewait or abortSYSTEM$ABORT_TRANSACTIONLock released

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, DELETE and MERGE block each other on the same table; INSERT and COPY generally do not.
  • Hybrid tables lock rows, not tables.
  • LOCK_TIMEOUT default is 43200 seconds.
  • SHOW TRANSACTIONS, SHOW LOCKS and SYSTEM$ABORT_TRANSACTION find and clear blockers; LOCK_WAIT_HISTORY is the history view.

Check yourself

  • Two jobs run UPDATE on 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_TRANSACTION with 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.