Why should you care?

Without locks, two cashiers could sell the last item twice. With too many locks, the app feels frozen.

See it live — copy this example

Run in SQL Server Management Studio (SSMS) or Azure Data Studio.

USE DataVerse;
BEGIN TRAN;
UPDATE dbo.Accounts WITH (ROWLOCK, UPDLOCK)
SET Balance = Balance - 10
WHERE AccountId = 1;
-- Holds locks until COMMIT/ROLLBACK
SELECT resource_type, request_mode, request_status
FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID;
COMMIT;

What happened?

• UPDLOCK/ROWLOCK takes update locks on the row early.
• dm_tran_locks shows what your session holds before commit.

Practice next

1. Run the BEGIN TRAN update without committing.
2. Query dm_tran_locks from another window for that session_id.
3. COMMIT and watch locks clear.
4. Try READCOMMITTEDLOCK hints vs default.
5. Inspect blocking_session_id in dm_exec_requests.

Remember

Locks guard concurrent access. Exclusive writes block other writers. Keep transactions short.

Row locks on accounts

DataVerse transfer takes UPDLOCK on account rows.

Outcome: Two transfers cannot interleave balances incorrectly.