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

[kapil thangavelu]

On Thursday 21 March 2002 08:45 pm, Stuart Bishop wrote:
> On Friday, March 22, 2002, at 01:47  AM, Renaud Gu=E9rin wrote:
> > Another question: would atomicity be better insured if I nested the
> > SELECT
> > statement inside the INSERT ? (ie: INSERT INTO table VALUES
> > (SELECT ...., ....)
>
> The correct way of doing it is to use a sequence to generate the key, a=
s
> Oracle guarantees that different transactions will not get the same nex=
t
> value.
> Using MAX(foo)+1 has no such guarantees, as another process may issue t=
he
> same query simultaneously (in which case one of them will fail if the
> column is
> constrained as unique). It may be possible to do something like:
> 	select max(foo) from bar for update
>       <dtml-var sql_delimiter>
>       insert into bar values (foo+1)
>
>       or you might have to do:
> 	select foo from bar for update
>       <dtml-var sql_delimiter>
>       select max(foo) from bar
>       <dtml-var sql_delimiter>
>       insert into bar values (foo+1)
>
> The 'for update' locks the requested rows so other queries will block
> until the locking process commits or rolls back. But the best way is
> to use sequences for the purpose they were designed for :-)

will a for update even work here?

my understanding is that its a row locking facility, but since max is an=20
aggregate function, will this in turn automajically lock the table, if no=
t=20
then this won't work. =20

curious,

kapil