I searched for a long time for a response on how correctly use the database lock feature to create a job distribution system. Also I hate programs that pools databases every few seconds. Than I noticed that postgresql have a notify feature that inform clients when a table change. Beautiful!! why not to use it? It also works with db server behind firewalls because connection is initiated from the client.
I think that the correct method to use is described by tom lane in this mail http://archives.postgresql.org/pgsql-general/2003-05/msg00351.php First of this mail I used this method: http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&threadm=002b01c2d6ca%24ac ceff60%240100a8c0%40kodunet.ee&rnum=3&prev=/groups%3Fhl%3Den%26lr%3D%26ie%3D ISO-8859-1%26q%3D%2522select%2Bfor%2Bupdate%2522%2B%2B%252B%2522limit%2B1%25 22%26meta%3Dgroup%253Dcomp.databases.postgresql.* Every concurrent process listen for a notify on a working table. A new job arrives on a writer table, and is copied by a trigger on a working table as described by tom lane and others. Also clients are informed of table change with a notify, so you have not to poll database. At this point every clients make a select of a new record, and simply try to update this record. I notice that after an update I can query if (atoi(PQcmdTuples(res)) !=0) And see if it returns a positive value I can process the record. Now what is the difference between this method //////////////////////////////////////////////////////////////////////////// My poor method 1 ) select id from readertable where owner = 0 limit 1 2) update readertable set owner = $me where (id = $id and owner=0); 3) if (atoi(PQcmdTuples(res)) !=0) then process record and method described by tom lane ? /////////////////////////////////////////////////////////////////// -------------------------------------------------- Tom's Lane method 1. When idle, you try to reserve a job like so: BEGIN; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; SELECT job_id, ... FROM job_table WHERE processor_id = 0 LIMIT 1 FOR UPDATE; The SELECT has three possible outcomes: 2: One row is returned. You do UPDATE job_table SET processor_id = $me WHERE job_id = $jobid; COMMIT; and then go about executing the job. When done, delete the row from job_table and try to get another one. 2b: No row is returned: no jobs are pending. Commit your transaction, sleep for an appropriate delay period, and try again. 2c: You get a "can't serialize" failure ......... ----------------------------------------------- Multiple concurrent updates on a table can generate a table lock so that clients must wait for a long time ? Making only 1 update is the correct solution , but who can explain this ? And what exactly do select for update? Suppose now that many jobs are sumbitted in the same time. Now with the second method different jobs are selected by different clients with only 1 select ? Why postgresql does not support update table LIMIT 1 ? ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
