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 ;