Consistent Reads/Updates

From: Jeffrey L. DeMent <jld_at_advtech.uswest.com>
Date: Wed, 24 Mar 1993 16:43:33 GMT
Message-ID: <C4EJsL.7sJ_at_da_vinci.it.uswc.uswest.com>

Our company seems to have a problem for which we can only devise inelegant, clumsy solutions, so I thought I would poll this community of Oracle experts (we are using Oracle V7 in UNIX) to see if a better answer exists. Also I am not a real database person so please excuse any major abuses of terminology.

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.

Assume point 1 is a simple SELECT. Point 2 will be an Oracle stored procedure call to UPDATE the row (passing in the row of data for update). Obviously some other person (Person B) might have come in and update the same row Person A was looking at sometime between point 1 and 2 in which case Person B's update will be overlayed and lost (unless "something" is done).

Or Person 1 could have done a SELECT WITH UPDATE locking the row but that has negative concurrency implications, and besides our company does not want to allow this option for policy reasons. We want all persons to use the stored procedure for update operations.

Or we could put timestamps in the database (on each row is one proposal) and at update time, check to see if the row being updated has been changed since it was first read. That puts additional burdens on the application and (probably depending on our design) the stored procedure doing the update.

This seems like a general problem that someone smarter than we has a better general solution for -- please share this solution if you would !

Jeff

---

 Jeffrey L. DeMent                                  _._._._._._._
 U S WEST Advanced Technologies                    (_|_|_|_|_|_|_)
 1475 Lawrence Street, Suite 400                    ` \` | | '/ '
 Denver, Colorado  80202                             ` \`| |'/ '
                                                      ` \| |/ '
 Phone: (303) 595-0519                                 ` \0/ '
 FAX:   (303) 592-6532                                  `~#~'
                                                          ^

 Internet: jld_at_advtech.uswest.com

---

Received on Wed Mar 24 1993 - 17:43:33 CET