Re: Locks rows

"Pablo Sanchez" <pablo_at_dev.null> wrote in message news:Xns93098FEEA88D8pingottpingottbah_at_216.166.71.233...
> "Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in
> news:AzXW9.28933$jM5.74796_at_newsfeeds.bigpond.com:
>
> > No problem. What you do is uninstall Oracle completely. And then you
> > replace it with a product called SQL Server.
> >
> > SQL Server is well-known for the way somebody doing an update to a row
> > causes all other people who are hoping to select from that table to
> > grind to a halt.
> >
> > Incidentally, I'm not joking when I say this
>
> The only time select's 'grind to a halt' is when the coder locked the
> row and put in the transaction a prompt: "how's the weather?"
>
> > But in Oracle, we NEVER do this. Readers don't block writers, and
> > writers don't block readers. And readers most certainly never block
> > other readers!! And there is NO way around that. You can do a select
> > which prevents anyone else from modifying a record (called a
> > 'select...for update'), but that won't stop me *selecting* that
> > record. In Oracle, selects never take locks (except for the special
> > case of a 'for update' one) and so they can't themselves be in
> > competition for a lock taken by someone else updating -and hence a
> > select can never be 'locked out'.
>
> To implement what Francky may need though, he may need to always use
> "select ... for update" so that in his situation (some serialized
> activity) he blocks until the row is completed. Correct?

Who can say? His post says he wants to stop two people simultaneously *selecting*. The select for update stops a second person *locking*. And since selects never require locks, that won't stop the second person doing a regular select.

I suppose that if you were writing an application to prevent two simultaneous selects, you could code it as a select for update. That way, when user2 tries to click the 'select' button, the app. actually tries to lock the rows, fails (because user1 already has them locked), and therefore ends up not displaying the records.

But that's an application coding issue. If he's talking about selecting via SQL*Plus, then the select for update trick isn't going to work, no?

Regards
HJR Received on Mon Jan 20 2003 - 15:23:55 CST