If I understand what you are asking, a simple test
will tell you.
>>> Session A
SQL> create table t2 (x number);
Table created.
SQL> insert into t2 values (1);
1 row created.
SQL> insert into t2 values (2);
1 row created.
SQL> commit;
Commit complete.
SQL> update t2 set x=4 where x=1;
1 row updated.
SQL>
Notice No commit;
>>>>>>> Session B
SQL> update t2 set x=4 where x=2;
1 row updated.
SQL> update t2 set x=4 where x=1;
>>> this waits for the commit/rollback in session A
Robert Pegram
Oracle Certified DBA (8i,9i)
- "Jesse, Rich" <Rich.Jesse_at_qtiworld.com> wrote:
> Hey all,
>
> After converting three columns on a multi-million
> row table from NULLs to
> SPACEs, the devs found one other program that pops
> NULLs into those columns
> (sigh). So, with only 150 rows needing updating, my
> knee-jerk reaction was
> to do a simple:
>
> UPDATE mytable
> SET mcol1 = ' '
> WHERE mcol1 IS NULL;
>
> ...and repeat for "mcol2" and "mcol3". Since this
> is a heavy table for us
> (Time/Attendance), I'm wondering about locking,
> since the UPDATEs won't be
> using an index because of the "IS NULL". When I
> RTFM for 8.1.7, I found
> this:
>
> ---
> The locking characteristics of INSERT, UPDATE,
> DELETE, and SELECT ... FOR
> UPDATE statements are as follows:
>
> The transaction that contains a DML statement
> acquires exclusive row locks
> on the rows modified by the statement. Other
> transactions cannot update or
> delete the locked rows until the locking transaction
> either commits or rolls
> back.
>
> The transaction that contains a DML statement does
> not need to acquire row
> locks on any rows selected by a subquery or an
> implicit query, such as a
> query in a WHERE clause. A subquery or implicit
> query in a DML statement is
> guaranteed to be consistent as of the start of the
> query and does not see
> the effects of the DML statement it is part of.
>
> A query in a transaction can see the changes made by
> previous DML statements
> in the same transaction, but cannot see the changes
> of other transactions
> begun after its own transaction.
>
> In addition to the necessary exclusive row locks, a
> transaction that
> contains a DML statement acquires at least a row
> exclusive table lock on the
> table that contains the affected rows. If the
> containing transaction already
> holds a share, share row exclusive, or exclusive
> table lock for that table,
> the row exclusive table lock is not acquired. If the
> containing transaction
> already holds a row share table lock, Oracle
> automatically converts this
> lock to a row exclusive table lock.
> ---
>
> To me, this says that the row locks will only be
> placed on the affected rows
> and not every row in the table, in addition to the
> RX lock on the table. Is
> this correct? I guess I'm looking for evidence that
> I could or could not
> update this table during the day.
>
> Thanks!
>
> Rich Jesse
>
> Rich Jesse System/Database
> Administrator
> Rich.Jesse_at_qtiworld.com Quad/Tech
> International, Sussex, WI USA
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: Jesse, Rich
> INET: Rich.Jesse_at_qtiworld.com
>
> Fat City Network Services -- 858-538-5051
> http://www.fatcity.com
> San Diego, California -- Mailing list and web
> hosting services
>
> 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).
>
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Robert Pegram
INET: pegramrg_at_yahoo.com
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
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).
Received on Tue Dec 10 2002 - 12:35:46 CST
