Message-ID: <F001.00516208.20021210065911@fatcity.com>
Date: Tue, 10 Dec 2002 06:59:11 -0800
To: Multiple recipients of list ORACLE-L <ORACLE-L@fatcity.com>
Sender: root@fatcity.com
Reply-To: ORACLE-L@fatcity.com
Errors-To: ML-ERRORS@fatcity.com
From: "Jesse, Rich" <Rich.Jesse@qtiworld.com>
Subject: Oracle FM interpretation of locking behavior
Organization: Fat City Network Services, San Diego, California
Precedence: bulk
Mime-Version: 1.0
Content-Type: text/plain;	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

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