[Twisted-Python] PyPgSQL not locking in adbapi

[Carolina Ardohain]

The functions are called with "SELECT conversation_insert(...)"
and "SELECT message_insert(...)".

FUNCTION conversation_insert(....)
BEGIN
    LOCK TABLE person IN ACCESS EXCLUSIVE MODE;
    PERSON * FROM persons WHERE id = person_id;
    IF NOT FOUND THEN
        INSERT INTO persons (id) VALUES (person_id)
    END IF;
    ...
    INSERT INTO conversations (personId) VALUES (person_id)
    ...
    RETURN;
END;

FUNCTION message_insert(....)
BEGIN
    LOCK TABLE person IN ACCESS EXCLUSIVE MODE;
    PERSON * FROM persons WHERE id = person_id;
    IF NOT FOUND THEN
        INSERT INTO persons (id) VALUES (person_id)
    END IF;
    ...
    INSERT INTO message (personId, subject, message) VALUES
(person_id, v_subject, v_message)
    ...
    RETURN;
END;



On 6/1/05, Jp Calderone <exarkun at divmod.com> wrote:
> On Wed, 1 Jun 2005 16:27:54 -0300, Carolina Ardohain <carolinaardohain at gmail.com> wrote:
> >I'm using Twisted 2.0, and pgSQL 2.4.
> >
> >I have two function in plpgsql that access the same table, I have to
> >look if one id is in the table persons, if not then add to the table.
> >I try to lock a table with "LOCK TABLE persons IN ACCESS EXCLUSIVE
> >MODE;", so no both functions try to add the same id.
> >
> >I get 'duplicate key violates unique constraint' when run the two
> >queries at the same time, if I delay one of the functions 1 sec then
> >everything is fine.
> >
> 
> What method did you use to run the LOCK statement?  Did you make sure it ran in the same transaction as the subsequent SELECT and UPDATE?
> 
> If you aren't already, I think you want to use one runInteraction() call for all three parts.
> 
> Jp
> 
> _______________________________________________
> Twisted-Python mailing list
> Twisted-Python at twistedmatrix.com
> http://twistedmatrix.com/cgi-bin/mailman/listinfo/twisted-python
>