At 06:36 PM 10/2/2001, Hiroshi Inoue wrote:
>The cause is that the stored function uses a common
>snapshot throughout the function execution. As I've
>complained many times, the current implementaion is
>far from intuition and this case seems to show that
>it isn't proper at all either.

Bravo! That indeed seems to have been the problem. To solve it, I simply 
moved the LOCK TABLE out of the PLpgSQL function and into the JDBC code. 
While this isn't *ideal* as it leaves the table locked across two JDBC 
calls (the function and the following commit), it achieves the desired 
result (synchronous access to the idfactory table across all clients), and 
as I said, the function won't be called very often. It's far more important 
that it work as expected rather than it work in sub-millisecond time.

To illustrate then what seems to have been occurring:

     Time    Thread A      Thread B
      1      snapshot
      2      lock
      3      read 1, 1
      4      write 11, 2
      5                    snapshot
      6      return 1
      7      commit
      8                    lock
      9                    read 1, 1
     10                    write 11, 2
     11                    FAIL

As long as thread B takes its snapshot any time before the commit at (7), 
its write at (10) will not affect any rows because ...

>The *update* statement
>find the row matching the where clause using the common
>snapshot but will find the row was already updated and
>the updated row doesn't satisfy the condition any longer.

Ouch. So querying for select, update, delete, whatever goes against the 
snapshot to *locate* rows, but then applies the where clause to the *new 
values* not seen in the snapshot? If that's the case, that's extremely 

Anyway, many thanks to everyone for keeping me from going totally insane. 
Luckily the other stored procedures we need to write won't require such 
strict access to table data. :)


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Reply via email to