RE: SQL Lock & consistency

From: Robertson Lee - lerobe <lerobe_at_acxiom.co.uk>
Date: Tue, 11 Dec 2001 14:20:09 -0800
Message-ID: <F001.003DA535.20011211141023@fatcity.com>

Absolutely tremendous.

No mention of core dumps, I noticed................

-----Original Message-----
Sent: 11 December 2001 20:10
To: Multiple recipients of list ORACLE-L

I have to share this post I got on another list. Someone had asked a question about how Oracle locks work, here is the question.......

Q: i'd like to understand the lock modes (share & exclusive) & how to make them on table in DML transaction & why locking is automatically use the lowest level of restrictiveness & how the read consistency occures

Here is the response someone sent................

An Oracle table is like a public restroom with an open door.

When you go in to use the restroom, you shut the door as you enter, and you have placed the bathroom in shared lock mode. When another person want to use the restroom too, they can push open the door and enter. Both of you are now in the restroom and sharing it.

However, if you go into the restroom, shut the door, and also lock it, then you have acquired the restroom in exclusive lock mode. Nobody else can come into the bathroom, even though there are multiple stalls in there: your exclusiveness prevents anybody else from going in there.

In a third situation, you go into the restroom in shared mode, and then somebody else comes in after you, then it would be very presumptious of him to lock the door behind him because it just isn't done for someone to lock another stranger into the restroom; it would also make you very apprehensive to know that he's now locked you in there with him. So, if someone already is in the restroom and is sharing it, it prohibits someone else from coming in later and claiming exclusivity on it (at least not until the first person leaves.)

So, when you acquire the restroom, you should use the lowest level of lockage, otherwise you're just being rude in not sharing use of the facilities, and you would probably cause a long queue of people outside the restroom causing contention problems waiting to gain access to the bathroom because it's been locked in exclusive mode.

Now, suppose you set up web cams inside the restroom to take video images of the restroom and the stalls therein, and issue every one who enters virtual reality googles. The web cams guarantees users' privacy by use of readconsistency: the moment someone goes into a stall and closes the door, the web cam switches from live feed to replaying footage of an empty stall that was recorded from just prior to someone going into that stall. So, although there may be several people in the stall doing their business, your virtual reality googles always seem to show you clean, empty stalls. You pick an empty stall and try to enter it. If the stall is occupied, the stall door will be locked, so you can't enter it and do things to it, but through the magic of read-consistency, it looks as if it is unused; so like an obstinate idiot, you keep trying, and trying to open the door that won't open; at least not until the person in the stall comes out again, but you don't know that because your eyes tell you that the stall is free; you can see it, you just can't seem to be able to touch it. If the stall is truly unoccupied, you may enter and close the door behind you and now you have locked your stall record in exclusive mode (although the restroom as a whole can still be in shared mode; you just don't want anyone else with you in the stall itself.)

After you do your business in the stall, and piss all over the toilet seat making a huge mess, you can either commit your work by just exiting the stall, exposing what you've done to the stall to all the other web cams now looking in on your messy, empty stall; or you can rollback your work by cleaning up the mess so that when you exit, nobody else is the wiser that you peed with the toilet seat down. The moment you commit or rollback by exiting the stall, your claim on the stall is released, and someone else may now acquire the stall.

So, the lesson to be learnt here is: either learn to piss sitting down, or raise the seat when you pee.
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Farnsworth, Dave
INET: DFarnsworth_at_Ashleyfurniture.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).

The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please re-send this communication to the sender and delete the original message or any copy of it from your computer system.
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Robertson Lee - lerobe
INET: lerobe_at_acxiom.co.uk

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

RE: SQL Lock & consistency - a great answer