Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

I was experiencing a deadlock so I set up a trace flags as well as profile trace to capture a deadlock. During this exercise I found something interesting. In the profile trace I noticed that "sa" login name acquired an IX lock on one of the objects (table) involved in the query. I have WITH (NOLOCK) on every table.

An interesting thing is that this particular lock is always acquired few seconds later after all of the other locks have been acquired.

Any ideas why this "sa" would issue an IX lock on the object?

The query is being executed from SSMS. Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64).

Thank you

share|improve this question
(1) why are you using NOLOCK (2) please post the entire batch, including any statements other than your SELECT, BEGIN/COMMIT TRANSACTION, etc. – Aaron Bertrand Oct 4 '12 at 18:40
NOLOCK was used for the sake of example in an effort to avoid an exclusive lock, and in production i actually set the isolation level. There was no other code but a simple "SELECT Column FROM TABLE" in the batch. – AleksSarg Oct 4 '12 at 21:08
I can't think of a single scenario where a SELECT on its own will cause a deadlock (or when changing a data type will make a deadlock disappear). Did you capture the deadlock graph? Can you post it somewhere? – Aaron Bertrand Oct 4 '12 at 21:17
I think on balance this question ought to be closed as it is unlikely to benefit others due to it's highly specific nature. – Jack Douglas♦ Oct 9 '12 at 18:41

closed as too localized by Jack Douglas♦ Oct 9 '12 at 18:41

This question is unlikely to help any future visitors; it is only relevant to a small geographic area, a specific moment in time, or an extraordinarily narrow situation that is not generally applicable to the worldwide audience of the internet. For help making this question more broadly applicable, see the FAQ.

1 Answer

up vote 0 down vote

I figure out it out. The issue was the datatype of smalldate and an index that was created on it. For some reason, "sa" login id was issuing an IX lock on the table due to that datatype. As soon as I changed the smalldate datatype to datetime, the lock was longer acquired.

share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.