"Ben-Nes Michael" <miki@canaan.co.il> writes:
> When I want to insert a branch to the tree I need:
> 1. select lft, rgt from the table to find where to insert
> 2. using update I open a gap in the tree ( offset all the lft, rgt above the
> location by two )
> 3. using insert I add a new branch.
> so "select for updates" seem inappropriate as I select only one row as I
> understood it lock only this row.
> Read Committed is also bad as if the second transaction will use select
> before the first transaction ran the update the select will be useless.
> so it seems that Serializable isolation is the solution, or am I wrong ?
Yeah. Use serializable mode, and be prepared to cope with "can't
serialize" errors (a retry loop around the whole transaction is the
standard answer to that).
regards, tom lane