[Zope] Concurrency/Atomicity in ZSQL/ZOracleDA ?

[Casey Duncan]

This is a common operation. Most databases support a way to retrieve the 
last (or next) generated id for a table without resorting to using 
max(id)+1. I would refer to your database documentation on this.

As for concurrency, just make sure you perform both operations in a 
single database transaction. Many database adapters automatically wrap 
each method call in a transaction. So by putting both sql statements in 
one ZSQL method (separated using <dtml-var sql_delimiter>) will do what 
you want. If the DA doesn't automatically do implicit transactions for 
you then just wrap the whole thing with "begin" and "end" SQL statements 
yourself.

hth,

-Casey

Renaud Guerin wrote:
> Hello,
> 
> I have a probably simple question, but got different opinions on the matter, 
> and I'm seriously in need of a quick answer.
> 
> 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 ... (using a sequence is not an option for me for various reasons)
> 
> 2) then, INSERT with the ID previously generated ( is INSERT INTO ... VALUES 
> (<dtml-var my_id>.... the right way to do this ?)
> 
> 3) display my_id to the user for future reference.
> 
> First, I'm not sure how to do 3) : how do I pass my_id back to the original 
> DTML document (which calls the ZSQL method with dtml-call) ?
> 
> Also (main question), is there something particular to do to ensure 
> SELECT+INSERT is done in an atomic way that avoids concurrency 
> problems ?
> 
> I know Zope starts and ends transactions with HTTP requests, but I'm not sure 
> this implies the kind of read locking I need.
> 
> 
> thanks a lot!
> PS: please cc any answers.