2 Select Statements Deadlocking
-
So we have a server which has user tables in the master database (I know that is not a good idea), they are constantly getting deadlocks shown here
left statement
right statementSo 2 select statements at the same time on the same table causing a deadlock. I didn't think this was possible?
-
Yeah, it's possible. Work through this - it should get you to the cause. Just as a matter of interest, how many rows are in the table and what indexes are on it?
John
- John Mitchell-245523 - Wednesday, February 8, 2017 9:52 AM
At this time there are no rows in the table. I think someone is using it for concurrency or something. There are 6 indexes with 1 of them being clustered. The requests come in exactly at the same time to the millisecond.
-
could it be that the construction of the query, where it says COLUMNNAME IN(singlevalue) causes a range scan, where if it was COLUMNNAME = singlevalue , it would be an index lookup, and that is causing the deadlocks?
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
as i recall it you shouldnt be seeing a lock mode of X for a select statement, SQL Server lock modes
Can you provide more detail of the queries that are being executed and full details of the indexes on the table-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
- Perry Whittle - Thursday, February 9, 2017 10:52 AM
There are indexes but there are often no rows in the table most of the time. Something is inserting a row, it persists for a few seconds, then its gone. I don't know much about the application. The queries are listed above and the indexes are
clustered primary key with 3 fields. Not sure what is inserting and deleting the records.The table has 5 non clustered indexes on the other fields, each with one column so that accounts for the 8 columns of the entire table. I don't understand why it was set up that way but I think a noob put this thing together.
- Perry Whittle - Thursday, February 9, 2017 10:52 AM
At a guess, either the SELECT is WITH (XLOCK), or there's an insert within the transaction that we're not seeing, and so it'll show the SELECT to have an XLock, because that's the lock that the session holds
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass - GilaMonster - Thursday, February 9, 2017 3:04 PM
This was my train of thought Gail, there's something else we're not seeing
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply