April 4, 2015 at 3:43 am
hi,
please correct me i am inserting updating few tabels from snapshot
and reading same bunch of tables from reporing using readcomminted
it is showing some deadlocks i think it is write in this situation
as " x" is not compitable with "s" ,"is".
yours sincerley.
April 4, 2015 at 5:30 am
Deadlocks occur because Process A has exclusive locks on objects that are needed by Process B while at the same time Process B has locks that are needed by Process A. The most common cause of this is when you have two different queries that INSERT/UPDATE/DELETE data and usually SELECT data, but they do it in different order. One does a SELECT, then an UPDATE. The other is doing an UPDATE then a select, or, each one is accessing different tables in different orders. The solutions usually entail getting everything into the right order, tuning the queries because if they were fast it wouldn't be an issue, or, as a last resort, possibly using query hints to let one of the processes get an exclusive lock on all the resources at the start of it's querying.
----------------------------------------------------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
April 5, 2015 at 4:57 am
each one is accessing different tables in different orders.
that means in my situation it can come, becasue the read comiited tran is only seclting.
and snapshot is only inserting update .
yours sincelrey
April 5, 2015 at 5:24 am
Yes. Although, generally, you don't see deadlocks from a procedure that only has reads.
----------------------------------------------------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
April 7, 2015 at 1:28 am
If you don't know exactly what kind of deadlock you are getting set up a Server Side Trace for Deadlock graph event and find out.
In your situation the deadlock may occur if UPDATE and SELECT accesses same table starting from different indexes. It is not trivial to make index access in the same order.
Anyhow I would recommend let reporting to read in snapshot isolation instead. Snapshot isolation does not acquire locks and is good fit for reporting from OLTP databases.
Are there good reasons for running UPDATE in snapshot? This optimistic approach may require advanced error handling that is not so easy to maintain and troubleshoot.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply