Re: Locks rows

From: Niall Litchfield <n-litchfield_at_audit-commission.gov.uk>
Date: Tue, 21 Jan 2003 10:05:49 -0000
Message-ID: <3e2d1b7e$0$234$ed9e5944@reading.news.pipex.net>

"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:sg6X9.29286$jM5.75377_at_newsfeeds.bigpond.com...
> > On a related note, plz help me understand something that I've been
> > meaning to figure out but since I have your ear (okay, eyes really!)
> > I'll run it by ya .. Oracle is tracking the 'select for update' in the
> > data block (INIT TRANS right?). Is that information actually flushed
> > to the redo log or are INIT TRANS in-memory buckets set aside to track
> > the select for updates?
>
> Damn good question. Let me slightly re-phrase it this way: does a 'select
> for update' generate redo?
>
> My best guess is that no it doesn't, since after an instance recovery you
> would not expect certain rows to be locked. But, how to prove this? As I
> say, good question, and a test will be forthcoming.... tomorrow.

I think that this proves that it does

Session 1
select * from tab1 for update;
<snip results>
29544 rows selected.

Session 2
SQL> select START_UBAFIL, START_UBABLK
  2 , USED_UBLK ,used_urec from v$transaction;

START_UBAFIL START_UBABLK USED_UBLK USED_UREC ------------ ------------ ---------- ----------

           2 6478 241 29544

No other user sessions on the db, and you have the same number of used undo records as you do rows my transaction is interested in.

Niall Litchfield
Oracle DBA
Audit Commission UK

---

Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer

---

Received on Tue Jan 21 2003 - 04:05:49 CST