New answers tagged locking
some (most) X locks are recorded in the log, see How to read and interpret the SQL Server log. You can see what xact acquired them and how long the xact lasted (see the link). You won't find the conflicts, but you can make some guess.
My recommendation would be to do a best effort try/catch. Handle the duplicate case explicitly, as appropriate, eg. ignore it... The real question: why is DDL running on-demand, from multiple xacts? Normally upgrade and migration are a serious matter, handled in dedicated time windows... You do not want your migration (code-first?) to kick in unexpectedly, ...
You best bet is to use an explicit containing transaction and acquire a custom exclusive lock to protect the whole operation (SELECT and CREATE TABLE) using sp_getapplock. System objects do not honor isolation level requests and use locks in the same way as user tables, by design. The race condition in the original code is that multiple threads can conclude ...
Top 50 recent answers are included