Concurrency, have I got this more or less right?

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 @.ErrorYou can use binary_checksum to check for changes without having to
append an extra field onto every table. If any field in the row
changes, the checksum will change.|||On Fri, 21 Jan 2005 13:36:12 -0000, Robin Tucker wrote:

> I would
>appreciate you checking my logic/reasoning to see if this kind of thing
>will work.
(snip)

Hi Robin,

It will work, but some remarks nonetheless.

1. Consider using a timestamp column instead of your _Written column. SQL
Server will automaticall update the value of the timestamp column whenever
the row is inserted or updated; you never have to include it in your code.
But you can use it to check that the row is untouched the same way you
propose to do it with your self-made _Written column.
(Note: despite the name, the timestamp value is in no way related to date
or time of latest update. The synonym for timestamp is rowversion, which
describes the actual function of this datatype lots better)

2. Even if you check that _Written (or the timestamp column) is unchanged
before doing the delete, I'd recommend you to still include
WHERE _Written = @.WRITTEN
in the actual delete statement. There's always the theoretical possibility
that someone updates the row in the split microsecond between the SELECT
and the DELETE.
An alternative to this is to set the transaction isolation level to
REPEATABLE READ or to use the HOLDLOCK locking hint on the select. This
ensures that the lock used to read the row is not released after the read
operation, but retained until the transaction is finished, precluding any
changes from other connections to the row.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Robin Tucker (idontwanttobespammedanymore@.reallyidont.com) writes:
> 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

You should have "WITH (UPDLOCK)" after MyTable where. This is a shared
lock, so the row is still readable for others. But no other can get a
second UPDLOCK. This prevents other process from coming inbetween and
modifying the row.

Hugo suggested HOLDLOCK, but HOLDLOCK is a read-only lock. If two
processes attacks the same row simultaneously, you will get a deadlock,
because both get their HOLDLOCK, and none can delete because of the
other.

Using timestamps that Hugo suggested is an excellent idea.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Fri, 21 Jan 2005 22:53:20 +0000 (UTC), Erland Sommarskog wrote:

>You should have "WITH (UPDLOCK)"
(snip)
>Hugo suggested HOLDLOCK, but HOLDLOCK is a read-only lock.

Oops! You're right - I just keep interchanging these two.

Thanks for the correction, Erland!

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks both of you for your replies. Somewhere I missed the "timestamp"
type and yes that would be more suitable. Also will read up on locking
semantics as I was under the impression everything within my transaction
would be applied with the given rows locked for updates any way.

Thanks.

"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:p623v0hig394jkmboq5n476pecd8bmbs2j@.4ax.com...
> On Fri, 21 Jan 2005 22:53:20 +0000 (UTC), Erland Sommarskog wrote:
>>You should have "WITH (UPDLOCK)"
> (snip)
>>Hugo suggested HOLDLOCK, but HOLDLOCK is a read-only lock.
> Oops! You're right - I just keep interchanging these two.
> Thanks for the correction, Erland!
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)