[POSTGRESQL] LOCKING A ROW

Hi there

We have developed a multiuser web application running over postgreSQL
database. Everything went right till several users access simultaneously. To
lock a table row we use SELECT FOR UPDATE statement over an index table. In
this table we keep indexes of all entities of the data model. When accessing
to consult the last with the SELECT FOR UPDATE statement we obtain
(sometimes) the same last index for several users, and due to this an error:

ERROR: Cannot insert a duplicate key into unique index secuence_id_key

We also have tried with LOCK statement but since it locks just within a
transaction, no positive solution was obtained.

We are novice to postgres database and concurrent application, and the only
solution we have by now is to lock the access on the software side, but we
would appreciate any help that would allow us to LOCK A ROW to prevent from
parallel accesses outside a transaction.

Thanks in advance,
J. Contreras

POSTGRES: database we have tried:
* postgres runnig on cygwin: psql (PostgreSQL) 7.1.2 contains readline,
history, multibyte support
* postgres running on SUSE 6.4 Kernel 2.2.14: psql (PostgreSQL) 7.0.3
contains readline, history support

As drivers we have tried with JDBC driver: jdbc7.0-1.2 and the very last one
(pgjdbc) and also with the jdbc:odbc bridge, all of them with the same
result.

This is the test java code that perform 1000 accesses to modify index value
with the SELECT FOR UPDATE statement, with two clients as this one running
in parallel we obtain errors
try {
DBConnection db = new DBConnection();
db.doUpdate("UPDATE index SET sec_id = 0");
for (int i=0; i<1000; i++) {
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 + "')");
}
}
db.close();
} catch (Exception e) {
e.printStackTrace();
}

These are the tables defined for test purposes:

test=# \d
List of relations
Name | Type | Owner
----------+-------+------------
index | table | jcontreras
secuence | table | jcontreras
(2 rows)

And each table have the following definition:
test=# \d
List of relations
Name | Type | Owner
----------+-------+------------
index | table | jcontreras
secuence | table | jcontreras
(2 rows)

test=# \d index
Table "index"
Attribute | Type | Modifier
-----------+---------+----------
sec_id | integer |

test=# \d secuence
Table "secuence"
Attribute | Type | Modifier
-------------+---------------+----------
id | integer |
description | character(25) |
Index: secuence_id_key