Re: [POSTGRESQL] LOCKING A ROW

"Jesus Contreras" <jcontreras(at)isoco(dot)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