Re: [POSTGRESQL] LOCKING A ROW

"Jesus Contreras" <jcontreras@isoco.com> writes:
>                 ResultSet rs = db.doSelect("SELECT * FROM index FOR UPDATE");
>                 int id=0;
>                 if (rs.next()) {
>                     id = rs.getInt("sec_id");
>                     id++;
>                     db.doUpdate("UPDATE index SET sec_id = " + id);
>                     db.doUpdate("INSERT INTO secuence VALUES (" + id + ",
> 'numero " + id + "')");
>                 }

This should work as long as you execute all three SQL commands in a
single transaction; otherwise the FOR UPDATE lock isn't being held long
enough to do what you need.  I'm not sure of the behavior of doSelect
and doUpdate, but I wonder whether they aren't issuing each command as a
separate transaction.  I'd expect to see a "begin" kind of operation at
the top of this fragment, and a "commit" kind of operation at the bottom...

            regards, tom lane