Microsoft SQL Server — Concurrency Control Factors Which of the following is NOT a determinant of how SQL Server performs concurrency control and locking during reads and writes?

Introduction:
Concurrency control in Microsoft SQL Server governs how multiple sessions safely read and modify the same data at the same time. This question checks whether you can distinguish true engine-level factors that shape locking and blocking from settings that either apply to narrower features or influence different parts of the system.

Given Data / Assumptions:

• SQL Server uses locks, latches, and row versioning depending on isolation level and options.
• Applications may use cursors with specific concurrency settings.
• Developers can add locking hints to override the optimizer’s default choices.
• Deadlock priority influences victim selection, not core lock acquisition rules.

Concept / Approach:
Identify which items directly configure or override the engine’s concurrency behavior (isolation level, hints, cursor concurrency) versus items that are either consequences of the optimizer or unrelated policy levers. The phrase “lock level” is not a user-configurable concurrency policy; SQL Server chooses lock granularity automatically (with optional hints), so “lock level” itself is not the determining factor.

Step-by-Step Solution:
1) Transaction isolation level (e.g., READ COMMITTED, SNAPSHOT) dictates read/write rules, phantom visibility, and whether row versioning is used.2) Locking hints like WITH (NOLOCK), HOLDLOCK, UPDLOCK can override default behavior on a statement, directly affecting concurrency.3) Cursor concurrency options (e.g., READ_ONLY, SCROLL_LOCKS, OPTIMISTIC) define how cursor reads interact with locks and versions.4) “Lock level” is not a configuration switch; lock granularity (row, page, table) is chosen by the engine’s lock manager and can be nudged by hints, but “lock level” itself is not a standalone determining factor.5) Deadlock priority affects who gets chosen as a victim after a deadlock is detected, not general lock acquisition policy.

Verification / Alternative check:
Inspect SQL Server documentation: concurrency behavior is specified under isolation levels, hints, and cursor options. No knob called “lock level” exists as a general policy control.

Why Other Options Are Wrong:

• Transaction isolation level: Directly defines concurrency semantics.
• Cursor concurrency setting: Controls how cursors lock or version rows.
• Locking hints: Explicitly override default concurrency behavior per statement.
• Deadlock priority setting: Influences victim choice, not base concurrency policy.

Common Pitfalls:
Confusing engine-selected lock granularity with a user-facing “lock level” setting, and assuming deadlock priority changes isolation semantics.

Final Answer:
Lock level