I am having list of url in table(postgres). I want to process each url.
For that i am having processing program p1(perl). For some reason i want to run multiple instance of my program p1. so each programm select one url from table and perform operation for that url.
I have tried "select for update limit 1" statement but another instance is locked not able to select the another row from database it is in waiting state until row lock is not release by first instance.
So can anybody sugest me which is the best locktype suitable for the operation.
each url must be process by only one instance of p1
so whenever url is selected by p1. that url is locked by database and not access by other instance in any way (not for reading also) while operation is not complete. At a time another instance is able to select another url from table and perform operation.
So multiple instance can run simultaniously and perform operation for url.
I suggest that you consider very carefully what you hope to achieve by running multiple copies of the program. It is very easy to cause far more overhead in such a scenario than you could ever hope to gain.
If you are determined to do this, then each program must issue its query, get the result, somehow flag the row as "being processed by someone," and immediately close the query; thus releasing the lock so that the next instance can use it.
But... a multi-threaded program would work much better in the same instance. Hence my advice: consider carefully what the computer hardware is being asked to do in each scenario; what the actual reasons for time-improvement are expected to be; and whether, in each situation, such improvements could actually occur.