Обсуждение: Help with concurrent insertions.

Hi all,

I am having problems with concurrent transactions. For my application I am running the following commands -
        SELECT pointid,pointtype,pointname,createtime FROM pointtable p ,ebihistory e WHERE p.pointid = e.pointnumber AND e.flag='0'   

        After executing this query, I do an update as follows -
        UPDATE ebihistory SET flag='1' 
But inbetween the SELECT and the UPDATE query, few more tuples are added into ebihistory table with flag='0'. So the UPDATE command updates the flag field of the newly inserted tuples also. As a result I am not able to track the newly inserted tuples.

As a work around I tried to use a function which locks the table to do the above operations, but it doesn't seem to work. There is something wrong with the function that I have written.

create type PointData as(PointId integer,PointType integer,CreateTime bigint);
create or replace function PP_PointBuildInfo() returns setof PointData as '
Declare
rec PointData;
begin
        LOCK TABLE EBIHistory SHARE UPDATE EXCLUSIVE MODE;
        for rec in SELECT pointid,pointtype,pointname,createtime
                FROM pointtable p ,ebihistory e WHERE p.pointid = e.pointnumber AND e.flag=''0'' loop
                return next rec;
        end loop;
        update ebihistory set flag=''1'';
        COMMIT;
end;
' language 'plpgsql';

Could anyone help me out with this.

Regards,
Pradeep

Centuries ago, Nostradamus foresaw when Pradeepkumar.Pyatalo@honeywell.com ("Pradeepkumar, Pyatalo (IE10)") would
write:
> Could anyone help me out with this.

May I suggest doing a slightly different "interlock"?

mobile% echo $$
65425

--> At the start of your update process, determine your process's
    PID.  Let's suppose it's 65425.

--> First query:
     update ebihistory set flag = 65425 where flag = 0

    That claims all records with flag = 0 for the local process.

--> Then, to process them...

    select [whatever] from pointtable p, ebihistory e
    where p.pointid = e.pointnumber and e.flag = 65425;

--> Then, once they're done

    update ebihistory set flag = 1 where flag = 65425

If there is some possibility of processes failing, you might want to
make sure that there's no update process running, and then run the
query:

   update ebihistory set flag = 0 where flag > 1;

That draws back to 0 [unclaimed] any entries that haven't had
processing finished yet.
--
let name="cbbrowne" and tld="gmail.com" in name ^ "@" ^ tld;;
http://cbbrowne.com/info/internet.html
I am not a Church numeral!
I am a free variable!