Mailing List Archive: Zope: DB

Two actions in one ZSQL method = one transaction?

Index | Next | Previous | View Threaded

ken at sunward

Jun 13, 2007, 6:11 PM

Post #1 of 5 (2021 views)
Permalink

Two actions in one ZSQL method = one transaction?

I have a question (stated in three different ways) about the following ZSQL
method:

1. Will it *always* return the person_id of the person that it just
inserted?
2. Will it do this even if another user inserted another person just in
between the two SQL actions in this ZSQL method?
3. In other words: Does a ZSQL method initiate a database transaction, so
that no other user can inset another record between the two actions in this
ZSQL method?

Here's the ZSQL method:

<dtml-comment>
title: Method to add a Person
and return the id of the Person just created
connection_id: dhatabase
arguments:
first_name
middle_names
last_name
</dtml-comment>

insert into person (first_name, middle_names, last_name)
values (
<dtml-sqlvar first_name type="string">,
<dtml-sqlvar middle_names type="string">,
<dtml-sqlvar last_name type="string">
);

select person_id from person where person_id = (select max(person_id) from
person);

~ TIA
~ Ken

_______________________________________________
Zope-DB mailing list
Zope-DB [at] zope
http://mail.zope.org/mailman/listinfo/zope-db

lists at zopyx

Jun 13, 2007, 9:54 PM

Post #2 of 5 (1916 views)
Permalink

Re: Two actions in one ZSQL method = one transaction? [In reply to]

--On 13. Juni 2007 21:11:08 -0400 Ken Winter <ken [at] sunward> wrote:

> I have a question (stated in three different ways) about the following
> ZSQL method:
>
> 1. Will it *always* return the person_id of the person that it just
> inserted?

You have to answer this question your self. It depends on your database,
your isolation level and your database adapter

> 2. Will it do this even if another user inserted another person just in
> between the two SQL actions in this ZSQL method?

see above.

> 3. In other words: Does a ZSQL method initiate a database transaction, so
> that no other user can inset another record between the two actions in
> this ZSQL method?
>

This is a completely different question. *If* you database provides
transaction support and *if* your DA integrates with the transaction of Zope
*then* everything happening within one Zope request will happen within one
Zope transaction and therefore within one DB transaction. This isn't
necessarily true to databases like MySQL.

-aj

ken at sunward

Jun 14, 2007, 7:41 AM

Post #3 of 5 (1911 views)
Permalink

RE: Two actions in one ZSQL method = one transaction? [In reply to]

> -----Original Message-----
> From: Andreas Jung [mailto:lists [at] zopyx]
> Sent: Thursday, June 14, 2007 12:54 AM
> To: Ken Winter; 'Zope-DB List'
> Subject: Re: [Zope-DB] Two actions in one ZSQL method = one transaction?
...
> > Does a ZSQL method initiate a database transaction,
> so
> > that no other user can inset another record between the two actions in
> > this ZSQL method?
> >
>
> *If* you database provides
> transaction support and *if* your DA integrates with the transaction of
> Zope
> *then* everything happening within one Zope request will happen within one
> Zope transaction and therefore within one DB transaction. This isn't
> necessarily true to databases like MySQL.
>
> -aj

My database is PostgreSQL 8.x and my adapter is ZPsyocopgDA. Does this
combination provide the transaction support you're talking about?

~ Thanks
~ Ken

_______________________________________________
Zope-DB mailing list
Zope-DB [at] zope
http://mail.zope.org/mailman/listinfo/zope-db

lists at zopyx

Jun 14, 2007, 7:48 AM

Post #4 of 5 (1937 views)
Permalink

RE: Two actions in one ZSQL method = one transaction? [In reply to]

--On 14. Juni 2007 10:41:46 -0400 Ken Winter <ken [at] sunward> wrote:

>> -----Original Message-----
>> From: Andreas Jung [mailto:lists [at] zopyx]
>> Sent: Thursday, June 14, 2007 12:54 AM
>> To: Ken Winter; 'Zope-DB List'
>> Subject: Re: [Zope-DB] Two actions in one ZSQL method = one transaction?
> ...
>> > Does a ZSQL method initiate a database transaction,
>> so
>> > that no other user can inset another record between the two actions in
>> > this ZSQL method?
>> >
>>
>> *If* you database provides
>> transaction support and *if* your DA integrates with the transaction of
>> Zope
>> *then* everything happening within one Zope request will happen within
>> one Zope transaction and therefore within one DB transaction. This isn't
>> necessarily true to databases like MySQL.
>>
>> -aj
>
> My database is PostgreSQL 8.x and my adapter is ZPsyocopgDA. Does this
> combination provide the transaction support you're talking about?
>

*Of course*

-aj

ken at sunward

Jun 14, 2007, 7:50 AM

Post #5 of 5 (1928 views)
Permalink

RE: Two actions in one ZSQL method = one transaction? [In reply to]

Great! Thanks again.

> -----Original Message-----
> From: Andreas Jung [mailto:lists [at] zopyx]
> Sent: Thursday, June 14, 2007 10:48 AM
> To: Ken Winter; 'Andreas Jung'; 'Zope-DB List'
> Subject: RE: [Zope-DB] Two actions in one ZSQL method = one transaction?
>
>
>
> --On 14. Juni 2007 10:41:46 -0400 Ken Winter <ken [at] sunward> wrote:
>
> >> -----Original Message-----
> >> From: Andreas Jung [mailto:lists [at] zopyx]
> >> Sent: Thursday, June 14, 2007 12:54 AM
> >> To: Ken Winter; 'Zope-DB List'
> >> Subject: Re: [Zope-DB] Two actions in one ZSQL method = one
> transaction?
> > ...
> >> > Does a ZSQL method initiate a database transaction,
> >> so
> >> > that no other user can inset another record between the two actions
> in
> >> > this ZSQL method?
> >> >
> >>
> >> *If* you database provides
> >> transaction support and *if* your DA integrates with the transaction of
> >> Zope
> >> *then* everything happening within one Zope request will happen within
> >> one Zope transaction and therefore within one DB transaction. This
> isn't
> >> necessarily true to databases like MySQL.
> >>
> >> -aj
> >
> > My database is PostgreSQL 8.x and my adapter is ZPsyocopgDA. Does this
> > combination provide the transaction support you're talking about?
> >
>
> *Of course*
>
> -aj

_______________________________________________
Zope-DB mailing list
Zope-DB [at] zope
http://mail.zope.org/mailman/listinfo/zope-db

Index | Next | Previous | View Threaded

Interested in having your list archived? Contact Gossamer Threads