SQL Lock Archives - Page 8 of 11 - SQL Authority with Pinal Dave

A sparrow leaving a birdbath taken by a big pigeon, flying toward a puddle beside a red tulip

SET LOCK_TIMEOUT and Error 1222: Waiting Less for Locks

September 13, 2012

SQL Error Messages, SQL Lock, SQL Server, SQL Transactions

Set LOCK_TIMEOUT to bound lock waits, reproduce error 1222, clean up the transaction, and retry safely without confusing client timeouts.

Four ladders against separate apple trees in an orchard row, a full basket under each and one cart waiting at the end.

Loading Partitions in Parallel

March 26, 2012

Pinal Dave

SQL Tips and Tricks

ETL, Parallel, SQL Lock, SQL Server, Table Partitioning

Loading partitions in parallel uses separate staging tables, controlled workers, and partition switching to publish data without chaotic locks.

Two toy locomotives facing each other nose to nose on one wooden track, one red and one slate blue, neither able to pass.

Blocking and Deadlock Quiz: Which One Ends by Itself?

January 10, 2012

Pinal Dave

SQL Puzzle

Deadlock, SQL Error Messages, SQL Lock, SQL Transactions

This Blocking and Deadlock Quiz asks which problem SQL Server ends on its own, and how the losing session finds out. A two-window test on SQL Server 2025 shows the deadlock error 1205 and the lock timeout error 1222. It also shows how to find a blocker and how to retry.

A narrow wooden footbridge over a calm stream with a small red gate closing its entrance.

Locking and Blocking Quiz: Will Updating a Different Row Wait?

January 6, 2012

Pinal Dave

SQL Puzzle

SQL DMV, SQL Index, SQL Lock, SQL Performance

This Locking and Blocking Quiz asks whether an update waits when it changes a different row than an open transaction. The answer depends on the index. A two-window test on SQL Server 2025 shows the wait, the lock that causes it, and two ways to remove it.

A writing desk by a window with a half-written letter and a red fountain pen resting on it, an empty chair pulled up close.

ANSI Isolation Levels Quiz: Which Level Lets You Read Uncommitted Data?

January 2, 2012

Pinal Dave

SQL Puzzle

Snapshot, SQL Lock, SQL Transactions, Transaction Isolation

This ANSI Isolation Levels Quiz asks which level lets one session read a price that another session has not committed. A two-window test on SQL Server 2025 shows what each level returns. It also shows how READ COMMITTED SNAPSHOT removes the wait.

A hand checking the red wax seal on a jar of jam before opening it

Lost Updates: Optimistic Concurrency With a rowversion Column

October 18, 2011

Pinal Dave

SQL Tips and Tricks

SQL Lock, SQL Server, SQL Transactions, Transaction Isolation

Prevent lost updates with optimistic concurrency and a rowversion token, detect zero-row conflicts, and compare short locking transactions.

Timeline of one update that is rolled back after 12 seconds and five readers that start after 3 seconds. The default reader waits 9 seconds and sees 24.00. READUNCOMMITTED and NOLOCK see 240.00 at once. READPAST skips the locked row. NOWAIT fails with Msg 1222. With row versioning on, the default reader sees 24.00 at once

Dirty Records and Table Hints: Wait, Read Dirty, Skip or Fail

August 7, 2011

Pinal Dave

SQL Tips and Tricks

SQL Lock, SQL Scripts, SQL Transactions, Transaction Isolation

Dirty records and table hints decide what a SELECT does while another session has an uncommitted change. This post runs one update that rolls back and five readers against it: the default, READUNCOMMITTED, NOLOCK, READPAST and NOWAIT. It also shows how a database option changes the default.