Обсуждение: [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

Hey,

   Try some reading at this location...

   The Practical PostgreSQL Book (LOCK)
   http://www.commandprompt.com/ppbook/index.lxp?lxpwrap=r27479%2ehtm

   You're in a kind of race condition and you're not managing processes
very well. The row-locks you want are automatically obtained by the
     SELECT ... FOR UPDATE
     UPDATE...
and INSERT...

   I'm also new to PostgreSQL and I'm still exploring the capabilities
and thinking in possibilities. I don't know whether it is for this kind
of use but take a look at LISTEN and NOTIFY keywords. They provide
together a kind of IPC management.

   In theory you can LISTEN for 'ok' in the 2nd process and when the
first finishes what it is doing, it NOTIFies an 'ok', so the 2nd process
takes action and starts its works while the first one LISTENs for 'ok'
and so on...

   Remember, I'm not a guru, it is just a suggestion...I don't even know
whether it will work or not for you.

William

"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