Deadlocks and optimistic concurrency

Treat conflicts as part of the design.

UnderstandStudy an examplePractise

Understand the concept

A deadlock is a cycle of sessions waiting for one another’s resources. SQL Server selects a victim and rolls its transaction back with error 1205. Consistent access order, short transactions and useful indexes can reduce risk without guaranteeing elimination. A deadlock graph identifies the participating resources and statements.

Retry requires idempotent or safely repeatable business logic, bounded attempts and backoff. rowversion is a change token, not a timestamp. An UPDATE matching both key and old version can detect changes made since a user read the row. Zero affected rows can mean a conflict or missing record; do not silently report success.

WHEN TO USE IT

Handle lost updates and safe retries.

COMMON PITFALL

Displaying rowversion as a date or retrying indefinitely.

01 / PRACTICE

One topic. Three levels of depth.

T-SQL / SQL Server 2019+

SELECT ERROR_NUMBER() AS ErrorOutsideCatch;

Returns NULL outside CATCH; error metadata has scope.

This page does not execute SQL. Practise in the downloadable SqlStudioLab using SSMS. Before rerunning temporary-table examples, remove only the relevant #table. Views, procedures and functions create persistent lab objects; never use a production connection.

02 / YOUR TURN

Try it yourself first.

Inspect the token data type in VersionDemo.

Show the solution and explanation

Worked solution

SELECT Id, Version, DATALENGTH(Version) AS TokenBytes FROM #VersionDemo;

Run the intermediate example first. The token is an eight-byte change marker.

RasimAtaberkŞahin

Understand the concept

One topic. Three levels of depth.

Try it yourself first.