SQL Transactions Archives - Page 11 of 17 - SQL Authority with Pinal Dave

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

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 long wooden peg board with a row of round pegs and one empty hole in the row, a red peg standing just past the gap.

Identity Column Quiz: Why Did My IDs Skip a Number?

January 4, 2012

Pinal Dave

SQL Puzzle

SQL Constraint and Keys, SQL Identity, SQL Table Operation, SQL Transactions

This Identity Column Quiz starts with three rows and one rolled-back insert, then asks what ID the next row gets. The answer is 5, not 4. A tested script proves it and shows two more ways numbers go missing.

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.

An empty basket rack at a shop door, every basket out on the floor, one red basket unused

Diagnosing Connection Pool Exhaustion From the SQL Server Side

December 10, 2011

Pinal Dave

SQL Tips and Tricks

SQL Connection, SQL DMV, SQL Server, SQL Transactions

Diagnose connection pool exhaustion by grouping SQL Server sessions, finding sleeping open transactions, and reading their last command.

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.

A grid of two cases: when Plan A works the UPDATE runs, Plan A worked is printed and CATCH is skipped; when Plan A fails the UPDATE raises error 515, the rest of TRY is skipped and CATCH prints Plan B with error 515, severity 16, line 2

TRY CATCH Block in SQL Server: Plan A, Plan B and the Rollback

August 28, 2011

Pinal Dave

SQL Tips and Tricks

SQL Error Messages, SQL Scripts, SQL Server, SQL Transactions

A TRY CATCH block runs Plan A and falls back to Plan B when Plan A raises an error. This post shows that CATCH runs only after an error, that severity 11 is the line between a message and an error, and that one ROLLBACK undoes a nested transaction completely. Every result comes from SQL Server 2025.

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.