Archive for April, 2015

how to test if a temp locking table is locked or not

April 30, 2015

sorry, minimal explanation. you’ll figure it out.
What’s the purpose? to prevent multiple updates from multiple servers in our webfarm.

BEGIN TRY

SET XACT_ABORT ON;
SET LOCK_TIMEOUT 100;

if OBJECT_ID('tempdb..##MyTempLockingTable') is not null
BEGIN
select 1 as [IsLocked]
end
else
begin
select -1 as [IsLocked]
end

end try
begin catch
-- when we test this with a second id, we find that the above object_id statement hangs
select 1 as [IsLocked]
end catch