Following on from a thread I started about "concurrency" (real-time-ish
system), I thought I would play about to see if I could easily adapt my data
model to take account of potential multi-user write conflicts. So, I would
appreciate you checking my logic/reasoning to see if this kind of thing
will work. Below I have a stored procedure that will simply delete a given
record from a given table. I have appended a "_Written" counter to the
columns of the table. Every time the record is written, the counter is
incremented. Clients store the current _Written count in their objects and
pass this in to any write procedure executed.
The procedure explicitly checks the _Written count within the transaction to
see if it agress with the written count passed in by the client. If it does
not, the client throws an error. Note I am explicitly checking the
_Written count precisely so I can determine exactly why this operation might
fail, rather than checking @@ROWCOUNT after an update.
Thanks.
Robin
CREATE PROCEDURE dbo.proc_DS_Remove_DataSet
@_In_ID INTEGER,
@_In_Written INTEGER
AS
DECLARE @Error INTEGER
DECLARE @WRITTEN INTEGER
BEGIN TRANSACTION
SET @Error = @@ERROR
IF @Error = 0
BEGIN
SELECT @WRITTEN = _Written FROM MyTable WHERE ID = @_In_ID
SET @Error = @@ERROR
IF @WRITTEN <> @_In_Written
BEGIN
RAISERROR ('10', 16, 1)
SET @Error = @@ERROR
END
END
IF @Error = 0
BEGIN
DELETE FROM MyTable WHERE ID = @_In_ID
SET @Error = @@ERROR
END
IF @Error = 0
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION
RETURN @Error
