[Zope] Concurrency/Atomicity in ZSQL/ZOracleDA ?

[Matthew T. Kromer]

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.
>

Well, the "best" way to do this is with a stored procedure, and using an 
Oracle sequence.

e.g.

CREATE SEQUENCE MY_SEQUENCE;

CREATE OR REPLACE PROCEDURE MY_INSERT (
        VAL1 IN INTEGER,
        VAL2 IN VARCHAR2,
        ID OUT INTEGER    -- the important bit
        ) AS
        BEGIN
            SELECT NEXTVAL(MY_SEQUENCE) INTO ID FROM DUAL;  -- I think
            INSERT INTO MY_TABLE (VAL1, VAL2, ID) VALUES (VAL1, VAL2, ID);
       END MY_INSERT;
/

Be warned that I did that off the top of my head without looking at the 
references -- I may have goofed the syntax.

With something like that, you can use a DCOracle2 Stored Procedure to 
invoke it, the OUT parameter is the id that was created, and so will be 
returned from the stored procedure.



-- 
Matt Kromer
Zope Corporation  http://www.zope.com/