What isolation level and lock type should I use?

[michael_hk]

Hi,

I have a C# program that calls a SP (MSSQL 2000) and I want my SP to first SELECT some rows and then UPDATE ONLY those selected rows. As my SP will run for quite a long time, I want my SP to

allow other users to insert rows while the SP is running​

but I dont want

• other users to modify the rows I have selected
• to update rows that are inserted between my SELECT and UPDATE

Here is my SP (simplified version)

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

/* Store the required rows in a table variable */

DECLARE @SelectedID TABLE (ID char (10) NOT NULL PRIMARY KEY)

INSERT INTO @SelectedID
SELECT ID FROM
tableA INNER JOIN tableB ON ... WHERE ....

/* Select all required rows from @SelectedID and
this will be return as a datatable to my C# program */

SELECT fieldA,... FROM tableA INNER JOIN tableB
ON ... where ID IN (SELECT ID FROM @SelectedID)

/* Update ONLY selected rows */

UPDATE tableA SET fieldA = whatever WHERE ID IN (SELECT ID FROM @SelectedID)

COMMIT TRAN

The problem with this SP is other users cannot insert rows while the SP is running (they need to wait until the SP finishes). What isolation level and lock type should I use?

Thanks in advance.

Michael

 

[michael_hk]

Solution can be found here.