Canned Beans Sparked In Microwave

New functionality for the SELECT FOR UPDATE (SKIP LOCKED)

On many occasions I had the opportunity to review PL / SQL code where the program, among other things, the "branding" of records by a flag . These marks generally are implemented by modifying a column (eg state) which indicates at what stage of the login process and to avoid overlapping with other parallel sessions who are doing the same.

The most common way I have been described for the op is using "SELECT ... FOR UPDATE NOWAIT" registry of the master table to ensure that other sessions can not process the register. Once the registration is processed, the other sessions will take the next record available for processing. If not required to process an order, this approach undermines the real parallelism. This is because while processing a session will log the other must wait for commitee to process the next record. This happens because Oracle does not Lockee the selects, then even if the process is processing a given record, you can not "skip" and take the next, but returns an error that the resource is being used (ORA-00054 resource busy and obtained with NOWAIT).
11g
From documented an option (so I could test since 9i but there was no documented) very interesting to deal with this type of processing, which is actually an extension of the syntax for update to allow precisely to process in much greater degree of parallelism and thus enable each session "skip" the record processing and take the next available for processing. This significantly improves the overall processing time, as it may raise parallel sessions n minimizing the interdependence between them.

Below we show an example: I

a table T and to insert 100 records:

 
 create table t (id int, 
 state char (1), 
 date date, 
 amount number (8,2)) 
 
 
 
 insert into t select rownum, 
 'C', sysdate + dbms_random.value 
 (-50.50), 
 dbms_random.value (1.1000000) 
 from dual connect 
 by rownum <= 100  
 

Change the state of 10 rows, chosen at random. The lanes will be in state 'P', assuming that the state 'P' is available for processing.

 
 t_v create view as select id 
 
 
 from t order by dbms_random.value 
 September 
 
 update t state = 'P' 
 where id in (select id from t_v) 
 <= 10  
 
 
 and rownum select * from t WHERE status = 'P'; E 
 
 ID DATE AMOUNT 
 ---------- - --------- ---------- 
 1 P 20-DEC-10 888292.36 27 P 09 
 MAR-11 845864.47 
 39 P 19-JAN-11 583901.49 
 52 P 23-FEB-11 157817.12 
 62 P 05-JAN-11 680744.2 
 63 P 19-JAN-11 679375.69 73 
 P 20-JAN-11 750069.3 
 87 P 26-FEB-11 783555.02 96 
 P 13-DEC-10 973668.87 
 100 P 28-FEB-11 756671.07 
 

In a console pl execute the next block to take the next record to process (Session 1 )

 
 
 declare cursor is 
 l_cur select * from t 
 
 WHERE status = 'P' 
 skip locked for update nowait; 
 l_rec l_cur% ROWTYPE; 
 
 begin open l_cur; 
 l_cur fetch Into l_rec; 
 -  
 dbms_output.put_line (l_rec.id) 
 end; 
 
  Result: 1  
 

In another session (Session 2) execute the same block pl:

 
 
 declare cursor is 
 l_cur select * from t 
 
 WHERE status = 'P' for update nowait 
 skip locked; 
 l_rec l_cur% ROWTYPE; begin open 
 
 l_cur; 
 l_cur fetch Into l_rec; 
 -  
 dbms_output.put_line (l_rec.id) 
 end; 
 
  Result: 27  
 

Session 1 took the record with id = 1 and Session 2 took the record with id = 27, which are the first and second respectively in the list above. Clearly not commiteo nothing yet Session 2 was able to take a new registration process while session 1 was processed. With conventional select for update session 2 code had failed and should be retried until the session 1 to release the record (commit / rollback) with id = 1 and thus allowed to pass to the next.