MS SQL Server lock all records where column X = y

Question

I'm working with a legacy table which I cannot change. It looks similar to:

CREATE TABLE foo 
(
    Id int IDENTITY(1,1) not null,
    OwnerId int not null,
    OwnerRecordId int not null,
    SomeColumn varchra(50) not null,

    CONSTRAINT ix_foo_OwnerId (OwnerId)

    -- Ideally the following constraint would exist, but it doesn't. It is enforced
    -- with code alone. There are currently duplicates, which should not
    -- not exist, but they prevent creation of the unique index.
    --CONSTRAINT ux_foo UNIQUE (OwnerId, OwnerRecordId)
)

OwnerRecordId is similar to an identity column within each OwnerId:

Id	OwnerId	OwnerRecordId
1	16	1
2	16	2
3	16	3
4	57	1

Now I would like to copy all records from ownerId 16 to ownerId 57. So OwnerId 57 would end up with 3 new records, and their OwnerRecordId would range from 2 - 4.

While this copying is taking places, other processes might be creating new records.

I thought about doing the following, but the sub-query seems slow:

insert into foo (OwnerId, SomeColumn, OwnerRecordId)
    select   
        (57, SomeColumn, (select isnull(max(OwnerRecordId), 0) + 1 
                          from foo where ownerId = 57)
    from foo
    where OwnerId = 16

Then I thought I could lock the table where OwnerId = 57. If I could do this I could lock those records, get the current maximum, and then use ROW_NUMBER in my select and add that to the MAX value I grabbed once.

Only, I can't seem to prevent other users from selecting from the table short of a table lock. Is there a way to lock records where colun OwnerId = 57? Doing so would prevent others from geting the current max(OwnerRecordId) + 1 value.

Perhas there is a better approach?

Certainly the unique index should be added, I can't do that at this point though.

Answer 1

The following code should hopefully do the correct amount of locking

insert into foo WITH (SERIALIZABLE)
    (OwnerId, SomeColumn, OwnerRecordId)
select
  57,
  SomeColumn,
  (select isnull(max(OwnerRecordId), 0) + 1
   from foo with (SERIALIZABLE, UPDLOCK)
   where ownerId = 57)
from foo
where OwnerId = 16;

SERIALIZABLE (which is a synonym for HOLDLOCK) will cause a range lock over all the rows where ownerId = 57, and UPDLOCK will cause that lock to be held until the end of the transaction.

You need an index on (OwnerId) with OwnerRecordId as another key column or as an INCLUDE, otherwise the whole table will get locked.

Do not fall into the trap of using XLOCK, it doesn't work unless you are actually modifying that table reference.

---

You say you have many different IDs to copy, in which case it would be more performant to do it in bulk.

Dump the list into a temp table (or a Table Valued Parameter), then do a joined update. Something like this

CREATE TABLE #tmp (SourceId int, TargetId int, primary key (SourceId, TargetId))

-- insert using statements or BULK INSERT or SqlBulkCopy etc

insert into foo WITH (SERIALIZABLE)
    (OwnerId, SomeColumn, OwnerRecordId)
select
  t.TargetId,
  f.SomeColumn,
  ISNULL(f2.mx, 0) + ROW_NUMBER() OVER (PARTITION BY f.OwnerId ORDER BY f.OwnerRecordId)
FROM foo f
JOIN #tmp t ON t.SourceId = f.OwnerId
LEFT JOIN (
    SELECT f2.OwnerId, mx = MAX(f2.OwnerRecordId)
    FROM foo f2 WITH (SERIALIZABLE, UPDLOCK)
    GROUP BY f2.OwnerId
) f2 ON f2.OwnerId = t.TargetId;