SQL Server 2022 — How can SHARED_TRANSACTION_WORKSPACE lock ownership be independently verified? We're valida

There is no documented, supported snapshot join that maps a SHARED_TRANSACTION_WORKSPACE row in sys.dm_tran_locks to one specific transaction; exact attribution must instead be captured prospectively when the lock is acquired, or containment must be proven independently at the transaction/session boundary.

Why the live DMV row cannot provide that proof

For request_owner_type = TRANSACTION, request_owner_id contains the transaction ID. That contract is not documented for SHARED_TRANSACTION_WORKSPACE; workspaces are internal structures used to hold locks for enlisted sessions. In addition:

• request_owner_lockspace_id is informational, unsupported, and not guaranteed for future compatibility.
• request_session_id identifies the session currently owning the request, but can change for bound and distributed transactions.
• sys.dm_tran_locks exposes mutable live lock-manager state and contains no history.
• lock_owner_address is documented only for correlation with sys.dm_os_waiting_tasks.resource_address, not as a transaction identifier.

Therefore, request_owner_id = 0, the session ID, lockspace ID, or internal memory address cannot be used as a supported independent workspace-to-transaction identity chain ^1^.

Exact prospective attribution

The documented Lock:Acquired SQL Trace event records these values in the same acquisition event:

• OwnerID = 4 for SHARED_TRANSACTION_WORKSPACE
• TransactionID
• SPID and RequestID
• DatabaseID, resource Type, lock Mode, login identity, and event sequence

This can establish the transaction ID associated with the workspace lock at acquisition time, provided capture starts before the test and the required event is successfully retained ^2^.

In SQL Server Profiler:

1. Create a trace before any test session starts.
2. Select Locks → Lock:Acquired and Locks → Lock:Released.
3. Show at least OwnerID, TransactionID, SPID, RequestID, DatabaseID, Type, Mode, EventSequence, LoginName, and SessionLoginName.
4. Filter DatabaseID to the synthetic database.
5. Save to a trace file rather than relying only on the live display.
6. Reject the test run if capture started late, stopped early, or did not contain the expected acquisition and release records.
7. Match OwnerID = 4, Type = 2 (DATABASE), and Mode = 3 (S), then compare its TransactionID with the transaction IDs registered by the test harness.

SQL Trace and Profiler are deprecated but remain documented for SQL Server; Microsoft recommends Extended Events for new monitoring ^3^.

Do not assume that sqlserver.lock_acquired exposes the SQL Trace columns under identical names. Before substituting Extended Events, inspect the installed SQL Server 2022 event metadata:

SELECT
    o.name AS event_name,
    c.name AS column_name,
    c.column_type,
    c.type_name,
    c.description
FROM sys.dm_xe_objects AS o
JOIN sys.dm_xe_object_columns AS c
  ON c.object_name = o.name
 AND c.object_package_guid = o.package_guid
WHERE o.object_type = N'event'
  AND o.name IN (N'lock_acquired', N'lock_released')
ORDER BY o.name, c.column_type, c.name;

Only use Extended Events for the ownership assertion if this metadata demonstrates that the installed events expose the owner category, transaction ID, resource identity, and lock mode needed by the test. Extended Events supports event-file targets and is the recommended tracing platform, but the available event payload must be verified rather than inferred from SQL Trace ^4^.

Supported containment verification without lock attribution

If prospective lock-event capture is not acceptable, structure the test so authorization is established independently of that workspace row:

1. At transaction start, have each authorized connection register its transaction ID:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;

SELECT
    @@SPID AS session_id,
    CURRENT_TRANSACTION_ID() AS transaction_id,
    @@TRANCOUNT AS transaction_count;

CURRENT_TRANSACTION_ID() returns the current session’s transaction ID ^5^.

2. From the monitoring connection, enumerate all enlisted sessions and transactions:

SELECT
    st.session_id,
    st.transaction_id,
    st.is_user_transaction,
    st.is_local,
    st.is_enlisted,
    st.is_bound,
    st.open_transaction_count,
    es.transaction_isolation_level
FROM sys.dm_tran_session_transactions AS st
JOIN sys.dm_exec_sessions AS es
  ON es.session_id = st.session_id;

The transaction DMV explicitly supports one transaction under multiple sessions and multiple transactions under one MARS session ^6^.

3. Fail closed unless every active user transaction and enlisted session belongs to the harness’s allowlist.
4. Ensure no unregistered connection can access the isolated database during the test.
5. Validate isolation behavior using controlled conflicting reads/writes, not only sys.dm_exec_sessions.transaction_isolation_level. That column reports the session setting; statement-level hints can alter individual operations ^7^.

This proves the authorized transaction set and containment conditions, but it does not prove that the particular live workspace-lock row belongs to one member of that set.

For SQL Server 2022, querying the server-wide DMVs requires VIEW SERVER PERFORMANCE STATE. Creating or altering a server Extended Events session requires the corresponding CREATE ANY EVENT SESSION or ALTER ANY EVENT SESSION permission ^8^.

References

1. sys.dm_tran_locks (Transact-SQL)
2. Lock:Acquired Event Class
3. SQL Trace
4. Quickstart: Extended Events
5. CURRENT_TRANSACTION_ID (Transact-SQL)
6. sys.dm_tran_session_transactions (Transact-SQL)
7. Snapshot isolation in SQL Server
8. Extended Events overview