Re: How I dramatically sped up DB file updating


"Bob Richardson" <bobr at whidbey dot com> wrote in message
news:EaidnYE5WejDwz7enZ2dnUVZ_tudnZ2d@xxxxxxxxxxxxxxxxx

> Before hitting on this solution, I was surprised to find that
> adLockPessimistic seemed to run a bit faster than adLockOptimistic. Also,
> there seemed to be very little time difference between Update and
> UpdateBatch in my application.

The difference in locking scheme times should be no surprise. Optomistic
locking locks at time of update, then reads the record and compares it to
the original one used by the locking instance, failing if there is a
difference (i.e. if the db record has been updated since it was last read
by the updating instance). Pessimistic locking locks the record then reads
the "original". This later scheme does not have to do any comparisons
before doing an update because it has exclusive write access to the record.

The reason that Optomistic locking tends to be used is because it is much
more efficient in busy multi-user situations. The only time it really
causes problems is when more than one user tends to be updating the same
small group of records. Otherwise, it permits fewer and shorter locks on
tables.


.