question about optimistic locking

Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:555714
Hi there,
I am working for a bigger team that develops VS.NET/SQL Server business app.
We have to implement application-wide solution for optimistic locking, so
for instance, we can inform end-user that the data was changed by somebody
else.
One of the ideas is to add a special OPLOCKID column to every table in our
database. That column is meant to be of decimal type and incremented every
time the record is updated. When we need to updates or delete the column,
"where" clause is to include the last known OPLOCKID. This way we can
guarantee that either we update the right record (unchanged between our read
and "now") or update will fail. The same for deletion - we will use "where"
with last known OPLOCKID.
The motivation behind this approach is that (a) using standard ADONET
mechanism for optimistic data locking is expensive because it compares all
"old" column values with "current" column values (b) since we control the
way OPLOCKID is incremented, we do not need to re-read it after successfull
update. Instead we just increment last known value and now it becomes
"current" value. A variation of this solution is to use trigger for updating
OPLOCKID filed but our architect says that we should avoid triggers in SQL
Server (although he was not too clear why).
To me all of that seems to be a way that was correct 10 years ago, when SQL
databases did not have a lot of functionality they have now. However I am
not a database guru, so I would like to get help on this group. My question
is: does all of that make sense ? Is there any better method in MS SQL that
allows achieveing the same result ?
Thank you very much,
Marek.You need to use a rowversion (timestamp) column. It's insane to develop
some custom mechanism to do this when the capability already exists.
This mechanism works very well, and doesn't require a trigger or other
mechanism to generate a new value for the column. Re-reading a row that was
just updated is not a problem because the chances are that the row will
still be in memory. If you have an immutable primary key (IDENTITY), then
all you need to verify is that the value of the rowversion column hasn't
changed for a row since it was read.
The only problem with this mechanism is if you have a mutable primary key.
But that's a problem regardless of the mechanism used for optimistic
concurrency.
You should be using stored procedures to perform updates to keep transaction
processing within the data tier wherever possible. This will simplify
troubleshooting and minimizing lock contention, blocking and deadlocks. It
will also improve scalability because it minimizes the time that locks are
held by waiting 'til the last possible instant to initiate the transaction.
Declare the procedure parameters as input/output, indicate whether a
collision occurred using the return value and pass the current
values--including the new rowversion back in the procedure parameters.
It sounds like you need a new architect.
"Marek" <nospam@.nospam.com> wrote in message
news:41J_e.2080$l03.436012@.news20.bellglobal.com...
> Hi there,
> I am working for a bigger team that develops VS.NET/SQL Server business
> app.
> We have to implement application-wide solution for optimistic locking, so
> for instance, we can inform end-user that the data was changed by somebody
> else.
> One of the ideas is to add a special OPLOCKID column to every table in our
> database. That column is meant to be of decimal type and incremented
> every
> time the record is updated. When we need to updates or delete the column,
> "where" clause is to include the last known OPLOCKID. This way we can
> guarantee that either we update the right record (unchanged between our
> read
> and "now") or update will fail. The same for deletion - we will use
> "where"
> with last known OPLOCKID.
> The motivation behind this approach is that (a) using standard ADONET
> mechanism for optimistic data locking is expensive because it compares all
> "old" column values with "current" column values (b) since we control the
> way OPLOCKID is incremented, we do not need to re-read it after
> successfull
> update. Instead we just increment last known value and now it becomes
> "current" value. A variation of this solution is to use trigger for
> updating
> OPLOCKID filed but our architect says that we should avoid triggers in SQL
> Server (although he was not too clear why).
> To me all of that seems to be a way that was correct 10 years ago, when
> SQL
> databases did not have a lot of functionality they have now. However I am
> not a database guru, so I would like to get help on this group. My
> question
> is: does all of that make sense ? Is there any better method in MS SQL
> that
> allows achieveing the same result ?
> Thank you very much,
> Marek.
>