What changes should be made to SQL Server’s locking architecture to make it more developer friendly?

Question

I’ve lately come across a rather frustrating situation where SQL server refuses to issue locks only against a primary key when a statement like this select * from table with (rowlock updlock) where key=value is executed against it. Now don’t get me wrong here, it does lock the row but it goes one step farther and locks the table too.

I’ve read up about SQL lock escalation and I’ve looked at using specific indexes in the lock hints but you see, this just isn’t practical when there are numerous indexes in a table with millions of records and concurrent updates that need to happen on those records. For a small table and a specific query it’s possible to get the desired behaviour, but when the table has a large width (many columns) and there are numerous processes using the data, this method melts down and can become a real point of contention.

What I’d like to see added is a new lockhint suck as PKLock (which would stand for Primary Key Lock) which would issue a lock against the primary key of a row and anytime an index, a table scan or other method is used to get the row, it would check this lock and honour it instead of locking the entire table.

As such a table lock would not need to be issued and this would greatly increase the capacity for parallel execution of code against the DB.

Please weigh in on this idea and point out any flaws which it might have, ways that it could be improved or other elements that should be added to resolve my dilemma.

EDIT

@Remus

If I execute this query

begin transaction
select lockname from locks  where lockname='A'
update Locks Set locked=1 where lockname='A'

and then this query:

begin transaction
select lockname from locks  where lockname='A'

Row A is returned in both examples before committing the transactions. This is reading behind the update, not blocking.

A successful solution should do the following without specifying indexes to use:

1. With Query 1: Read and lock A, update A
2. With Query 2: Read and lock B, update B, Commit query 2
3. With Query 2: Read B and be blocked until Locks on A are released
4. With Query 1: Commit Query 1
5. With Query 2 :Read and lock A, update A, Commit query 2

Answer 1

Perhaps you don't want to specify any locking at all?

http://blogs.msdn.com/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx

Answer 2

You have asked this question before and were given the answer: fix your schema and your code. In that post the lock conflict was an IX lock, and a conflict on intent locks indicates high granularity locks, which in turn indicate table scans. You don't need lock hints, you just need an index and decent queries. Take for instance your other question, http://stackoverflow.com/questions/2335433/why-does-row-level-locking-not-appear-to-work-correctly-in-sql-server where answer is trivial: Locks table needs to be organized by a clustered index on LockName:

CREATE TABLE [dbo].[Locks]( 
    [LockName] [varchar](50) NOT NULL, 
    [Locked] [bit] NOT NULL, 
    CONSTRAINT [PK_Locks] PRIMARY KEY CLUSTERED  ([LockName]));
GO    

insert into Locks (LockName, Locked) values ('A', 0);
insert into Locks (LockName, Locked) values ('B', 0);
GO

On one session do this:

begin transaction
update Locks 
  set Locked=1 
  output inserted.*
  where LockName = 'A';

On the other session do this:

begin transaction
update Locks 
  set Locked=1 
  output inserted.*
  where LockName = 'B';

There is no update conflict, no blocking, no need for (wrong) hints, nothing. Just good ole' correct schema and query design.

As a side note, the lock you describe here already exists and are called key-locks. They are the default, implicit mode SQL Server operates. Just how in the world do you imagine SQL Server can publish TPC-C benchmark numbers of 16000 tpc transaction per second? You have all the parallelism capacity you need in the server, you just need to read a book or two to understand how to use it. There is plenty of literature on the subject, you can start with Transaction Processing: Concepts and Techniques.

Updated

begin transaction 
select lockname from locks  where lockname='A' 
update Locks Set locked=1 where lockname='A'

This will never work, no matter how many/diverse lock hints you try. This is why you have the update with output syntax:

begin transaction 
update Locks 
 Set locked=1 
 output inserted.*
 where lockname='A'

this ensures that you first update, then return what you've updated. This technique is fairly common in databases for exactly the semantics you seek: resource acquisition. In fact this technique is the cornerstone of the resource acquisition poster child: queue processing. See Queues paragraph in OUTPUT Clause. In queues you have a table of resources to be processed, and each thread grabs one, locks it and start processing:

create table Resources (
   id int identity(1,1) not null,
   enqueue_time datetime not null default getutcdate(),
   is_processing bit not null default 0,
   payload xml);

create clustered index cdxResources on Resources 
  (is_processing, enqueue_time);
go   

-- enqueue:
insert into Resources (payload) values ('<do>This</do>');
insert into Resources (payload) values ('<do>That</do>');
insert into Resources (payload) values ('<do>Something</do>');
insert into Resources (payload) values ('<do>Anything</do>');

Now from separate sessions, run this:

--dequeue
begin transaction;
with cte as (
  select top(1) *
  from Resources with(readpast)
  where is_processing = 0
  order by enqueue_time)
update cte
  set is_processing = 1
  output inserted.*;   

You'll see each session grabs it's own resource, locks it and skipps everything locked by everybody else. It so happens I have in production a system that runs exactly like this, with over 5M resources int he table (they are web service payment processing requests), and dequeueing and processing around 50 per second, from 100 concurent processors (takes about 2sec. per call to process). On a piece of junk hardware. So it absolutely is possible.