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

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

Advertisements

Tags:

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: