Re: Row and Table Locking

On Tue, 04 Sep 2001 18:45:19 -0500, Graham Thornton <graham.thornton_at_ln.ssw.abbott.com> wrote:

>
>(example is using standard, default Oracle locking - i.e. not using
>FOR..UPDATE )
OK, this is where the model fails straight away. You are assuming that the front-end MUST use default database locking. It must not.

If the front-end lets a user change data without putting some form of lock on the original, then the problem is with the front-end, not the database locking. And that goes for any type of locking in any database. With Forms, things are (were?)done in such a way that this doesn't happen.

If you use the FOR UPDATE locking (which you have to, if you plan to display things on-screen while not blocking other readers/changers), you don't have this problem. FOR UPDATE is part of ORACLE locking and to be used where needed.

>
>You read a row from a table, and based on the information you find there
>you make a change using some typical Oracle-Forms based GUI app.

What happens at this stage is Forms will apply a re-read and lock with FOR UPDATE to the row right here. Before it lets you change the screen. If it succeeds, it compares what it just read with what it had read before. If they are the same, it lets you continue with your changes, if not it gives you an error straight away to warn you you must re-query.

>
>I read the same row and make a different change using the same GUI app.

With Forms, you'd queue right about here. I'd have put a row lock with FOR UPDATE on that same row as soon as I made a change on-screen to it. I'd have succeeded, but you would now queue.

>
>You write your changes back to the database and commit your transaction ( you
>hit Save ).

fine, I just released my lock. Your queue re-read to apply the FOR UPDATE lock proceeds now and Forms finds out the row just re-read is different from the row it had shown you before. You get an error straight away and *if* you decide to re-query, you'll see my changes. If you decide to not re-qeury, then Forms won't let you change anything else until you rollback the screen. Problem solved.

>
>I write my changes back to the database and commit my transaction ( I hit
>Save ).

This will never happen with Forms. Other front-ends might not do what Forms does, but it all depends on how they use the locking options available from ORACLE.

>
>Your changes are lost or 'buried' by mine.

they'll never be if the front-end does what Forms does. It depends on the design of the front-end. No matter what the locking system you have in the DB, if the front-end does not lock/verify changes, you're done. Nothing to do with optimistic/pessimistic locking at DB level. They are different things. Although one is designed to facilitate the other.

As soon as you throw a front-end into the equation, which presumes a delay between display, decision and change, you have to use some form of delayed locking. What you get with ORACLE is one kind of locking by default and an option for a more "pessimistic" locking if needed. Best of both worlds?

>Wow,
>that one will really trash your performance! :-)

As I said, SERIALIZABLE is bad news. FOR UPDATE is the way to go.

>I was impressed to see the SilverStream Web-Server supports optimistic
>locking with concurrency right out-of-the-box. The BEA guys told us we'd
>have to spend an extra $100K for a third-party transaction control system to
>do that with their web-server. We said no-thanks.
>

Web is completely different from an asynchronous terminal. It is very similar to the old "block-mode" terminal approach. If you make a change on-screen with an asynchronous terminal, the database process can be notified immediately. Therefore locking can act at this level.

With web (and block mode screens), when you make a change on-screen nothing "taps" the database back-end on the shoulder. Hence the need for pessimistic or FOR UPDATE/timestamp based locks. They were very common in the old days of block mode terminals in mainframes. Web is just a re-hash of the same idea.

Cheers
Nuno Souto
nsouto_at_optushome.com.au.nospam Received on Wed Sep 05 2001 - 07:11:21 CDT