New answers tagged

1

By default, SQLServer writers block readers and vice versa if Read Commited Snapshot is not enabled. If application is not written exclusively for SQLServer , it may cause lots of extra locks (SQLServer can also escalate locks) when database is used more or less extensively. Enabling Read Commited Snapshot solves this issue and improves concurrency. On ...


0

"Read chunks, but only update a few of them"... SELECT a chunk -- separate transaction, don't care about locking, etc munch on that chunk to figure out the 'few' ids to update BEGIN; SELECT ... WHERE id IN (list of the few) FOR UPDATE; minimal other work UPDATE them; COMMIT; -- plus checks for deadlocks, etc, and restart the code. The idea ...


5

If your intention is to avoid readers from blocking writers and visa-versa in the default READ_COMMITTED isolation level, turn on the READ_COMMITTED_SNAPSHOT database option. This will cause row versioning instead of locking to be used to implement statement-level read consistency. Although often confused, the ALLOW_SNAPSHOT_ISOLATION option is not related ...


3

Enabling ALLOW_SNAPSHOT_ISOLATION does not alter the behaviour of your code. Instead it lays the groundwork for you to be able to use row versioning if you wish. Once snapshot isolation has been applied to the database a query can succesfully use row versioning if the following statement is used: SET TRANSACTION ISOLATION LEVEL SNAPSHOT This post by ...



Top 50 recent answers are included