[Twisted-Python] PyPgSQL not locking in adbapi
Carolina Ardohain
carolinaardohain at gmail.com
Wed Jun 1 16:23:26 MDT 2005
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
>
More information about the Twisted-Python
mailing list