SQL transaction locks query weird behavior

I was investigating some transaction locks, when I observed the following behavior. When I run this select statement, I don't get everytime the lock caused by it (Sch-S on sysschobjs table).

SELECT   l.request_session_id AS SessionID ,
         s.login_name AS UserName ,
         l.resource_type AS ResourceType ,
         l.request_mode AS LockMode ,
         l.request_status AS LockStatus ,
         l.resource_database_id AS DatabaseID ,
         DB_NAME (l.resource_database_id) AS DatabaseName ,
         l.resource_associated_entity_id AS ResourceID ,
         o.NAME AS TableName ,
         o.type_desc AS ObjectType ,
         CASE WHEN l.resource_type = 'OBJECT' THEN o.NAME
              WHEN l.resource_type = 'ROW' THEN 'ROW Lock'
              ELSE 'N/A'
         END AS ResourceName ,
         CASE WHEN l.resource_type = 'OBJECT' THEN o.type_desc
              ELSE 'N/A'
         END AS ObjectType
FROM     sys.dm_tran_locks l
         JOIN sys.objects o ON l.resource_associated_entity_id = o.OBJECT_ID
         JOIN sys.dm_exec_sessions s ON l.request_session_id = s.session_id

but when I'm adding the where clause, I get my lock every single time

WHERE l.request_session_id = @@SPID 

This happens with other similar queries, and I was wondering what can cause this behavior?

This is execution plan dependent.

Query 1

SELECT o.name,
       l.request_mode
FROM   sys.objects o
       INNER MERGE JOIN sys.dm_tran_locks l
               ON l.resource_associated_entity_id = o.OBJECT_ID
WHERE  l.request_session_id = @@SPID

This accesses sysschobjs first meaning that by the time it looks in SYSLOCKINFORMATION there is already a schema stability lock held by the session.

So this returns

name          request_mode
------------- ------------
sysschobjs    Sch-S

Query 2

SELECT o.name,
       l.request_mode
FROM   sys.dm_tran_locks l
       INNER LOOP JOIN sys.objects o
               ON l.resource_associated_entity_id = o.OBJECT_ID
WHERE  l.request_session_id = @@SPID;

has execution plan

where SYSLOCKINFORMATION is called and filtered for request_session_id = @@SPID and then any matching rows are looked up in sys.objects (base table sysschobjs).

The schema stability lock on sysschobjs is not acquired until the first matching row from sys.dm_tran_locks is emitted from the filter and the nested loops operator calls its inner input. When I run the above on my machine it returns no results.

Potentially if SYSLOCKINFORMATION was to return multiple matching rows (as run by a session already holding other locks) then by the time the second matching row is read it would have already acquired the SCH_S lock on sysschobjs. I'm unclear in this case if there is any possibility that you might then see it popping up with this execution plan too or if it takes any steps (such as materializing the initial result) that would avoid this. I didn't get this to happen with some extremely superficial testing.