Re: SQL Lock & consistency - a great answer

> I have to share this post I got on another list.

Excuse me but what List is this ?
Thanks

Robert Chin

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Tuesday, December 11, 2001 3:10 PM

> 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 read-
> consistency: 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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Robert Chin
  INET: chinman_at_optonline.net

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).