Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #484

multiple rows changes and consistency

Show all headers | View raw

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

Back to comp.databases.ms-sqlserver | Previous | Next — Next in thread | Find similar

Thread

multiple rows changes and consistency migurus <migurus@yahoo.com> - 2011-06-28 18:28 -0700
  Re: multiple rows changes and consistency Erland Sommarskog <esquel@sommarskog.se> - 2011-06-30 00:00 +0200

csiph-web