[Zope-DB] Are transactions/atomicity implicit in ZSQL+ Oracle ?

[kapil thangavelu]

On Thursday 21 March 2002 03:06 am, rguerin@free.fr wrote:
> En r=E9ponse =E0 kapil thangavelu <kthangavelu@earthlink.net>:
> > > I need to make a ZSQL method that inserts data in a table this way:
> > > 1) first, generate a new ID with something like SELECT MAX(ID)+1 AS
> >
> > my_id
> >
> > > FROM ...
> > > 2) then, INSERT with the ID previously generated ( is INSERT INTO
> > > VALUES (<dtml-var my_id>.... the right way to do this ?)
> >
> > it would be much better to use a sequence. the above is only safe in =
a
> > transaction serialized mode.
>
> Unfortunately I can't modify the db structure or even add simple object=
s.
> My question is precisely this: is there explicit LOCKing to do to ensur=
e
> serialization or does Zope do it automatically for all SQL statements i=
n a
> single ZSQL method?
>

zope doesn't do any locking per say, it tries to handle transaction=20
integration to make all methods invoked during a request happen=20
transactionally.

> > > Also (main question), is there something particular to do to ensure
> > > SELECT+INSERT is done in a single transaction, thus avoiding
> >
> > concurrency
> >
> > > problems ?
> >
> > zope's integration with databases depends on whats supported by the
> > database
> > adapter. if the adapter supports transactions than zope will commit
> > transactions involving any changed persistent objects within zope and
> > the dbs
> > at the end of a request.
>
> By 'request' you mean http request  ?

yes.

> If the Oracle DA supports transactions, which I guess it does, am I rig=
ht
> to assume that the 2 SQL statements inside my ZSQL method are an atomic
> operation ?

you can assume transactionality. zope's transaction boundary is at the en=
d of=20
an http request where it will attempt to commit to all db adapters involv=
ed=20
during a request. which means all zsql methods involved in a http request=
 are=20
associated with a single transaction. of course this is not true if you a=
re=20
manually manipulating the transaction.

atomicity, depends on what you're doing and how you've set up the databas=
e=20
connection/env, specifically its transaction isolation level. =20

hth

-k=20