CODEWITHSUNDEEP
javapostgresqltransactionspostgresql-9.1postgresql-9.2
09Q71AO534
09Q71AO534

Reputation: 4450

How to rectify the conflicts which occur at concurrent transactions in PostgreSQL

I have the function which checks the maximum value of pid and then inserts into the database.

But it raises a conflict while i have concurrent transactions!

So how can i avoid this conflict!

create table tablea(
  sno serial,
  pid integer,
  ppid integer,
  pname text
  );

--This is the function checks the maximum value of pid and then inserts into the database.

CREATE OR REPLACE FUNCTION insert_details(_pname text)
RETURNS void AS
$BODY$
declare
    _pid int;
begin
   _pid := (select MAX(pid) from tablea);
   _pid := coalesce(_pid, 0) + 1;

   insert into tablea(pid,pname)
   values(_pid,_pname);
end;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

--(Sub Party)This is the function checks the maximum value of ppid and then inserts into the database.(sub-Party)

CREATE OR REPLACE FUNCTION insert_details(_pid int,_pname text)
RETURNS void AS
$BODY$
declare
        _ppid int;
begin
   _ppid := (select MAX(ppid) from tablea where pid=_pid);
   _ppid := coalesce(_ppid, 0) + 1;

   insert into tablea(pid,ppid,pname)
   values (_pid,_ppid,_pname);
end;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

My Requirement : When i hit a submit button from the front end then my field(pname) should be inserted into the tablea,by incrementing the pid by +1, so i cant remove my pid from the tablea,and pid is kept integer so that i can insert another record by the same pid & the flow is as same as with ppid too..

So should i change my table structure or else any other way to rectify the conflicts in concurrent transactions.

This is How my Whole concept is all about sql fiddle demo

Upvotes: 2

Views: 146

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657617

On way to avoid conflicts permanently and effectively would be to drop the redundant pid from your table. Use sno instead for all purposes. And disallow direct input for the column, so that the default from the attached sequence cannot be circumvented. sno can have gaps, that's by design. But it works flawlessly for a unique column under concurrent load. And later requests (starting later) get a higher serial number. That's what a serial column is good for.

If you really need a gapless "pid", you can emulate one with row_number():

SELECT *, row_number() OVER (ORDER BY sno) AS pid FROM tablea

Now, the synthesized pid is not stable if rows can be deleted. But what's the point of this exercise if there can be gaps in the numbering after all?

Upvotes: 1

roman
roman

Reputation: 117455

try something like this to reduce chance of conflict:

insert into tablea(pid,pname)
values(coalesce((select MAX(pid) from tablea), 0) + 1,_pname);

To avoid conflict completely you can also set transaction isolation level to serializable (read about it first to consider pros and cons):

set transaction isolation level serializable;

insert into tablea(pid,pname)
values(coalesce((select MAX(pid) from tablea), 0) + 1,_pname);

Upvotes: 0

Related Questions