Re: Consistent Reads/Updates

In article <1osutmINNilt_at_meaddata.meaddata.com> davidp_at_meaddata.com (David Pledger) writes:
>In article <C4EJzo.7uD_at_da_vinci.it.uswc.uswest.com>, Jeffrey L. DeMent <jld_at_advtech.uswest.com> writes:
> Some thoughts on timestamps...
>We found that Sybase's timestamp was of finer granularity than our client
>application (PowerBuilder) was able to handle. PowerBuilder would truncate
>the least significant digits so that when we tried to do the update, it would
>almost never happen, because the timestamps did not match. We ended up adding
>a field, ConcurTag, to each table that was a one byte integer (tinyint in Sybase).
>Each time an update occurred (always through a stored procedure), the update
>could occur only if the ConcurTag was set to the same value as it was when you
>read it initially. We initially set the tag to zero and every update thereafter
>would increment the tag by 1. When the tag reached 255, we reset it back to 1.
>We skipped zero just so we could quickly determine if the row had ever been
>updated since it would only be set to zero upon insertion. The odds that
>255 updates would occur to my particular row in a table were
>miniscule and this solution works great.
>
>--

Another advantage of the ConcurTag solution is that the column is not of variable length. Sybase cannot presently do update in place on any row containing a timestamp, as it is considered a variable length column. I've heard this may change in Sybase 10. My company, being more paranoid than David's, uses smallint instead of tinyint.

Another advantage of this technique is that it is RDBMS independent, whereas timestamp (at least the column type of that name) is a propriatary extention.

The technique of checking before updating a row is often called 'soft' locking. 'Hard' locking would involve locking the row when you issued the SELECT.

Larry J Lennhoff Received on Sat Mar 27 1993 - 21:38:38 CET