 |
|
Oracle Tips by Burleson |
Cursor For Update
A SELECT statement is a read only operation to the
database and the Oracle database never blocks a read. Even if there
are locks on rows, the database will allow a statement to read those
rows (subject to the requirement to maintain a consistent view).
Locks are placed on rows when they have been changed and a
commit/rollback has not yet ended the transaction. Until the user
changing the row has ended the transaction, the lock remains and no
other user can make changes to the locked data.
Normally, you update, insert, or delete
information using standard SQL statements.
begin
insert into store
values (select * from pubs.new_store);
end;
Sometimes you will want to select and lock the
data so that you restrict anyone else from making changes to the data
until you are finished. To retrieve and lock the data we use the
CURSOR FORUPDATE syntax.
The example below has a lot of new PL/SQL
constructs for us to examine. The function of this code is to update
the quantity column in the sales table for every order
for store_key ‘S101’. Let’s just say the author bribed us to
increase his sales record.
SQL> declare
2 cursor c12 is
3 select store_key
4 from sales
5 for update of quantity;
6 begin
7 for r_c12 in c12 loop
8 if r_c12.store_key = 'S101' then
9 update sales
10 set quantity= quantity+1
11 where current of c12;
12 end if;
13 end loop;
14 end;
15 /
The cursor is declared using the FOR UPDATE clause
and the quantity column is identified as the item that will be
updated. At line 7 the database opens the cursor which locks all the
rows in the sales table. An IF/THEN statement is used to
identify the rows (orders) that belong to the store with ‘S101’
as it’s key. Those rows are then updated in lines 9 through 11.
Notice that lines 9 through 11 are actually one
line of code. The UPDATE WHERE CURRENT OF clauseautomatically updates the row that the cursor is
currently using at that time. Because the update SQL does not have to
relocate the row to update, it is very fast. At the end of the PL/SQL
block, all the rows selected by the cursor are still locked (not just
the changed rows). The transaction stays open and the locks are held
until a rollback or commit is issued.
Notice that cursor c12 locks all the rows
in the sales table because it will update the quantity
column. This cursor could have been defined as the cursor below with
the same effect.
declare
cursor c12 is
select store_key
from sales
for update;
begin
If the cursor will join tables, then all the rows
returned by the cursor in both tables are locked.
declare
cursor c12 is
select author_last_name, book_title
from author join book_author using (author_key)
join book using (book_key)
for update;
begin
Here the cursor locks all the rows in the
author table, the book_author table and the book
table. This is because the cursor returns all the rows in each of the
three tables. If the intent is to update the book_title
column in the book table, the cursor can indicate that as shown
in the example below.
declare
cursor c12 is
select author_last_name
from author join book_author using (author_key)
join book using (book_key)
for update of book_title;
begin
Now the database will only lock the rows in the
book table because the cursor indicates that although it will
retrieve more data, it will only update the book table. This
is an important point. The database will keep all the rows locked
until the transaction ends, not the cursor is closed. As the
programmer, you want to lock as little data in the database as
possible. Always specify the columns to update in the cursor
definition to reduce unnecessary row locking.
Here are some important points to remember about
the FOR UPDATE clause:
-
Lock as few rows as possible. Locks reduce
concurrency and cause waits, slowing-down your processing.
-
Even though the cursor indicates that it is
going to change some data, it is not required to change any values.
-
Even if the cursor identifies a column to update
(such as book_title in the above example) it can still update
other columns or tables. In this case, the updates may be blocked
by locks held by other users.
-
The locks remain until the end of the
transaction (Commit or Rollback), not the closing of the cursor.
-
If another transaction has locks on the data,
the cursor open will wait (block) on those locks. There is no
restriction on the time the cursor will wait for the locks to be
released. The NOWAIT clause can be appended to the cursor
definition to tell it to return immediately (without opening the
cursor) if there are already locks on the data.
-
The WHERE CURRENT OF clause can be used for
updates or deletes. It does not make sense to insert a row at the
current cursor location.
-
The CURSOR FORUPDATE using
the WHERE CURRENT OF clause is the fastest way to update data. The
cursor will use the rowid to perform the update (or delete)
which is the fastest way to locate a specific row in an Oracle
database table.
Cursors can also be defined as variables. These
variables are known as REF CURSORS because the variable references the
actual cursor.
 |
For more details and scripts, see my new book "
Oracle
Tuning: The Definitive Reference", over 900 pages
of BC's favorite tuning tips & scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot. |
|
