How to use application lock in SQL server from two databases

How to use a lock from two different databases. When a session acquires the lock, there is nothing preventing different session to acquire the same lock from different database.

First session:

USE db_one;
EXEC @lock_result = sp_getapplock @Resource = 'my_resource',
                                  @LockMode = 'Exclusive',
                                  @LockTimeout = 30000;

Second session:

USE db_two;
EXEC @lock_result = sp_getapplock @Resource = 'my_resource',
                                  @LockMode = 'Exclusive',
                                  @LockTimeout = 30000;

Second session acquires lock with no delay, instead of waiting for the lock. Now two sessions appear to have the same lock at the same time.

Consider database-qualifying the sp_getapplock proc name. This way, the lock will be acquired in the specified database instead of the current session database context.

EXEC @lock_result = tempdb..sp_getapplock @Resource = 'my_resource',
    LockMode = 'Exclusive',
    @LockTimeout = 30000;

StackExchange Database Administrators Q#295709, answer score: 9