RE: Lock

It's ONE lock that in this case will be for EVERY row in the table (since you do not specify a WHERE statement)

Lock Type = DML
Mode Held = Row-X (SX)

Any subsequent updates/deletes from this table will "WAIT" for your update to complete before they are allowed (inserts and selects will be unaffected).

If you specify a WHERE statement (i.e. update...where ROWNUM=1) you also get ONE lock for ONE row: Row-X (SX). Other updates on other rows will continue to process.

I think Row-X (SX) lock can be 1:1 or 1:M though that may not be technically correct.

If you specify a where statement to only update a subset of rows

Chris Taylor

"Quality is never an accident; it is always the result of intelligent effort."
-- John Ruskin (English Writer 1819-1900)

Any views and/or opinions expressed herein are my own and do not necessarily reflect the views of Ingram Industries, its affiliates, its subsidiaries or its employees.

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Paul Harrison Sent: Saturday, March 10, 2012 10:57 AM
To: oracle-l_at_freelists.org
Subject: Lock

Hello,  

I have a table with four rows. I issue the sql statement: update table set name = 'test'; this will update 4 rows. Will Oracle create 4 locks one for each row or 1 lock for the 4 rows?  

Thanks,

Paul

--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Mon Mar 12 2012 - 07:01:49 CDT