Postgres Pro
Downloads
Home   >   mailing lists

Thread: Q about transactions

Q about transactions

From
"Ben-Nes Michael"
Date:
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 ?

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
--------------------------

Re: Q about transactions

From
Bruno Wolff III
Date:
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 ?

Read: http://developer.postgresql.org/docs/postgres/mvcc.html

Re: Q about transactions

From
"Ben-Nes Michael"
Date:
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
?
>
> Read: http://developer.postgresql.org/docs/postgres/mvcc.html
>

Re: Q about transactions

From
Tom Lane
Date:
"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

Re: Q about transactions

From
"Ben-Nes Michael"
Date:
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
--------------------------