0

i have to transmit data from a table to an external system . data fetching should happen in parallel by multiple process. in order to avoid multiple process fetching same records i have used 'select for update skip locked'. each process has to fetch 25 records and do the processing .table contains a column FREE_TEXT_1 which will have initial value as 'N'. each process needs to fetch 25 records and do the processing. while processing column value will be changed to 'P' and once data is transmitted, value of column will be 'T'.

My select query looks like this

select * from table where free_Text_1 ='N' for update skip locked

[each process will fetch 25 records and then change the status of free_Text_1 = 'P' and then do a commit so that lock on the records will be removed and next process can fetch next set of 25 records]

problem here is each process is putting a lock on entire set of records with free_Text_1 ='N' so that next process has to wait for the first process to finish and then proceed thereby not achieving parallel processing . is there any way i can select 25 records each and ensure that no process will not fetch same set of records . Please help

Nibin Issac
  • 11
  • 1
  • 3
  • show the table definition that you are extracting from, what version of oracle would help too. what is the size of the table? How many records are you extracting? – kevinskio Jan 29 '16 at 15:11
  • Are you doing this in PL/SQL or some other language? We do this in both PL/SQL and C# without a problem. If you can provide some pseudocode as to how you're running the query and accessing the results, that will help a lot. – furman87 Jan 29 '16 at 15:27
  • Possible duplicate of [Oracle select for update behaviour](http://stackoverflow.com/q/5847228/266304)? You haven't said what you're using to fetch and process the rows, but the answer there may still be useful. But with 'skip locked' you shouldn't see the second process block, it should get no data back immediately; so not sure if that's your real query. Maybe you also have a rownum limit in your real one too - otherwise where is the 25-row fetch handled? – Alex Poole Jan 29 '16 at 16:30

0 Answers0