Batch Running On Multiple Servers

I have a batch application which simply consists of these steps.

• Select all the records from the table where the update-fl开发者_StackOverflowag column is null
• Process the selected information
• Mark the record as updated

I'm using Oracle 10g and what I'm trying to do is to be able to execute this application on multiple servers concurrently. However, I'm concerned about the lock mechanism of Oracle. When the first instance of my application running on the first server selects a record, the other instance running on the second server should not be able to select it.

Is it suitable to use "select for update" for this kind of purpose? If I use "select for update skip locked" pattern, does the second instance of the application silently skip those records locked by the first instance previously and select those other rows? Or, does it wait until the locked rows are released?

Any advice and suggestions will be greatly appreciated

---

My collegue Rob wrote a nice article about this Parallellism in a skip locked scenario So yes, it works, no, it wont help you. FWIW: it might be smarter to change the algoritm a little, give the update flag a value, an incremental number to help the selection processes that handle the new data, and update to NULL when ready. The advantage here is two fold

1. reduces the index size
2. it makes parallism easier to implement because processes can now all select ther 'own' rows.

I hope it helps.

---

I use select for update skip locked and it works fine. Yes, later sessions silently skip records locked by earlier sessions and select/lock other rows. Be sure there is no other common data modified by parallel processing sessions or you get stuck in another bottleneck.

declare 
  my_limit constant number default 1000;
  cursor cRecords is
    select primary_key
    from processed_table t
    where t.update_flag is null
    for update skip locked;
  type t_cRecords is table of cRecords%rowtype;
  tRecords t_cRecords;
begin
  open cRecords;
  while true loop
    -- Select all the records from the table where the update-flag column is null
    fetch cRecords bulk collect into tRecords limit my_limit;
    -- Process the selected information
    -- ...
    -- Mark the record as updated
    forall i in tRecords.first..tRecords.last
      update processed_table
        set update_flag = 'PROCESSED'
      where primary_key = tRecords(i).primary_key;
    --
    exit when tRecords.count < my_limit;
  end loop;
end;

By the way, I don't confirm Parallellism in a skip locked scenario - on my site there is from 4 to one second speed up, using scripts from rwijk.