December 2, 2011 at 4:49 pm
Hello,
Hope all is well.
I have noticed in my profiler trace few deadlocks. The select statements that were requesting an S lock on resources were deadlocked by update statements that requested an X lock. I figured out that the select statements are all the Entity framework generated sql select statement.
I have discussed this with my developers. Since the select should not be happening at the same time as updates I requested them if they could add any delay at all in the select statements. But I am not sure if they will be able to edit the EF generated sql.
has anyone come up with such a scenario before? Thanks for your inputs.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
December 3, 2011 at 12:36 am
There is a very good chance that you can eliminate the deadlocks without making any code changes by setting the database to read_committed_snapshot.
ALTER DATABASE [MyDatabase] SET READ_COMMITTED_SNAPSHOT ON;
You should read and understand this first:
Understanding Row Versioning-Based Isolation Levels
http://msdn.microsoft.com/en-us/library/ms189050(v=SQL.100).aspx
December 5, 2011 at 12:25 pm
Thanks for your reply michael.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
December 6, 2011 at 7:08 am
1) Here is a great resource for deadlock troubleshooting: http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspx
2) The BEST thing you can do is tell the developers to stop using EF. Objects are NOT relational tables - the two don't mix very well in many cases.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 6, 2011 at 7:44 am
Thanks for the reply Kevin. Per michael, I have changed the isolation level from Read Committed to Read Committed Snapshot (although I dont think I have enabled row versioning). I implemented this on a test environment first and still in monitoring it (probably for the rest of this week). If everything goes as desired I will change the settings on the production environment.
I have read the information u requested yet. thanks for the valuable information again.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
December 7, 2011 at 1:16 pm
I am still seeing the deadlocks: Please find the error log trace below:
deadlock-list
deadlock victim=process4df048
process-list
process id=process4df048 taskpriority=0 logused=0 waitresource=RID: 6:1:440021:29 waittime=1397 ownerId=18465075 transactionname=SELECT lasttranstarted=2011-12-07T14:17:45.240 XDES=0x80015920 lockMode=S schedulerid=4 kpid=268 status=suspended spid=144 sbid=2 ecid=0 priority=0 trancount=0 lastbatchstarted=2011-12-07T14:17:45.240 lastbatchcompleted=2011-12-07T14:17:45.240 clientapp=.Net SqlClient Data Provider hostname=CALLBOX6 hostpid=9440 loginname=DomainLoginName isolationlevel=read committed (2) xactid=18465075 currentdb=6 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
executionStack
frame procname=adhoc line=1 stmtstart=358 sqlhandle=0x02000000e6211b268f53cddf87d0ddda69574f2c40a250d8
SELECT [Filter1].[JobID1] AS [JobID]
FROM (SELECT [Extent1].[JobID] AS [JobID1], [Extent1].[Pri] AS [Pri], [Extent3].[RequiredFeatures] AS [RequiredFeatures1], [Extent4].[RequiredFeatures] AS [RequiredFeatures2] FROM [dbo].[PhoneJob] AS [Extent1] LEFT OUTER JOIN [dbo].[PhoneJobOptions] AS [Extent2] ON [Extent1].[JobID] = [Extent2].[JobID]
LEFT OUTER JOIN [dbo].[PhoneJobOptions] AS [Extent3] ON [Extent2].[JobID] = [Extent3].[JobID] LEFT OUTER JOIN [dbo].[PhoneJobOptions] AS [Extent4] ON [Extent2].[JobID] = [Extent4].[JobID]
WHERE 1 = [Extent1].[Status] ) AS [Filter1]
INNER JOIN (SELECT MIN([Filter2].[Pri]) AS [A1] FROM (SELECT [Extent5].[JobID] AS [JobID2], [Extent5].[Pri] AS [Pri], [Extent6].[JobID] AS [JobID3], [Extent7].[RequiredFeatures] AS [RequiredFeatures3] FROM [dbo].[PhoneJob] AS [Extent5]
LEFT OUTER JOIN [dbo].[PhoneJobOptions] AS [Extent6] ON [Extent5].[JobID] = [Extent6].[JobID]LEFT OUTER JOIN [dbo].[PhoneJobOptions] AS [Extent7] ON [Extent6].[JobID] = [Extent
frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000
unknown
inputbuf
(@p__linq__5 datetime2(7),@p__linq__6 int,@p__linq__7 int,@p__linq__8 int,@p__linq__9 int,@p__linq__0 datetime2(7),@p__linq__1 int,@p__linq__2 int,@p__linq__3 int,@p__linq__4 int)SELECT
[Filter1].[JobID1] AS [JobID]
FROM (SELECT [Extent1].[JobID] AS [JobID1], [Extent1].[Pri] AS [Pri], [Extent3].[RequiredFeatures] AS [RequiredFeatures1], [Extent4].[RequiredFeatures] AS [RequiredFeatures2]
FROM [dbo].[PhoneJob] AS [Extent1]
LEFT OUTER JOIN [dbo].[PhoneJobOptions] AS [Extent2] ON [Extent1].[JobID] = [Extent2].[JobID]
LEFT OUTER JOIN [dbo].[PhoneJobOptions] AS [Extent3] ON [Extent2].[JobID] = [Extent3].[JobID]
LEFT OUTER JOIN [dbo].[PhoneJobOptions] AS [Extent4] ON [Extent2].[JobID] = [Extent4].[JobID]
WHERE 1 = [Extent1].[Status] ) AS [Filter1]
INNER JOIN (SELECT
MIN([Filter2].[Pri]) AS [A1]
FROM (SELECT [Extent5].[JobID] AS [JobID2], [Extent5].[Pri] AS [Pri], [Extent6].[JobID] AS [JobID3], [Extent7].[RequiredFeatures] AS [RequiredFeatures3]
FROM [dbo].[PhoneJob] AS [Extent5]
process id=process722e08 taskpriority=0 logused=1028 waitresource=KEY: 6:72057594051952640 (77407d35f4fb) waittime=1398 ownerId=18465143 transactionname=user_transaction lasttranstarted=2011-12-07T14:17:45.260 XDES=0x1c01f5950 lockMode=X schedulerid=6 kpid=4392 status=suspended spid=219 sbid=2 ecid=0 priority=0 trancount=2 lastbatchstarted=2011-12-07T14:17:45.263 lastbatchcompleted=2011-12-07T14:17:45.263 clientapp=.Net SqlClient Data Provider hostname=CALLBOX6 hostpid=9440 loginname=DomainLoginName isolationlevel=read committed (2) xactid=18465143 currentdb=6 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
executionStack
frame procname=adhoc line=1 stmtstart=246 sqlhandle=0x02000000e2fea126ec39077b6c43268007d58a61831a662b
update [dbo].[PhonePerson]
set [Status] = @0, [Queued] = @1, [QueuedAt] = null, [ContactAttempts] = @2, [LastContactStatus] = @3, [LastPhoneNumberID] = @4, [LastContactedAt] = @5
where ((((([PersonID] = @6) and ([Status] = @7)) and ([Queued] = @8)) and ([QueuedAt] = @9)) and ([ContactAttempts] = @10))
frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000
unknown
inputbuf
(@0 int,@1 bit,@2 int,@3 int,@4 uniqueidentifier,@5 datetime2(7),@6 uniqueidentifier,@7 int,@8 bit,@9 datetime2(7),@10 int)update [dbo].[PhonePerson]
set [Status] = @0, [Queued] = @1, [QueuedAt] = null, [ContactAttempts] = @2, [LastContactStatus] = @3, [LastPhoneNumberID] = @4, [LastContactedAt] = @5
where ((((([PersonID] = @6) and ([Status] = @7)) and ([Queued] = @8)) and ([QueuedAt] = @9)) and ([ContactAttempts] = @10))
resource-list
ridlock fileid=1 pageid=440021 dbid=6 objectname=Database_Name.dbo.PhonePerson id=lock33d91fd80 mode=X associatedObjectId=72057594041139200
owner-list
owner id=process722e08 mode=X
waiter-list
waiter id=process4df048 mode=S requestType=wait
keylock hobtid=72057594051952640 dbid=6 objectname=Database_Name.dbo.PhonePerson indexname=IX_PhonePerson_1 id=lock3b0081480 mode=S associatedObjectId=72057594051952640
owner-list
owner id=process4df048 mode=S
waiter-list
waiter id=process722e08 mode=X requestType=wait
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
December 7, 2011 at 1:47 pm
Hi all,
I thought to run the victims (EF Generated SQL SELECT queries) through the DETA and see if it recommends any indexes. As expected DETA suggested that there will a performance improvement of 50% if I create a couple of indexes and few statistics. But one of the indexes is a clustered index on a GUID column. So Now I am confused to really approach this direction as creating a clustered index on GUID columns will have a performance hit whenever an update or an insert is performed on the table. So is there a betterway that I can prevent these deadlocks.
The actual execution plan suggests that by adding a missed index on the GUID column for that table the performance can be improved by 20%. However neither the DETA nor the actual execution plan recommends any indexes for the update statement.
Thanks for your help in advance
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
December 7, 2011 at 3:33 pm
Did you change the database where you are still seeing the deadlocks to Read Committed Snapshot?
December 7, 2011 at 5:12 pm
Sapen (12/7/2011)
Hi all,I thought to run the victims (EF Generated SQL SELECT queries) through the DETA and see if it recommends any indexes. As expected DETA suggested that there will a performance improvement of 50% if I create a couple of indexes and few statistics. But one of the indexes is a clustered index on a GUID column. So Now I am confused to really approach this direction as creating a clustered index on GUID columns will have a performance hit whenever an update or an insert is performed on the table. So is there a betterway that I can prevent these deadlocks.
The actual execution plan suggests that by adding a missed index on the GUID column for that table the performance can be improved by 20%. However neither the DETA nor the actual execution plan recommends any indexes for the update statement.
Thanks for your help in advance
I agree with Michael here, Snapshot will probably be your best bet to help avoid this, just be aware of the tempdb hits.
These queries are pretty nasty, about what I expect from ORM/EF these days. What's interesting is you'd think that Update statement with that many where components would be a single record. How many records is your update trying to handle at once coming in from EF? I'd find that call and figure out what transactional components it's wrapping that with. If it's doing a looping update inside a transaction like I expect it is off the entities loaded in memory, well... yeaaah... that's gonna suck.
As to a clustered index on a GUID... really, there's no way anyone is going to be able to tell you if that's wise from this side of the forum. You'll get some hard-core 'Hell no!' but really we don't know your transactional volume vs. read volumes. How large is the table in question and how wide are the rows... what's your fill factors? Etc.
If the GUID never changes after it's put in play the only concern you'll have are inserts forcing page splits all over the place in a clustered index that doesn't use an ever-incrementing GUID structure. Once that's complete it's like any other identity field really.
The other question is what is your current clustered index, and how much have the queries been optimized to use that instead of the one you might replace. If this is a heap a clustered almost certainly wouldn't hurt, no matter which one you use. We'd have to see the existing schema and indexing to have a clue.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 8, 2011 at 12:03 am
1260221107 (12/7/2011)
...
Please don't SPAM here.
December 8, 2011 at 6:03 am
Dev (12/8/2011)
1260221107 (12/7/2011)
...Please don't SPAM here.
No point in begging, they just don't care about us, only themselves. Just report to Steve and mark it as reported and move on! 😉
December 8, 2011 at 6:07 am
Ninja's_RGR'us (12/8/2011)
Dev (12/8/2011)
1260221107 (12/7/2011)
...Please don't SPAM here.
No point in begging, they just don't care about us, only themselves. Just report to Steve and mark it as reported and move on! 😉
That's what I was trying to explain to Anthony in another thread. We can report only few as SPAM... This guy SPAMed in 10+ thread with more than 4 posts. It was really annoying.
December 8, 2011 at 6:11 am
Dev (12/8/2011)
Ninja's_RGR'us (12/8/2011)
Dev (12/8/2011)
1260221107 (12/7/2011)
...Please don't SPAM here.
No point in begging, they just don't care about us, only themselves. Just report to Steve and mark it as reported and move on! 😉
That's what I was trying to explain to Anthony in another thread. We can report only few as SPAM... This guy SPAMed in 10+ thread with more than 4 posts. It was really annoying.
Glad I missed that! 😀
December 12, 2011 at 3:01 pm
Thanks for the input Michael. Yes I have enable the RCSI on this database. However I am still seeing deadlocks but not the same ones. This time its a different table but what I noticed is its a similar EF generated SELECT statement getting deadlocked by an update statement.
The other thing I noticed in the deadlock graph is that I see the isolation level setting as serializable for some reason. But when I query DBCC UserOptions I see the below output.
Set OptionValue
textsize2147483647
languageus_english
dateformatmdy
datefirst7
lock_timeout-1
quoted_identifierSET
arithabortSET
ansi_null_dflt_onSET
ansi_warningsSET
ansi_paddingSET
ansi_nullsSET
concat_null_yields_nullSET
isolation levelread committed snapshot
Please find the new dead lock graph below
deadlock-list
deadlock victim=process73d4c8
process-list
process id=process73d4c8 taskpriority=0 logused=0 waitresource=KEY: 6:72057594050183168 (fdc0f1a4ec56) waittime=1280 ownerId=158195461 transactionname=user_transaction lasttranstarted=2011-12-12T13:01:55.730 XDES=0x35bbb9950 lockMode=S schedulerid=7 kpid=6256 status=suspended spid=194 sbid=2 ecid=0 priority=0 trancount=1 lastbatchstarted=2011-12-12T13:01:55.737 lastbatchcompleted=2011-12-12T13:01:55.730 clientapp=.Net SqlClient Data Provider hostname=APPSERVER hostpid=3740 loginname=LoginName isolationlevel=serializable (4) xactid=158195461 currentdb=6 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
executionStack
frame procname=adhoc line=1 stmtstart=102 sqlhandle=0x02000000ee5f9a347525532fdabd8edeaedf298318000da3
SELECT
[Project2].[JobID] AS [JobID],
[Project2].[Name] AS [Name],
[Project2].[OwnerAppName] AS [OwnerAppName],
[Project2].[OwnerAppVersion] AS [OwnerAppVersion],
[Project2].[Pri] AS [Pri],
[Project2].[Status] AS [Status],
[Project2].[Size] AS [Size],
[Project2].[MessagesSent] AS [MessagesSent],
[Project2].[MessagesCompleted] AS [MessagesCompleted],
[Project2].[PackagedAt] AS [PackagedAt],
[Project2].[SubmittedAt] AS [SubmittedAt],
[Project2].[CompletedAt] AS [CompletedAt],
[Project2].[LastUpdatedAt] AS [LastUpdatedAt]
FROM ( SELECT
[Extent1].[JobID] AS [JobID],
[Extent1].[Name] AS [Name],
[Extent1].[OwnerAppName] AS [OwnerAppName],
[Extent1].[OwnerAppVersion] AS [OwnerAppVersion],
[Extent1].[Pri] AS [Pri],
[Extent1].[Status] AS [Status],
[Extent1].[Size] AS [Size],
[Extent1].[MessagesSent] AS [MessagesSent],
[Extent1].[MessagesCompleted] AS [MessagesCompleted],
[Extent1].[PackagedAt] AS [PackagedAt],
[Extent1].[SubmittedAt] AS [SubmittedAt],
[Ext
frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000
unknown
inputbuf
(@p__linq__0 datetime2(7),@p__linq__1 datetime2(7))SELECT
[Project2].[JobID] AS [JobID],
[Project2].[Name] AS [Name],
[Project2].[OwnerAppName] AS [OwnerAppName],
[Project2].[OwnerAppVersion] AS [OwnerAppVersion],
[Project2].[Pri] AS [Pri],
[Project2].[Status] AS [Status],
[Project2].[Size] AS [Size],
[Project2].[MessagesSent] AS [MessagesSent],
[Project2].[MessagesCompleted] AS [MessagesCompleted],
[Project2].[PackagedAt] AS [PackagedAt],
[Project2].[SubmittedAt] AS [SubmittedAt],
[Project2].[CompletedAt] AS [CompletedAt],
[Project2].[LastUpdatedAt] AS [LastUpdatedAt]
FROM ( SELECT
[Extent1].[JobID] AS [JobID],
[Extent1].[Name] AS [Name],
[Extent1].[OwnerAppName] AS [OwnerAppName],
[Extent1].[OwnerAppVersion] AS [OwnerAppVersion],
[Extent1].[Pri] AS [Pri],
[Extent1].[Status] AS [Status],
[Extent1].[Size] AS [Size],
[Extent1].[MessagesSent] AS [MessagesSent],
[Extent1].[MessagesCompleted] AS [MessagesCompleted],
[Extent1].[PackagedAt] AS [PackagedAt],
process id=process4bb048 taskpriority=0 logused=296 waitresource=KEY: 6:72057594050248704 (f770e3caca72) waittime=1280 ownerId=158195450 transactionname=user_transaction lasttranstarted=2011-12-12T13:01:55.713 XDES=0x6c20bae80 lockMode=X schedulerid=2 kpid=6524 status=suspended spid=98 sbid=2 ecid=0 priority=0 trancount=2 lastbatchstarted=2011-12-12T13:01:55.737 lastbatchcompleted=2011-12-12T13:01:55.730 clientapp=.Net SqlClient Data Provider hostname=APPSERVER hostpid=3740 loginname=LoginName isolationlevel=serializable (4) xactid=158195450 currentdb=6 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
executionStack
frame procname=adhoc line=1 stmtstart=144 sqlhandle=0x02000000d0e338368562522edeee18412fca2f10e520f002
update [dbo].[SmsJob]
set [MessagesSent] = @0, [MessagesCompleted] = @1, [LastUpdatedAt] = @2
where (((([JobID] = @3) and ([Status] = @4)) and ([MessagesSent] = @5)) and ([MessagesCompleted] = @6))
frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000
unknown
inputbuf
(@0 int,@1 int,@2 datetime2(7),@3 uniqueidentifier,@4 int,@5 int,@6 int)update [dbo].[SmsJob]
set [MessagesSent] = @0, [MessagesCompleted] = @1, [LastUpdatedAt] = @2
where (((([JobID] = @3) and ([Status] = @4)) and ([MessagesSent] = @5)) and ([MessagesCompleted] = @6))
resource-list
keylock hobtid=72057594050183168 dbid=6 objectname=DatabaseName.dbo.SmsJob indexname=PK_SmsJob id=lock34c7d1b80 mode=X associatedObjectId=72057594050183168
owner-list
owner id=process4bb048 mode=X
waiter-list
waiter id=process73d4c8 mode=S requestType=wait
keylock hobtid=72057594050248704 dbid=6 objectname=DatabaseName.dbo.SmsJob indexname=IX_SmsJob_PriorityStatus id=lock8744dd00 mode=RangeS-S associatedObjectId=72057594050248704
owner-list
owner id=process73d4c8 mode=RangeS-S
waiter-list
waiter id=process4bb048 mode=X requestType=wait
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply