PL/SQL Transactions   «Prev  Next»

Lesson 5 Explicit locks using a cursor or a table lock
Objective 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:
  1. Use a cursor that locks all the rows queried when the cursor is opened.
  2. Use a table lock command that locks the entire table when it is executed.

Cursor lock

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.

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.

Table lock

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.

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.