Re: Reading locked rows

> > In <1993Sep03.061326.21151@wvus.org> sford@wvus.org (Scott Ford) writes: > > >Today's question #1 is about reading locked records. Here's the > >scenario: > > buffered logging data base "foo" with table "bozo". > > table bozo has two columns "col1" (primary key) & "col2". > > table foo also has row lock mode. First off. Sorry Scott Informix is not a versioned database. It does not provide the ability to see the current committed values of a row which is locked. But there is a method that can be used that may give you what you need. See below. > I thought this was the best way for the program to work -- Since we're > working with a record, I lock the row when the user selects the 'update' > option. However, I have a customer who is worried about concurrency. They > are proposing that I move the commit work statement to just prior to the > actual update. This means: > > user 1 -> update bozo > the original value for col2 was 5. user 1 is changing it to 10, > BUT we haven't started the transaction set yet. > > user 2 -> update bozo > when user 2 selected the same row for update from bozo, user 2 > sees the value for col 2 is 5 and changes it to 7 -- We still > haven't started the transaction set. > > user 1 -> begin work and update col2=10 > > user 2 -> begin work and update col2=7 > > I'm curious which method others use... > -- > Clay Irving N2VKG | See the happy moron Clay the above either results in a corrupted database or a lock error. Take the example of a bank account with a value of 10. Two users read the value. The first wishes to add 5 so he updates row with 15. The second then wants to add 10 so he updates row with 20 when it should be 25. For this simple example there are ways to fix this but you get the general idea. We are using a client server technique which reduces concurrency problems to a minimum by reducing transaction size as much as possible. The front end reads committed rows to the screen. We use isolation level committed read and set lock mode to wait ?? seconds to ensure this. The user then enters the changes required and sends a message to the backend to perform the transaction. Within the backend the transaction is started with a begin work. Then the row is re-read, locked and checked to ensure that it has not changed since it was read by the front end. If it has a concurrency error is returned to the user. After this the updates and any checks are done and the work committed. This results in very small transactions with locks being held for minimal periods of time. The result is that users should always see committed data but may have transactions rejected because changes were made to the data after they saw it. This method is acceptable for many applications though I admit not all. Cheers - Jim My opinions are my own. They may vary with time but they remain MINE! ---------------------------------------------------------------------- Name: Jim Gordon Internet: jgordon@ssf-sys.DHL.COM Company: DHL Systems Inc Phone: (415) 375-5222 (Work) Address: 700 Airport Blvd. #300 (415) 882-9728 (Home) Burlingame, CA 94010-1937 Fax: (415) 375-5019 ----------------------------------------------------------------------