[POSTGRESQL] LOCKING A ROW
От | Jesus Contreras |
---|---|
Тема | [POSTGRESQL] LOCKING A ROW |
Дата | |
Msg-id | MJEFKJHOOLCNBJPOJAMIGECMCIAA.jcontreras@isoco.com обсуждение исходный текст |
Ответы |
Re: [POSTGRESQL] LOCKING A ROW
(Tom Lane <tgl@sss.pgh.pa.us>)
|
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: