Question I’m facing a concurrency problem with SQL Server. If one user is in a transaction and is updating a particular table, then other user’s aren’t able to read any data from that table at all. The other user application’s hang and wait until the first user either commits or rollbacks the transaction. This is causing my application to slow down considerably. I would be grateful if you can suggest a solution.
Answer Based on the information you provided, It sounds like that the table has a record lock on it instead of a row or page lock. Generally, SQL Server uses row-locking by default. The reason for this is to prevent just the problem you describe.
But if there are many rows to lock, SQL Server dynamically escalates locking to either page or table locking, depending on the number of records that SQL Server needs to lock. While lock escalation helps to reduce SQL Server overhead (keeping track of many locks can use up memory and CPU time), it also hurts data concurrency because other users can’t access records they need to access. In some cases, you may want to control how lock escalation works for a specific index. For example, for a specific index, you can specify if page locks are allowed on a particular table if a specific index is being used to access data in that table. SQL Server 7.0 and SQL Server 2000 (and 2005) offer slightly different options to perform this task. The first commands listed below work with both SQL Server 7.0 and SQL Server 2000 (and 2005). The second set of commands only works for SQL Server 2000 (and 2005). SQL Server 7.0 and SQL Server 2000 Lock Escalation Options SP_INDEXOPTION ‘INDEX_NAME’, ‘ALLOWPAGELOCKS’, TRUE When FALSE, page locks are not used. Access to the specified indexes is obtained using row- and table-level locks only. SP_INDEXOPTION ‘INDEX_NAME’, ‘ALLOWROWLOCKS’, TRUE When FALSE, row locks are not used. Access to the specified indexes is obtained using page- and table-level locks only. SQL Server 2000and 2005 Lock Escalation Options SP_INDEXOPTION ‘INDEX_NAME’, ‘DISALLOWPAGELOCKS’, TRUE When TRUE, page locks are not used. Access to the specified indexes is obtained using row- and table-level locks, only. SP_INDEXOPTION ‘INDEX_NAME’, ‘ALLOWROWLOCKS’, TRUE When TRUE, row locks are not used. Access to the specified indexes is obtained using page- and table-level locks, only. When these commands are used, they affect all queries that affect these indexes. This command should not be used unless you know, positively, that they always produce the results you desire.
Another way to help reduce the problem you describe is to use locking hints, which only affect individual queries that have the hint assigned to it. Here are some lock escalation hints you may want to consider:
Rowlock: This hint tells SQL Server to use row-level locking instead of page locks for INSERTS. By default, SQL Server may perform a page-level lock instead of a less intrusive row-level lock when inserting data. By using this hint, you can tell SQL Server to always start out using row-level locking. But, this hint does not prevent lock escalation if the number of locks exceeds SQL Server’s lock threshold.
Paglock: This hint forces SQL Server to use page-level locking no matter what. So SQL Server will not use either row-level or table-level locking if this hint is used.
Tablock: This hint forces SQL Server to use table-level locking only, and not to use either row-level or page-level locking.
If you decide to try one or more of these optimizer hints, keep in mind that using hints prevents SQL Server from figuring out what it thinks is best. If your data changes a lot, or if your queries or dynamic, using hints such as these may cause more problems than they cure. But if you know that a specific query has lock-related performance issues, and that the results of this query are predictable, then using one of these hints may be a good idea.
For even more information on locking, see this article.
Another solution to your problem might be to have the application rewritten so that it does not keep transactions open any longer than required. But this is not always an option.]]>