Re: Reading locked rows

In <1993Sep03.061326.21151@wvus.org> sford@wvus.org (Scott Ford) writes: >Hey folks, >We're running online & istar 5.0 on a Sequent 2000 with ptx 2.0.4. We >talk to it using inet-pc 4.10 and powerbuilder running on windows. >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. >user 1 -> begin work; update bozo > set col2 = 10 > where col1 = 'a'; > let's say that the previous value for col2 was 5. note that > we HAVE NOT yet done a commit work. Which is setting yourself up for a problem. You should not allow anything which might cause a significant delay between the UPDATE and the COMMIT. >user 2 -> set isolation to dirty read; > select * from bozo;> user 2 sees the row where col1 = a and shows col2 = 10 >user 3 -> set isolation to committed read; > select * from bozo;> user 3 errors (probably -244 or -246) due to locked record. And surprise, surprise here's the problem. >Ok, sure, we could set lock mode to wait 30 and hope that user 1 commits >by that time. Or we could write some program to trap the error and skip tacky. >the row. But suppose even 10 minutes is not enough and we want to see probably undesirable. >that row with the current committed value of col2 = 5. How can we do it? don't issue the update until the user says "Commit". Use LOCK MODE WAIT <n> where <n> = max expected commit time + 1. >Sure, we'll probably see some discussion about shortening the time >between update & commit, but that's not the issue. The issue is that we That's debatable. >want to see committed records even if they are locked. Is this possible Then use Update locks not Exclusive locks. >with Informix? Not if it's an exclusive lock. >Apparently it is with Oracle - it will show you the before image of a true. >record. And this would seem possible under Informix since the archive >does put all the before images on a tape based upon the start time (or is >it waiting for a row lock to release before continuing)? But there is no equivalent of an Oracle Rollback Segment. The archive uses before images of *pages* from the Physical Log, any given page may have been updated (with or without commit) many times by the time your User 3 asks to read it. How is the system to know which rows could be safely extracted and which could not??? In any case, there is no guarantee that the before image would still be in the Physical Log by the time User 3 asks to see it, and implementing such a guarantee would have horrendous implications throughout the system. >Thank you for your patience in reading the example and for any thoughts >you might have on the subject.... >-- >Scott F. Ford, Data Base Administrator | sford@wvus.org >bps (Benefit Panel Services) | elroy.jpl.nasa.gov!wvus!sford >888 South Figueroa Street, Suite 1400 | Voice: (213) 489-2694 >Los Angeles, CA. 90017 | FAX: (213) 489-7973 > > "...The bozone layer: shielding the rest of the solar > system from the earth's harmful effects...." graeme -- Disclaimer: The author's opinions are his own, and not necessarily those of Pyramid Technology Ltd. or Pyramid Technology Inc. --------------------------------------------------------------------------- -m------- Graeme Sargent Voice: +44 (0)252 373035 ---mmm----- Senior Database Consultant Fax : +44 (0)252 373135 -----mmmmm--- Pyramid Technology Ltd. Telex: Tell who??? -------mmmmmmm- Farnborough, Hants GU14 7PL Email: graeme@pyra.co.uk --------------------------------------------------------------------------- We have the technology. The tricky bit is learning how to use it.