I deal with high modification frequency data, which must be always
consistent when read and when updated. System is run under SQL Server
2005.

The task at hand is from transportation field, we need to persist
itineraries of trips, the trip stops are stored in runs, sequence of
stops is very important and subject to changes. There are roughly
50,000 stops in some 5,000 routes that system has to support. The
problem is how to provide consistent reads/writes when there are many
users who change those itineraries, shufling stops within runs, as
well as moving trips from one run to another.

For illustration please find below my DML, data samples and a scenario
of a trip moved from run 1 to run 2.

IF OBJECT_ID (N'tempdb..#STOPS', N'U') IS NOT NULL DROP TABLE #STOPS;
GO
CREATE TABLE #STOPS (
ID int not null IDENTITY,
RUN_ID int not null,
TRIP_ID int not null,
STYPE char(1) not null,
SEQ_NO int not null
)
GO

INSERT INTO #STOPS (RUN_ID, TRIP_ID, STYPE, SEQ_NO)
-- run 1 has trips 100 and 120
SELECT 1, 100, 'P', 1 UNION ALL
SELECT 1, 120, 'P', 2 UNION ALL
SELECT 1, 100, 'D', 3 UNION ALL
SELECT 1, 120, 'D', 4 UNION ALL
-- 2 has trips 200, 300, and 400
SELECT 2, 200, 'P', 1 UNION ALL
SELECT 2, 200, 'D', 2 UNION ALL
SELECT 2, 300, 'P', 3 UNION ALL
SELECT 2, 400, 'P', 4 UNION ALL
SELECT 2, 300, 'D', 5 UNION ALL
SELECT 2, 400, 'D', 6
GO

-- we need to transfer trip # 200 from run 2 into run 1,
-- placing its 'P' stop after 'P' stop of trip 100
-- and its 'D' stop after 'P' stop of trip 120,
-- so resulting run 1 should look like this
-- 1, 100, 'P', 1
-- 1, 200, 'P', 2
-- 1, 120, 'P', 3
-- 1, 200, 'D', 4
-- 1, 100, 'D', 5
-- 1, 120, 'D', 6
-- and run 2 would be like this:
-- 2, 300, 'P', 1
-- 2, 300, 'D', 2
-- 2, 400, 'P', 3
-- 2, 400, 'D', 4

-- UPDATE STARTS HERE, TRAN BEGIN
-- 1) process run 2:
DELETE FROM #STOPS WHERE RUN_ID=2 AND TRIP_ID=200
-- 2) re-sequence run 2:
UPDATE #STOPS SET SEQ_NO=SEQ_NO-1
WHERE RUN_ID=2 AND SEQ_NO>2
UPDATE #STOPS SET SEQ_NO=SEQ_NO-1
WHERE RUN_ID=2 AND SEQ_NO>4

-- 3) re-sequence all the stops in run 1 that are after new 'P' stop:
UPDATE #STOPS SET SEQ_NO=SEQ_NO+1
WHERE RUN_ID=1 AND SEQ_NO>=2
-- 4) re-sequence all the stops in run 1 that are after new 'D' stop:
UPDATE #STOPS SET SEQ_NO=SEQ_NO+1
WHERE RUN_ID=1 AND SEQ_NO>=4
-- 5) add new trip stops in run 1:
INSERT INTO #STOPS (RUN_ID, TRIP_ID, STYPE, SEQ_NO)
SELECT 1, 200, 'P', 2
INSERT INTO #STOPS (RUN_ID, TRIP_ID, STYPE, SEQ_NO)
SELECT 1, 200, 'D', 4

-- 6) End of updates COMMIT


-- note: stype above is just for reference and carries no additional
value but make data readable.

The biggest concern is to make sure that
a) nobody does updates to runs 1 and 2 while the update steps 1
through 6 are running
b) nobody could read a state of the runs while updates are in progress

One posibility is to use sp_getapplock to serialize all access, but
that is not ANSI, and we would like to stay away from db-specific
solutions.

What would be your recommendation on isolation level, hints etc., to
keep and present data in a good shape.

Thanks in advance
migurus

migurus (migurus (AT) yahoo (DOT) com) writes:
Moreover, sp_getapplock would apply to that procedure only. I don't know,
but it seems to me that there would be several paths of access in this case.
Also, an application lock on the lock would mean that while you are working
with runs 1 and 2, no one can work with runs 12 and 13.

I think you should start the transction with something like:

SELECT * INTO #temp FROM Stops WITH (UPDLOCK) WHERE RUN_ID IN (1, 2)
DROP TABLE #temp

The temp table here is of no interest in itself. What you do is that you
place an update lock on the runs you plan to modify. An update lock is a
shared lock which states an intent to update. Other process can still
read, but no can update the data. Or take a second update lock on the
resource.

As written, this gives you REPEATABLE READ. This means that none of
the data you have read can be changed. However, it still possible
for another process to insert new trips of stops in the runs.

To get protection against such phantoms (as they are called), you can
change the locking hint to (UPDLOCK, SERIALIZABLE). There are two drawbacks
with this. One is that your lock may be too wide. You may also block
another process to insert a new trip first in run 3. The other drawback
is that the serliasable isolation level is prone to create deadlock
situations.

You semed to be concerned about portability, and UPDLOCK is indeed a
Microsoft-specific hint. You can to without it, but in that case you
will see a lot of conversion deadlocks, where two processes both
were able to lock the same trip and then tries to continue with an
update.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx