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

[Stuart Bishop]

On Friday, March 22, 2002, at 01:47  AM, Renaud Gu=E9rin wrote:
> Another question: would atomicity be better insured if I nested the=20
> SELECT
> statement inside the INSERT ? (ie: INSERT INTO table VALUES=20
> (SELECT ...., ....)

The correct way of doing it is to use a sequence to generate the key, as
Oracle guarantees that different transactions will not get the same next=20=

value.
Using MAX(foo)+1 has no such guarantees, as another process may issue =
the
same query simultaneously (in which case one of them will fail if the=20
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 :-)

--
Stuart Bishop <zen@shangri-la.dropbear.id.au>
http://shangri-la.dropbear.id.au/