Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
michael_hk
Starting Member
6 Posts |
 Posted - 2005-05-10 : 23:59:50
|
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 don't 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 @SelectedIDSELECT ID FROMtableA 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 TRANThe 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 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-05-14 : 17:03:35
|
| Don't think what you are suggesting is possible.An easy way round this is to create a flag (spid and login time is a good flag for this).Your process sets these values when it reads.The trigger rolls back any updates that try to affect these records - after checking that the spid/login time exists just in case your processs has crashed.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
|
|
|
What isolation level and lock type should I use?