Developer Forum

A few months ago i asked to figure out who is holding an application lock.

<quote>
EXECUTE sp_getapplock
@Resource = 'Edit~Ticket~{3259AA76-8F02-4361-B224-1422E57C6BAE}',
@LockMode = 'Exclusive',
@LockOwner = 'Session'

The command(s) completed successfully.


SELECT rsc_text, req_spid
FROM master.dbo.syslockinfo
WHERE rsc_type = 10 --10=Appliction

rsc_text req_spid
-------------------------------- -----------
Edita89cdb85 56

(1 row(s) affected)

</quote>

SQL Server mangles the lock string so that you cannot figure out who is
holding your resource (which is a relativly critical feature; every user one
of our users who has ever seen the "this thing is locked by someone else"
message wants to know who that someone is.)


As a momumental hack, i wrote a function that modifies your original lock
string. It:
a) takes your original locks string
b) runs it through SHA1
c) XORs it down into 3 bytes, then
d) base64 encodes the 3 bytes so that it's then four characters.

So the previous lock string of
'Edit~Ticket~{3259AA76-8F02-4361-B224-1422E57C6BAE}'
now becomes
'MLkyEdit~Ticket~{3259AA76-8F02-4361-B224-1422E57C6BAE}'

And the "MLky" prefix is your new 'unique' lock string, that you can later
use to find this specific lock. Running the query for application locks:

SELECT rsc_text, req_spid
FROM master.dbo.syslockinfo
WHERE rsc_type = 10 --10=Appliction

rsc_text req_spid
-------------- -----------
MLkyac613bb8 56

(1 row(s) affected)

If you want to find who has your thing locked, you can get the "tagged" form
of your original lock string, pick off the first four characters, and search
the syslockinfo table for it:

SELECT rsc_text, req_spid
FROM master.dbo.syslockinfo
WHERE rsc_type = 10 --10=Appliction
AND rsc_text LIKE 'MLky%'

rsc_text req_spid
-------------- -----------
MLkyac613bb8 56

(1 row(s) affected)


And now we have solved the original problem.

i don't know the math of it, but i'll say that the four letter base64
characters are guaranteed to be unique. :)

Patent Pending!

Just kidding.

Not really.

Or am i?