Q about transactions

Hi Again.

Read it all but still im not really sure of what todo, hope the list can
clear some thing up :)

I implanted Joe Celko Nested set of tree.

example of small tree:

name | lft | rgt
top - 1,8
b1 - 2,5
b1.1 - 3,4
b2 6,7

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 ?

I think that the best solution is that while I run the first function the
second function will wait until the first one end.

What is the list opinion ?

Thanks in Advance :)

--------------------------
Canaan Surfing Ltd.
Internet Service Providers
Ben-Nes Michael - Manager
Tel: 972-4-6991122
Fax: 972-4-6990098
http://sites.canaan.co.il
--------------------------
----- Original Message -----
From: "Bruno Wolff III" <bruno@wolff.to>
To: "Ben-Nes Michael" <miki@canaan.co.il>
Cc: "postgresql" <pgsql-general@postgresql.org>
Sent: Sunday, April 13, 2003 4:21 PM
Subject: Re: [GENERAL] Q about transactions

On Sun, Apr 13, 2003 at 16:42:59 +0200,
Ben-Nes Michael <miki@canaan.co.il> wrote:

Hi All

What happens when two people try to update the same table simultaneously

?

The second one wait until the first one finish ?

Also what happens when the transaction is more complex then one update ?
I mean first I read some tables and then start to update / delete /

insert

other tables ( using function for example )

will the second action will wait until the function transaction will end

?

"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

Hi Again, I thought about it a while and I still filling somewhat vague.

If in one transaction I start Serial mode and the other I start later in
Read Committed mode, what will happen if the Serializable transaction will
change some of the rows that the Read Committed is relay on ?

Thanks in Advance.

"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

--------------------------
Canaan Surfing Ltd.
Internet Service Providers
Ben-Nes Michael - Manager
Tel: 972-4-6991122
Fax: 972-4-6990098
http://sites.canaan.co.il
--------------------------