How to change locking strategy in SQL Server?

Question

I've read articles like these: http://www.codinghorror.com/blog/archives/001166.html http://www.databasejournal.com/features/mssql/article.php/3566746/Controlling-Transactions-and-Locks-Part-5-SQL-2005-Snapshots.htm

And from what I understand, SQL Server has a very pessimistic locking strategy. And to improve performance, I should change the locking Read Committed Snapshot.

But I can't find where to do this. Where do I change the locking strategy?

Answer 1

You can read up on Using Row Versioning-based Isolation Levels with examples on how to set them using the ALTER command.

It is set at the database level as follows:

ALTER DATABASE YourDatabaseName SET READ_COMMITTED_SNAPSHOT ON;

A better starting point is the parent of the above documentation, which covers related topics: Row Versioning-based Isolation Levels in the Database Engine.

EDIT: added links mentioned in my comments below.

• DBCC USEROPTIONS - determine current isolation level
• Controlling Transactions and Locks Part 5: SQL 2005 Snapshots - explains read committed snapshot and the isolation level.

Answer 2

Using the SNAPSHOT Isolation Level will add a lot of load to the tempdb as your database load increases.

Changing the locking methods is best done via locking hints in the queries, or by changing the ISOLATION LEVEL in general for the stored procedure or connection. This is done with the SET ISOLATION LEVEL command, or by changing the isolation level on the connection object in .NET.

If you want SQL Server to handle its locking at a level other than the default page level (ie. row level locking) that has to be handled on a statement by statement level by using the WITH (ROWLOCK) hint within your statements.

UPDATE YourTable (WITH ROWLOCK)
   SET Col2 = 3
WHERE Col1 = 'test'

There is no global setting to change this locking level, and if ROWLOCK is used in combination with the snapshot isolation level operations will still take place at the page level as the entire page has to be copied off to the tempdb database then updated then the old version has to be dropped from the tempdb database.

Answer 3

You can set the lock isolation level when you set up the connection to the database, by calling

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

Where I work we do this for every connection we set up, they are all set up in the same place as we use a shared connection pool for access to the database. The READ UNCOMMITTED option then applies to all statements issued by that connection.