December 24, 2010 at 4:45 am
Hello,
We have a table which stores information about a device. The table is accessed for updates by two difference sources
1- A web application - which updates certain columns by fetching data from vendor database.
2- A local SQL Job - which runs every 10 seconds and syncing information into other parts of our database and then marking synced records as 'read'.
I have seen that SQL Server started failing the Job I have recently when web application is in more use.
One of the solutions to avoid this deadlock (I could immediately think of) is adding a link table and moving the 'read' column there.
However, I am more interested to learn from experts in SQL server if this would be best strategy to avoid this type of deadlocks? I just do not want to end up having another problem to fix one now.
Kind regards
December 24, 2010 at 5:31 am
Hi,
If you are using SQL Server 2005 or higher you can enable Row Versioning (Snapshot Isolation Level). It is not the 100% solution but it will be help you some level. Row Versioning use Tempdb so please make sure your tempdb must have sufficient disk space.
ALTER DATABASE <dbName> SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE
To revert Back
ALTER DATABASE <dbName> SET READ_COMMITTED_SNAPSHOT OFF WITH ROLLBACK IMMEDIATE
There is no requirement to reset SQL Server Instance after changing Isolation Level of database.
If you are not in position to use Row Versioning on Database Level then you have to consult with your web application designer to use Row Versioning Isolation level at transaction level. But I think Database level is correct and easy to administrate.
check url http://technet.microsoft.com/en-us/library/ms175095.aspx
Ram
MSSQL DBA
December 24, 2010 at 6:55 am
I think the first thing you want to do is understand precisely what is causing the deadlock - meaning what objects is each process requesting that is leading to the deadlock.
Using SQL Profiler, the "Deadlock Graph" will give you a nice graphical representation of why the deadlock is occurring. It could be the case that simply adding or changing an index will alter the execution path of the query and avoid the deadlock.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply