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

Question

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

Russell Services 0

Subject: SQL Server 2022 — How can SHARED_TRANSACTION_WORKSPACE lock ownership be independently verified?

We're validating an isolated synthetic database with a multi-session concurrency test.

SQL Server reports a granted database shared lock with these properties:

resource_type: DATABASE

request_mode: S

request_status: GRANTED

request_owner_type: SHARED_TRANSACTION_WORKSPACE

request_owner_id: 0

Our safety requirement is to independently establish that the lock belongs to an authorized transaction, rather than assuming that a recognized session owns it.

We have not established an unambiguous mapping through the documented transaction DMVs. Microsoft's documentation also identifies request_owner_lockspace_id as unsupported.

Question: Is there a documented, supported SQL Server 2022 mechanism for independently attributing this workspace lock to a specific transaction?

If not, is there a supported alternative for verifying transaction ownership and isolation without weakening the test's containment guarantees?

Please provide documentation or explain the applicable SQL Server limitations.

We are not requesting a way to bypass the ownership checks.

0 comments

3 answers

Your answer

Answer 1

Following up on your comment under Erland's answer about the QUERY STORE ASYNC FLUSH locks: yes, those locks belong to SQL Server itself. I reproduced them on SQL Server 2022 (16.0.4305.5, Developer edition in Docker). I created a database with Query Store on, ran a small insert workload, and polled sys.dm_tran_locks every 50 ms.

What I saw while Query Store flushed its data

Sessions with the command QUERY STORE ASYNC FLUSH (and in other polls, BRKR TASK) held a DATABASE S lock with request_owner_type = SHARED_TRANSACTION_WORKSPACE and request_owner_id = 0, exactly like the lock in your original question.
The same sessions also held TRANSACTION-owned locks: DATABASE (subtype QDS), METADATA (QDS_STATEMENT_STABILITY, QDS_QUERY_TEXT_STABILITY), OBJECT, PAGE, KEY and RID.
When one of those transaction IDs could be found in sys.dm_tran_active_transactions, it was named QDS base transaction or QDS nested transaction.
None of these transactions ever appeared in sys.dm_tran_session_transactions. That's why you can't map them through the session transaction DMVs: they aren't user transactions bound to a session.
All of these sessions had is_user_process = 0 in sys.dm_exec_sessions. Their login_name was sa, so the login doesn't tell them apart.
CHECKPOINT and TASK MANAGER background sessions also briefly held locks in the database, including the same SHARED_TRANSACTION_WORKSPACE lock.
The session IDs of these background tasks varied (2, 5, 36, 57, 76), so the old rule "session_id 50 or lower is system" doesn't hold.

For comparison, a user session running BEGIN TRAN; UPDATE ... showed the same session-level SHARED_TRANSACTION_WORKSPACE lock (owner ID 0, as Erland explained). Its TRANSACTION locks had a request_owner_id equal to CURRENT_TRANSACTION_ID() and to the transaction in sys.dm_tran_session_transactions.

A verification approach that separates the two

The supported, documented discriminator is sys.dm_exec_sessions.is_user_process, which is 0 for system sessions. The check that worked for me:

is_user_process = 0: SQL Server background task. Don't treat it as an unauthorized transaction.
is_user_process = 1: the session must be on your allowlist. Every TRANSACTION-owned lock must have a request_owner_id that one of your test sessions registered with CURRENT_TRANSACTION_ID() and that appears in sys.dm_tran_session_transactions for that session.
SHARED_TRANSACTION_WORKSPACE locks: attribute them to the session, not a transaction.

Here's the query I used (replace SynthDb with your database name):Following up on your comment under Erland's answer about the QUERY STORE ASYNC FLUSH locks: yes, those locks belong to SQL Server itself. I reproduced them on SQL Server 2022 (16.0.4305.5, Developer edition in Docker). I created a database with Query Store on, ran a small insert workload, and polled sys.dm_tran_locks every 50 ms.

What I saw while Query Store flushed its data

Sessions with the command QUERY STORE ASYNC FLUSH (and in other polls, BRKR TASK) held a DATABASE S lock with request_owner_type = SHARED_TRANSACTION_WORKSPACE and request_owner_id = 0, exactly like the lock in your original question.
The same sessions also held TRANSACTION-owned locks: DATABASE (subtype QDS), METADATA (QDS_STATEMENT_STABILITY, QDS_QUERY_TEXT_STABILITY), OBJECT, PAGE, KEY and RID.
When one of those transaction IDs could be found in sys.dm_tran_active_transactions, it was named QDS base transaction or QDS nested transaction.
None of these transactions ever appeared in sys.dm_tran_session_transactions. That's why you can't map them through the session transaction DMVs: they aren't user transactions bound to a session.
All of these sessions had is_user_process = 0 in sys.dm_exec_sessions. Their login_name was sa, so the login doesn't tell them apart.
CHECKPOINT and TASK MANAGER background sessions also briefly held locks in the database, including the same SHARED_TRANSACTION_WORKSPACE lock.
The session IDs of these background tasks varied (2, 5, 36, 57, 76), so the old rule "session_id 50 or lower is system" doesn't hold.

For comparison, a user session running BEGIN TRAN; UPDATE ... showed the same session-level SHARED_TRANSACTION_WORKSPACE lock (owner ID 0, as Erland explained). Its TRANSACTION locks had a request_owner_id equal to CURRENT_TRANSACTION_ID() and to the transaction in sys.dm_tran_session_transactions.

A verification approach that separates the two

The supported, documented discriminator is sys.dm_exec_sessions.is_user_process, which is 0 for system sessions. The check that worked for me:

is_user_process = 0: SQL Server background task. Don't treat it as an unauthorized transaction.
is_user_process = 1: the session must be on your allowlist. Every TRANSACTION-owned lock must have a request_owner_id that one of your test sessions registered with CURRENT_TRANSACTION_ID() and that appears in sys.dm_tran_session_transactions for that session.
SHARED_TRANSACTION_WORKSPACE locks: attribute them to the session, not a transaction.

Here's the query I used (replace SynthDb with your database name):

SELECT l.request_session_id,
       s.is_user_process,
       r.command,
       l.resource_type,
       l.request_mode,
       l.request_owner_type,
       l.request_owner_id,
       at.name AS transaction_name,
       st.transaction_id AS session_transaction_id,
       CASE
           WHEN s.is_user_process = 0 THEN 'SQL Server background task'
           WHEN l.request_owner_type = 'TRANSACTION' AND st.transaction_id IS NULL THEN 'CHECK: user lock with unknown transaction'
           ELSE 'User session: check session/transaction against allowlist'
       END AS classification
FROM sys.dm_tran_locks AS l
JOIN sys.dm_exec_sessions AS s ON s.session_id = l.request_session_id
LEFT JOIN sys.dm_exec_requests AS r ON r.session_id = l.request_session_id
LEFT JOIN sys.dm_tran_active_transactions AS at
       ON l.request_owner_type = 'TRANSACTION' AND at.transaction_id = l.request_owner_id
LEFT JOIN sys.dm_tran_session_transactions AS st
       ON st.session_id = l.request_session_id AND st.transaction_id = l.request_owner_id
WHERE l.resource_database_id = DB_ID(N'SynthDb');

During a flush, this showed the QDS nested transaction locks as "SQL Server background task" and my test session as "User session".

Don't rely on command or the QDS transaction names. They're useful for logging, but they're descriptive values, not a documented contract, and I saw the same Query Store work under two different command names.

If you'd rather not see these locks at all

Query Store is on by default for new databases in SQL Server 2022, and that includes LocalDB. If your synthetic test database doesn't need it, turning it off removes this source of background locks:

ALTER DATABASE SynthDb SET QUERY_STORE = OFF;

Other background tasks such as checkpoint can still take short locks, so keep the is_user_process rule either way.

Let me know how it goes.

Russell Services 0 Reputation points

2026-10-30T15:14:06.8766667+00:00

Thank you, Erland, and thanks to Paul as well.

My objective is to validate a controlled concurrency test runner against an isolated, synthetic SQL Server 2022 database.

Before and during testing, the runner must reject unexpected user sessions and transactions affecting that database, while allowing legitimate SQL Server background activity. We already register the test sessions and their transaction IDs.

The problem was that Query Store background locks could not be mapped to our registered transactions, causing our isolation safeguard to stop the tests.

Based on your explanation and Paul's reproduction, it seems appropriate to classify sessions with is_user_process = 0 as internal SQL Server activity, while continuing to require that all is_user_process = 1 sessions and their transaction-owned locks match our registered test sessions and transactions.

We would still reject missing or ambiguous session information and any unexplained user transactions.

Does that approach sound correct for the isolation objective described above?

Thank you for your help.
Erland Sommarskog 138.6K Reputation points MVP Volunteer Moderator

2026-10-30T15:53:52.8166667+00:00

Is this intended to be some sort of performance test, and you want to ensure that there is no activity that could distort you numbers? In that case, you could consider disabling Query Store in all databases to avoid that QS background task causing noise for your tests. Then again, that also mean that you cannot use Query Store to analyse your tests.

Where are you currently looking to find this unwanted activity?
Russell Services 0 Reputation points

2026-10-30T16:37:57.9066667+00:00

Thank you, Erland.

No, this is not a performance test. We're not measuring execution times or trying to eliminate background activity that might distort performance results.

We're running functional concurrency tests against an isolated, synthetic SQL Server 2022 database to verify that simultaneous application operations behave correctly.

Our test runner has a safety check intended to ensure that only authorized test sessions and transactions are involved.

The check examines sys.dm_tran_locks for the specific test database and attempts to correlate observed locks with registered test sessions and transactions using SQL Server's session and transaction DMVs.

The difficulty arose when SQL Server's Query Store background activity produced locks that couldn't be attributed to our registered user transactions. Our safeguard stopped the test rather than assume those locks were safe.

Our goal is to distinguish legitimate SQL Server background activity from unexpected user sessions or transactions, without weakening the protection against genuinely unauthorized activity.

Based on your explanation and Paul's findings, it appears that sys.dm_exec_sessions.is_user_process may provide the distinction we need.

Would you agree that recognizing is_user_process = 0 as SQL Server system activity, while continuing to verify all user sessions and transactions against our registered test sessions, is appropriate for this purpose?

I'd prefer not to disable Query Store merely to avoid encountering these locks.

Thank you again for your assistance.
Erland Sommarskog 138.6K Reputation points MVP Volunteer Moderator

2026-10-30T17:21:46.71+00:00

Thanks for the clarification! Yes, is_user_process is something you need to look at.

Although, if memory serves, there is one case when is_user_process is = 0 would be of a concern to you, and that is Service Broker activation. But I guess that since you have set up this database yourself, you know that Service Broker is not in use.

But I'm wondering whether sys.dm_tran_locks is good enough for you want to do. You may look there and find nothing. But one millisecond after you've looked, and unauthorised process may start a transaction. Inspecting sys.dm_exec_sessions for processes with the synthetic database as the current database would be better, I think. (But still not waterproof.)
Russell Services 0 Reputation points

2026-10-30T20:08:25.24+00:00

Thank you, Erland. That distinction is helpful.

We'll check whether Service Broker activation is in use rather than assume it isn't.

Our testing is confined to a dedicated synthetic database. The current verification stops on two Query Store-related locks because we cannot establish whether they belong to SQL Server's internal activity or another process.

I understand your point that checking sys.dm_exec_sessions is more informative than looking only at locks, but neither provides a guarantee against another connection appearing immediately afterward.

Is there a SQL Server-supported way to ensure that no other user processes can interfere throughout the test, while allowing SQL Server's legitimate background activity?

Or is that level of isolation something we cannot reliably establish through SQL Server's session and lock information alone?

Thanks again for your guidance.
Erland Sommarskog 138.6K Reputation points MVP Volunteer Moderator

2026-10-30T20:12:56.0833333+00:00

Maybe. What are the presumptions? Can we assume that you have the instance to yourself? Or must you permit for activity in other databases?
Erland Sommarskog 138.6K Reputation points MVP Volunteer Moderator

2026-10-31T09:52:49.74+00:00

Just to clarify: There are more than one way to achieve this, but about all come with some requirements that may not work for you.

The easiest is probably if you can run the application and SQL Server on the same machine. Then you can disable remote connections, so that only processes on the machine can connect. Hopefully in that case, you can feel certain that no unauthorised user is there.

Answer 2

Erland Sommarskog 138.6K MVP Volunteer Moderator

Is there a documented, supported SQL Server 2022 mechanism for independently attributing this workspace lock to a specific transaction?

No, because this lock is not related to any transaction at all, but it is a session-level lock. When a process accesses a database, it needs a lock on the database to report that it is there. This serves to block other processes that may want to perform actions that require exclusive access to the database, for instance drop the database or change the setting for READ_COMMITTED_SNAPSHOT.

Russell Services 0 Reputation points

2026-10-29T23:23:59.1633333+00:00

Thank you, Erland. Your explanation about session-level database locks helped us correct our original verification rule.

We've now encountered a related issue with SQL Server 2022 LocalDB 16.0.1000.6.

Our isolated concurrency test passed 16 assertions before stopping on two locks associated with QUERY STORE ASYNC FLUSH.

We've captured lock, session, request, and transaction metadata. The sessions appear to be internal SQL Server activity, but we cannot independently establish ownership of the associated transactions.

Could you clarify whether these locks are normally owned by SQL Server's internal processes and whether there is a supported method to distinguish them from unauthorized user transactions?

If independent ownership attribution is unavailable, what verification approach would you recommend?

We want to preserve our safety checks without incorrectly rejecting legitimate SQL Server background activity.

Thank you again for your assistance.
Erland Sommarskog 138.6K Reputation points MVP Volunteer Moderator

2026-10-30T07:58:52.9866667+00:00

It is not clear to me what your ultimate aim is, but as Paul says, sys.dm_exec_sessions.is_user_process is an important discriminator. Only those with is_user_process = 1 can be an "unauthorized" user transactions. (I'm using quotes here, since I'm in doubt what you are actually looking for.)

Answer 3

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:

Create a trace before any test session starts.
Select Locks → Lock:Acquired and Locks → Lock:Released.
Show at least OwnerID, TransactionID, SPID, RequestID, DatabaseID, Type, Mode, EventSequence, LoginName, and SessionLoginName.
Filter DatabaseID to the synthetic database.
Save to a trace file rather than relying only on the live display.
Reject the test run if capture started late, stopped early, or did not contain the expected acquisition and release records.
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:

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^.

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^.

Fail closed unless every active user transaction and enlisted session belongs to the harness’s allowlist.
Ensure no unregistered connection can access the isolated database during the test.
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