Concurrency Control idea for discussion

[John Rivers]

Hello,

The common approaches to concurrency control in web apps:

optimistic:
- row version (timestamp, guid, datetime, digest) etc.
- value checking

pessimistic:
- locking fields / locking tables (not discussing here)


I have come up with a slight variation on optimistic value checking
that works a treat:

- first: detect which values have actually been changed by the user
- second: break your record down into "dependency groups" (see below)
- third: if you need to update within dependency group "a"
only check that group for changes (from elsewhere) don't worry about
other fields changing

"dependency group" explanation

record level locking makes the assumption that all the fields
in a record are cross dependent, and an edit in field1 might
conflict with an edit in field2 (of course only a human can know
whether this is the case or not)

however this is rarely the case for the whole record:

"Products" table:
ProductID (not in dependency group as can't be changed)
Description (in dependency group "1")
PriceWithoutTax money (in dependency group "2")
AmountOfTax money (in dependency group "2")

thus if one user updates description (group "1")
and another updates PriceWithoutTax and/or AmountOfTax (group "2")

then this is allowed

this increases the granularity of the concurrency control
leading to less conflicts

and is especially handy for tables with lots of fields
which don't want to be split into two tables

furthermore by detecting and updating only the values
that the user has changed, your transaction log space
will be preserved, instead of getting filled up with
values being replaced by themselves which seems to be
the norm?

all this can be wrapped up in a reusable class library
and applied to many forms with little effort

Best wishes,

John "The Troll" Rivers

 

[Lucas Tam]

furthermore by detecting and updating only the values
that the user has changed, your transaction log space
will be preserved, instead of getting filled up with
values being replaced by themselves which seems to be
the norm?

On the web, database requests are submitted instanteously.

How is your class library going to detect that another user has updated the
same row at the same time?

 

[John Rivers]

OnPostBack

open connected recordset (with updlock hint)
now we have full read/write lock on record in question
we can now compare the original values we read
from the recordset as it is now
and make decision accordingly
(releasing lock asap)

i haven't finished learning about ado.net yet
so i am not sure if DataSet Class is capable
of synchronous locking or not, certainly ADODB.Recordset
with ServerSide cursor can do it

i guess a stored procedure solution is also possible

(if DataSet can't do synchronous locking then that is
another architectural atrocity to deal with - two layers of
optimistic locking when one will do)

is that what you were referring to?

 

[Lucas Tam]

OnPostBack

(if DataSet can't do synchronous locking then that is
another architectural atrocity to deal with - two layers of
optimistic locking when one will do)

is that what you were referring to?

Ah OK, that makes more sense now, I thought that you weren't dong any
locking at all : )

I haven't looked at locking too much in ADO.NET either. But your idea does
sound interesting.

 

[Bruce Barker]

this is pretty standard. if you store the previous value (start of lock),
then at update only update if same.

update mytable
set col1 = @newcol1value,
col2 = @newcol2value,
col3 = @newcol3value
where primaryKeyID = @id
and col1 = @oldcol1value
and col2 = @oldcol2value
and col3 = @oldcol3value
if @@rowcount == 0
raiserror ("data modifed - no update for id %d",16,1,@id)



-- bruce (sqlwork.com)

 

[John Rivers]

Hi everybody,

I spent a few more hours trying to port my (beautiful) form and
datasheet editing library over to .NET
and this is what I have found:

- if you want to use finer granularity on your locking than per row
it is going to be tricky using DataSet as documented

however by sidestepping the standard DataAdapter.Update concept and
using a second DataSet
with a Transaction you *can* get enterprise-class locking features,
here's how:

- fill your dataset
- persist it across roundtrips
- onpostback do your string > typedvalue conversions into the dataset
- if all validations and conversions all passed:
- open a new dataset inside a transaction with repeatable read or
serializable isolation (or use read commited + use updlock hint)
(in effect we are simulating synchronous locking as per adodb with a
transaction)
- now you can detect actual edits by comparing DataSet1.CurrentValue
against OriginalValue
- and you can detect optimistic lock conflicts by comparing
DataSet1.OriginalValue against DataSet2.CurrentValue
- just skip the Update method and write your own that does the above!

this technique lets you:

- detect conflicts at the field level, not row level
- generate dynamic sql that only updates fields that have changed and
aren't conflicted
- present more intelligent conflict errors to the user
- refresh your DataSet1 with conflicted values

certainly very few of my intranet apps could survive without this
type of fine-grained optimistic locking, how about you guys?

or am i missing some wonderful hidden power of the DataSet?