I need row level locking

Question

This is an extension to Is row level forced locking possible in SQL Server? . Here is a usage example

I have a table of accounts with account numbers, balances, etc. This table is used by many applications. It is possible that by changing an account, someone else will modify another account. So the expected behavior is that I blocked my account (ROW) and another use will block it (another ROW).

But SQL Server 2008 R2 increases this lock per page / table, and the second user receives a timeout exception. I tried all the solutions mentioned in the question mentioned, but nothing works.

How to force SQL Server to lock only row-level locking OR how can I change this model so that it works with page / table locking?

EDIT The update is for one record via PK, and it is indexed, so only ONE ROW is updated / blocked, and the process takes no more than a minute

Edit Now it looks weird. I use the ORM library for DAL, which opens several connections, and I asked a question about their support. But for testing purposes, I opened two sessions in the query tool and did the 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; 

Request in session # 2 times! Queries for other COL_1 values ​​work fine. Now it looks like SELECT is locked for a session if the same record is in edit mode in another session.

Although Oracle supports selecting a row (with default parameters / without keywords), while it is modified by another session, SQL Server does not (with default parameters / without keywords), so the problem seems to be related to the library.

Answer 1

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

If you lock more than a certain number of rows (approximately 5000), then SQL Server will lock the escalation (lock the table instead of more than 5000 rows separately) to optimize performance and optimize resource use - but this is good !:-)

There are ways to completely disable this, but they are NOT recommended! because you are tinkering with a very fundamental mechanism inside the SQL Server storage engine.

Cm:

• MSDN lock escalation docs
• Adam Machanik's Block Escalation Blog
• Introduction to locking on SQL Server - there are also scripts to visualize which process blocks what exactly (rows, page, table, type of lock, etc.)

Answer 2

Imagine your system as a client-server application, where the client and server are connected by a very slow line (for example, street mail), and users change their records for a very long time (for example, a week). Then think about when you need to block rows / data and when you really allow rows / data to be changed, etc. - Apparently, placing internal SQL server locks for several days no longer seems like a good idea.

If you do not have a situation where two users need to change the same record, you do not need to block when changing data at all. You only need to block for a very short moment when the records change in the database - in other words, when the user makes the changes. (This is an optimistic locking scenario.) Of course, if two users change the same data, then the latest changes will overwrite the previous ones.

If you absolutely need two users to never change the same data (pessimistic lock), then probably the most common way is to use a specific lock table for specific applications or specific fields (s) in the data table. When one user checks a record (when starting editing or similar), you need to check that this record is already in use (locked), and if not, mark this record as locked. Of course, to remove obsolete locks you will need some functionality.

Or use the SQL Server-specific internal functions for such cases. See here: sp_getapplock function on MSDN ; that way, you don’t have to worry about keeping records forever, etc.