Subject Re: TransactionID from Transaction and Transaction Id from Record
Author Adam
--- In, Danny Garcia Hernandez
<danny@...> wrote:
> Hi, i have used buffer comparison to implement optimistic blocking.
> my component call post with update transaction, the component read
> inside other transaction the same record on the table, reading this
> record with a buffer and check it with the oldbuffer looking for
> diferences between buffers data. If some field have changed then i show
> a message to ask post confirmation.
> I think that buffer comparison is not the best option (what happen
> blob comparison?), but is the only that i have right now.
> BTW, i´m looking for other solutions. For example. Make comparison
> between transaction ids, one the transaction id asociated with the
> update transaction and the other the transaction id asociated with the
> newest record version on the database. If transaction ids are diferents
> mean that other transaction have changed the record before me and i
> to show the message to ask port confirmation. I´t is posible?


I don't think it is a good idea to rely on TransactionID for this,
because they are reset if the database is restored from a backup.

I am having trouble understanding what you want, but I think you have
a record in a table that is read into some UI. After making changes,
the user hits save and you want to make sure that no-one has modified
the record in the meantime.

If that is the case, you can use a generator and trigger to do this.

Add a field to the table called UpdateNumber as a BigInt. Create a
generator called Gen_UpdateNumber.

Create a trigger before insert or update that does the following.
NEW.UPDATENUMBER = GEN_ID(Gen_UpdateNumber, 1);

When loading the data into your UI, take note of the update number,
and only perform the update if the UpdateNumber has not changed. If it
has changed, you will need to design some sort of ability to merge the

A stored procedure would make this reasonably easy to do, as you could
raise a custom exception. Remember this will not detect uncommitted
changes to the update number, but in that case you will receive a lock