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