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" <[EMAIL PROTECTED]> 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 > [EMAIL PROTECTED] Quad/Tech > International, Sussex, WI USA > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Jesse, Rich > INET: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
