Re: serialization errors

On Tuesday 28 January 2003 23:59, Tom Lane wrote:
> Ryan VanderBijl <rvbijl(at)vanderbijlfamily(dot)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(at)izzy(dot)net
http://khtml-win32.sourceforge.net/ - KHTML on Windows