Describe how to create explicit locks with a cursor or a table lock.
Explicit Locks using Cursor (or Table lock)
When creating procedures or other PL/SQL blocks that update data, it
is important to lock out other users during the entire process. Oracle’s default locking only locks out one row at a time
while you make the update.
Another user may be in the process of updating a row that your procedure is about to update. When this happens, your procedure
must wait until the other user’s transaction ends. This can slow down your processing. If you notice that the process takes
longer than expected to complete, you may need to prevent users from accessing rows during your process. Do this by reserving the
rows you wish to update. There are two ways to reserve (lock) rows that you wish to update:
Use a cursor that locks all the rows queried when the cursor is opened.
Use a table lock command that locks the entire table when it is executed.
This section shows you how to explicitly lock all the rows your procedure intends to update so that no users will delay the
procedure. This is done using a special variation on the cursor declaration statement and a special variation on the update
command. The graphic below shows the syntax of the CURSOR declaration and the UPDATE command.
The next graphic shows an example of creating a cursor and using the special variation of the update command.
The cursor lock avoids delays due to heavy update activity on a particular table.
Even when rows are locked exclusively, other users can read the data because ordinary queries do not impose any locks of their own and therefore do not conflict with other locks.
This section describes how to lock an entire table so that your procedure has total control of the table and other users are not
allowed to modify it while you work. This is a severe limitation on other users and should only be used when the procedure you are
developing is of high priority. An example is a procedure that balances electronic funds transfers between banks.
The graphic below shows the syntax and an example of the table lock command.
Remember, locks are released when you issue a COMMIT or a ROLLBACK command, or a DDL command, or (sometimes) when you exit the application you are in.
The next lesson wraps up this module.