Re: serialization errors

On Tuesday 28 January 2003 23:59, Tom Lane wrote:
> Ryan VanderBijl <rvbijl@vanderbijlfamily.com> writes:
> > For adding a new node, I basically do this:
> >    insert into tree_node(parent_id, node_order)
> >    values(1, (select max(node_order)+1 from tree_node where parent_id =
> > 1) );

> That "select max()+1" makes me itch ;-) ... that's as perfect a recipe
> for concurrency problems as I can imagine.

For concurrent transactions, select max (node_order) + 1 will return the
same value for all concurrent transactions. The first transaction to
insert the value wins. Any other concurrent transaction will abort with
a duplicate key error.

Do this instead:

insert into tree_node (parent_id, node_order)
values (1, 0);

update tree_node
   set node_order =
        (select max (node_order) + 1
           from tree_node as tn1
          where tn1.parent_id = parent_id);

> At first glance it seems that all this is doing is assigning sequential
> node_order values to the children of any particular parent.  Is it
> really necessary that those node_order values be consecutive?  If they
> only need to be strictly increasing, you could use a sequence to
> generate them.  (Actually, I think you could dispense with node_order
> entirely, and use the node_id field instead.)
>
> In any case, I'd suggest some careful thought about what your data
> structure really is, and how you could avoid creating serialization
> bottlenecks like this one.

In my application, I use select max to generate a sequential value for
concatenated key such as:

create table order_item (
    firm_id integer not null references (firm),
    patient_id integer not null,
    prescription_id integer not null,
  ... -- script data
    primary key (firm_id, patient_id, prescription_id),
    foreign key (firm_id, patient_id) references (patient)
);


Creating a prescription id by select max + 1 in this case does not cause
a bottleneck, since it will only block other transactions that wish to
insert a prescirption for this particular patient. Not common in my
application.

If you are going to be inserting into trees frequently, you are more
likely to have a bottleneck, espcecially if different processes want to
insert into the same tree.

Alan Gutierrez - ajglist@izzy.net
http://khtml-win32.sourceforge.net/ - KHTML on Windows