How to serialize on SQL server metadata?

Posted on

Question :

I tried to find an answer in Kalen Delaney’s SQL Server Concurrency but there is no mention of metadata.

The problem is how to ensure that only one of these reads and index creates executes from one connection before the other connection does the same .

if not exists (select * from sysindexes with (XLOCK) where name = 'IND_audittrail_date_time') 
CREATE CLUSTERED INDEX IND_audittrail_date_time ON audittrail(date_time)

— because we get

java.sql.SQLException: The operation failed because an index or
statistics with name ‘IND_audittrail_date_time’ already exists on
table ‘audittrail’.

Query:

if not exists (select * from sysindexes with (XLOCK) where name = 'IND_audittrail_date_time') 

Reading Metadata Access, Isolation Levels, and Lock Hints
I found this

SQL Server does not guarantee that lock hints will be honored in
queries that access metadata through catalog views, compatibility
views, information schema views, metadata-emitting built-in functions

Is there an accepted way how this is done? Thank you.

Answer :

In this case, if you cant serialize this in the application code you can use try/catch or an applock

begin tran
if APPLOCK_TEST ('public','idx_create','exclusive') = 1
 begin 
  EXEC sp_getapplock @Resource = 'idx_create', 
                     @lockmode = 'Exclusive', 
                     @DbPrincipal='public'
   print 'I can do it'

  -- EXEC sp_releaseapplock @Resource='idx_create', @DbPrincipal='public'
  -- scope is transaction see comment below
   commit 
 end
else rollback ;

Leave a Reply

Your email address will not be published. Required fields are marked *