OCC database expert Julian Fletcher describes how SQL’s ForceSeek can be used to combat database deadlocking and blocking.
One particular way of using tables in a SQL database can make them particularly prone to deadlocking (and deadlocking’s sickly cousin, blocking). This is where ‘working data’, generated by and used in a complex set of calculations, is temporarily stored in one or more tables.
Whereas any one process will probably access only a very small percentage of the rows in a ‘normal’ table, it is likely that it will be managing a significant proportion of the data in these ‘working data’ tables – up to 100% in fact. This makes it far more likely that one process will block or deadlock another.
Ways to mitigate the problem include:
-
1. Making sure the tables are suitably indexed
2. Forcing SQL to use the indexes via index hints, for example:
Join dbo.TWorkingData_SubTotals With (Index (IX_TWorkingData_SubTotals_ClientID))This second step is necessary, although not always wholly successful, because SQL often decides whether to use an index based NOT on concurrent process considerations, but on single-user speed.
The use of the ForceSeek Table Hint can give significant improvement when performing concurrent calculations of this sort. As its name implies, this makes SQL do an index seek instead of a scan, even if that seek is slower. And seeks are far less likely to block or be blocked than scans. Using the hint is simply a matter of replacing code such as:
Join dbo.TWorkingData_SubTotals With (Index (IX_TWorkingData_SubTotals_ClientID))with:
Join dbo.TWorkingData_SubTotals With (ForceSeek)In tests, this has allowed 8 or 9 concurrent processes to complete without error where previously the majority would have been deadlocked.