May 5, 2015 at 10:55 am
Just migrated application from Oracle to SQL and we are seeing alot of deadlocking and blocking. I did notice that app seems to be passing isolation level of repeatable read. Attached is a .doc of one of the deadlocks, is there a way to avoid these in the repeatable read isolation level? This example is a select with two tables, using NCI's that cover the where, and a insert doing just a clustered index insert. Any help is appreciated. Is this simply try to get rid of the repeateable read if not needed, guess have to check with vendor on that or is there a way to get this to not deadlock using repeatable read?
May 5, 2015 at 4:27 pm
The Repeatable Read isolation level is much more restrictive, holding locks until a transaction is committed so that others cannot modify data, so yeah, that's absolutely going to lead to more blocking. I haven't tested it, so I couldn't say for certain, but I suspect it's going to lead to more deadlocks too since deadlocks are fundamentally a performance issue.
Unless there's a really good reason for Repeatable Read, instead, I'd suggest using Read Committed Snapshot. For the vast majority of systems, that's the best way to go.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
May 5, 2015 at 9:09 pm
Further on Grant's post, recommend you have a look at Kalen Delaney's book SQL Server Concurrency:
Locking, Blocking and Row Versioning
😎
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply