Page 636
Batch processing jobs inherently selects a bulk of records from a table on which processing is to be done, performs some processing on them, and then updates them back with new values. It's very important to lock these records before the program does the processing, because the batch job could select old values from the table while another session comes and updates the record. Therefore, when the batch job has to update the results back to the table at the end of processing, it will find that another session has updated the record already. To prevent this from happening, the records are locked in advance while selecting itself. The pseudo-PL/SQL code for this would look like Listing 25.3.
Listing 25.3Batch Processing Pseudo CodeBegin Declare c_emp for Select * from emp <Where Clause > For Update; v_emp c_emp%RowType; Begin Open c_emp; Loop; Begin Fetch c_emp into v_emp; --Do some processing -- Update c_emp Set sal = X, incr = Y Where emp_id = v_emp.emp_id; End; End Loop; Commit; Close c_emp; End ; End ;
In this code, notice that the entire set of records to be processed is locked; it needs to
be locked before processing so that another session cannot update these records while they
are still being processed. The disadvantage in this code is that even after the records get
processed, they are locked until all the records are processed. If the processing time is very
large, the time for which these records are locked will prevent concurrent access to these by
any other session. Depending on the demand for these records at the given point of time,
this locking strategy may prove to be a performance bottleneck.
An alternative code, which could be used to combat this situation, is given in Listing 25.4.
Page 637
Listing 25.4Batch Processing Pseudo Code for Maximum ConcurrencyBegin Declare c_emp for Select rowid from emp <Where Clause >; v_emp c_emp%RowType; r_emp emp%RowType; Begin Open c_emp; Loop; Begin Fetch c_emp into v_emp; Select * Into r_emp From emp Where rowid = v_emp.rowid; <Where Clause > For Update; --Do some processing -- Update emp Set sal = X, incr = Y Where rowid = v_emp.rowid <Where Clause >; Commit; End; End Loop; Close c_emp; End ; End ;
Notice in this Listing 25.4 that the outer cursor is just selecting all the rows from the table.
The outer cursor feeds values to the inner select, and the inner select will lock only one row at
a time with the for update clause. The commit in Listing 25.4 is now moved inside the
loop; therefore, after every row is updated, the commit is issued, thus maximizing concurrent
access to the table for other users. You may be tempted to move the commit in the script in
Listing 25.3 inside the loop. Unfortunately, in Oracle 6.7, if this is done the cursor also gets
closed, resulting in termination of the PL/SQL loop. All the relevant rows in the table are locked
as soon as the cursor is opened by the open clause. When the commit is issued, the cursor loses
its context and will display the following message:
ORA-01002: fetch out of sequence
Therefore it's necessary to code in the style shown previously.
Page 638
Care should be taken when using tables with row-level triggers on them. For example, if a table has a row-level trigger such that when a record of the table is updated the trigger on the table may cause other tables to be updated. This can cause a lot of locks to be implicitly acquired on the system by a single update statement.
User education can help in avoiding locks. A number of times I have noticed users who think that their process has hung for some reason switch off their terminal in order to reset the process (particularly in client/server applications). Another category of users is those who leave the terminal logged on when taking a lunch break or when leaving the desk. This could lock some tables in the system and could cause other users to wait behind this user. Users need to be made to understand that switching off a terminal does not reset the process, and the process is still active on the server side. Similarly, users should be encouraged to log off inactive terminals. This can help to increase concurrency and keep users from locking up.
Oracle implements locks using various techniques mentioned in the previous section. Using latches is one of them. Latches, unlike locks, are acquired internally and released by Oracle. Latches cannot be acquired explicitly by issuing a statement. Basically, latches are used to control access to shared code paths. A latch is a type of lock that can be very quickly acquired or freed. Latches are used to prevent more than one process from executing the same piece of code at a given time.
Latches are used to control access to shared structures. Latches are implemented using semaphores at the operating system level. Latches are locks that are held for a very small amount of time.
Before a process gets access to a shared structure protected by a latch, it has to
first acquire the latch. This latch could currently be free (that is, no other process is accessing the latch),
in which case the process gets the latch immediately. This process will hold the latch for
the period of time it requires and then relinquish the latch. This is the simplest and the most
typical case.
Consider the case in which a particular latch is already acquired by a process. Another process trying to acquire the latch has two options depending on the type of latch it is trying to acquire. Associated with latches there are two types:
If the process tries to acquire the latch in immediate
mode, which is already held by another process, the process will not wait to acquire the latch if the latch is not available immediately.
It will continue by taking a different action.