sql server - I need row-level locking

Question

This is an extension to Is it possible to force row level locking in SQL Server?. Here is the use case

I have accounts table having account numbers, balances, etc. This table is being used by many applications. It is quite possible that while I am modifying an account, someone else is modifying another account. So the expected behavior is that I would lock my account(ROW) and the other use will lock his(another ROW).

But SQL Server 2008 R2 escalates this locking to page/table and the second user gets timeout exception. I have tried all the solutions mentioned in the referenced question but nothing is working.

How can I force SQL Server to lock a row-level lock only OR how can I modify this model in a way that it will work with page/table locking?

EDIT The update is targeting a single record via its PK and it is indexed so only ONE ROW is being updated/locked and the process takes not more than a minute

Edit Now it looks something weird is happening. I am using an ORM library for DAL which is opening more than one connections and i have raised the question to their support. But, for testing purpose, i opened two sessions on query tool and did following

Session # 1
begin tran
UPDATE myTable SET COL_1 = COL_1 WHERE COL_1 = 101;

Session # 2
SELECT COL_1 FROM myTable WHERE COL_1 = 101;

The query in Session # 2 times out !!! Queries for other values of COL_1 are working fine. Now it looks SELECT is blocked for a session if the same record is in edit mode in another session.

Though Oracle does support selection of a row (with default params/no keywords) while it is being modified by other session, SQL Server does not (with default params/no keywords), so it seems the problem is with the library.

Answer 1

SQL Server always uses row-level locking by default .... so what exactly is it that you need??

If you lock more than a certain amount of rows (roughly 5000), then SQL Server will do lock escalation (lock the table instead of more than 5000 rows individually) to optimize performance and optimize on resource usage - but that's a good thing! :-)

There are ways to turn this off entirely - but those are NOT recommended! since you're messing with a very fundamental mechanism inside SQL Server's storage engine.

See:

• MSDN docs on lock escalation
• Adam Machanic blog on lock escalation
• Introduction to Locking in SQL Server - also has scripts to visualize which process is locking what exactly (rows, page, table, type of lock etc.)