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:

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.