1

I experienced a deadlock when I have a thread that DROPs a table and another thread that SELECTs INFORMATION_SCHEMA.KEY_COLUMN_USAGE.

I'd like to know if any of you encountered something similar. I've checked all over the internet and found some mentions of this but nothing too specific about the resolution. For instance: - #1 - #2

I did notice that there is a similar complaint for SQLServer2014 but I am using SQLServer2012 which was reportedly unaffected by the bug (maybe it actually affects?).

I can workaround this issue by using WITH (NOLOCK) or WITH (READPAST) but that's a bit unsafe. Any ideas?

Community
  • 1
  • 1
user1836155
  • 858
  • 14
  • 29
  • what query you are running from this, Im sure you can get the same information using `sys.columns`. – M.Ali Sep 03 '14 at 21:14
  • 2
    How is it unsafe? Do you want to read information about a table that is being dropped by someone else, or not? You should be getting blocked but I'd have to see the actual code or .xdl to understand why it's deadlocking. Do you experience the same if you [stop using `INFORMATION_SCHEMA` and use the catalog views instead](https://sqlblog.org/blogs/aaron_bertrand/archive/2011/11/03/the-case-against-information-schema-views.aspx)? – Aaron Bertrand Sep 03 '14 at 21:32
  • Also, since the bug report (which is about some really questionable SQL in the first place - who's creating a table 3000 times in a loop?) hasn't even been confirmed by Microsoft, how do you know it is unrelated, or whether it *only* occurs in 2014? – Aaron Bertrand Sep 04 '14 at 00:00
  • Using READPAST/NOLOCK is dangerous because that DROP operation is not the only query that can occur concurrently with the SELECT statement and in all other cases, there's no deadlock. As for bug report, I never said it's a verified bug. I referenced it because it is somewhat related and may be a lead for this problem. I will try with catalogs view as you suggested. Thanks! – user1836155 Sep 05 '14 at 18:25

0 Answers0