Re: Reading locked rows

Scott Ford writes: |> 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. |> |> 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. |> |> 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 |> the row. But suppose even 10 minutes is not enough and we want to see |> that row with the current committed value of col2 = 5. How can we do it? Well, you can't because there is no committed row. The table, as far as that particular row is concerned, is in an indeterminate state, and will remain so until you commit or rollback the current transaction. This is one of the side- effects of real-time data processing. It sounds like what you are after is some sort of batch-like processing where no changes are actually made until the commit is done. |> 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 |> want to see committed records even if they are locked. Is this possible |> with Informix? See above. |> Apparently it is with Oracle - it will show you the before image of a |> record. .. which carries with it its own unique set of problems.. |> 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)? Nope. As Greame as already pointed out, our before images could not be used to this purpose. For further info, see any one of C.J. Date's many books (but especially "An Intro to Database Systems), under the topic "Uncommitted Dependency Problem." Dave Disclaimer: These opinions are not those of Informix Software, Inc. ************************************************************************** "I look back with some satisfaction on what an idiot I was when I was 25, but when I do that, I'm assuming I'm no longer an idiot." - Andy Rooney