Locking and blocking

Waiting and deadlock are different.

UnderstandStudy an examplePractise

Understand the concept

Locks coordinate concurrent access using compatibility rules for shared, update and exclusive modes. Blocking means one session waits for another’s lock; brief waits can be normal. Long transactions, broad updates or poor access paths can increase waiting.

Locks and latches differ: latches protect internal memory structures. NOLOCK does not mean no locks; it permits dirty, missing or duplicate reads and can still wait on schema locks. Do not sacrifice financial correctness for speed. Investigate blockers, transaction duration, statements and plans together. Assess impact and rollback cost before terminating any session.

WHEN TO USE IT

Balance correctness and latency under concurrency.

COMMON PITFALL

Adding NOLOCK to every query.

01 / PRACTICE

One topic. Three levels of depth.

T-SQL / SQL Server 2019+

SELECT @@SPID AS SessionId;

Inspect your own session identifier.

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 your connection’s open transaction count.

Show the solution and explanation

Worked solution

SELECT @@SPID AS SessionId, @@TRANCOUNT AS OpenTransactions;

After completed examples, OpenTransactions should be zero.

RasimAtaberkŞahin

Understand the concept

One topic. Three levels of depth.

Try it yourself first.