Mailing List Archive: Two actions in one ZSQL method = one transaction?

Two actions in one ZSQL method = one transaction?

Jun 13, 2007, 6:11 PM

Post #1 of 5 (4022 views)

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@zope.org
http://mail.zope.org/mailman/listinfo/zope-db

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

lists at zopyx

Jun 13, 2007, 9:54 PM

Post #2 of 5 (3876 views)

Permalink

--On 13. Juni 2007 21:11:08 -0400 Ken Winter <ken@sunward.org> 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

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

ken at sunward

Jun 14, 2007, 7:41 AM

Post #3 of 5 (3869 views)

Permalink

> -----Original Message-----
> From: Andreas Jung [mailto:lists@zopyx.com]
> 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@zope.org
http://mail.zope.org/mailman/listinfo/zope-db

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

lists at zopyx

Jun 14, 2007, 7:48 AM

Post #4 of 5 (3904 views)

Permalink

--On 14. Juni 2007 10:41:46 -0400 Ken Winter <ken@sunward.org> wrote:

>> -----Original Message-----
>> From: Andreas Jung [mailto:lists@zopyx.com]
>> 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

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

ken at sunward

Jun 14, 2007, 7:50 AM

Post #5 of 5 (3887 views)

Permalink

Great! Thanks again.

> -----Original Message-----
> From: Andreas Jung [mailto:lists@zopyx.com]
> 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@sunward.org> wrote:
>
> >> -----Original Message-----
> >> From: Andreas Jung [mailto:lists@zopyx.com]
> >> 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@zope.org
http://mail.zope.org/mailman/listinfo/zope-db