Re: Consistent Reads/Updates

In article <C4EJsL.7sJ_at_da_vinci.it.uswc.uswest.com> Jeffrey L. DeMent <jld_at_advtech.uswest.com> writes:
>
>Our issue is one of achieving consistent reads and updates for long
>running interactions between humans and databases. A typical scenario
>goes as follows:
>
> 1. Person A sitting a some sort of GUI reads a row of data, and then
> proceeds to "think" for a while.
> 2. Person A decides on how they want to update the data before them
> and does so.
>

I remember going to a class where this was discussed. First issue a Select statement to get the data including the rowid.

Then, when the user indicates he wants to update the data, do a select for update of the same row. If the database is different from what is on the screen warn the user or abort the transaction. I think a timestamp is unneccessary - what if the second user changed the data back to the same value the first originally saw ? You can of course, use a timestamp if you like.

Do the select for update by ROWID, which is really fast.

Generically, replace ROWID with primary key if you want to remain portable among databases.

If all is successful so far, then the user can proceed to commit or rollback their changes which releases their select for update row lock.

You can also do this in Sybase using browse mode and timestamps, which you would have to do since Sybase doesn't support select..for update. However be forwarned if you do this your code will be proprietary and will rely on non-ANSI standard functionality - the ANSI standard way with Select for update should work just fine, and you can do it in almost any of the modern RDBMS products.

• Dan

Daniel Druker
Anderson Graduate School of Management at UCLA                    

| Dan Druker                                                               |
| agsm mail 	: ddruker                                                  |
| internet 	: ddruker_at_agsm.ucla.edu                                    |
| oracle*mail	: unix:ddruker_at_agsm.ucla.edu                               |
----------------------------------------------------------------------------

Disclaimer: None. I'm a student now and I don't care what you think. Received on Thu Mar 25 1993 - 09:31:28 CET