Обсуждение: MVCC and concurrent clients

I have an application that involves multiple processes accessing and
updating a shared database.  I have been running it successfully on
6.4, but I am wondering whether I can use 6.5's MVCC features to
improve performance.  The user's guide doesn't tell me a whole lot
about the behavior of MVCC.  Comments on this problem would be
appreciated.

The processes must all be aware of the current state of every active
tuple in the database, so each one needs to find out when any other one
changes the database.  However, there are plenty of tuples in each table
that are *not* getting modified at any instant, so repeatedly SELECT'ing
the whole table and looking for differences is not what I want.

The solution I'm using now: each shared table has a column that is
an "update sequence number", plus an associated sequence object.
Whenever any client wants to insert or update rows, it does
something like this:BEGIN TRANSACTION;LOCK TABLE shared_table;INSERT and/or UPDATE, setting each new or modified
row'supdateSequenceNumber to    nextval('shared_sequence_object');NOTIFY shared_table;END TRANSACTION;
 

All clients are listeners for the NOTIFY messages, and whenever
a notify is received, a client will doSELECT * FROM shared_table WHERE updateSequenceNumber >
highest_sequence_number_seen;
where highest_sequence_number_seen is a state variable in each
client that is updated while scanning the SELECT results.  This brings
the client up-to-date about all new and modified rows.  There is an
index on updateSequenceNumber so that this kind of query is fast.

In 6.4 this works pretty well; it doesn't allow for concurrent updates
but 6.4 didn't allow concurrent writes even without a LOCK command.
I am wondering if I can use MVCC in 6.5 to increase the level of
concurrency.  The main problem I see is that in the 6.4 design, the
writer sequencing provided by the LOCK TABLE statement guarantees that
updates will be assigned strictly increasing updateSequenceNumber
values.  If I allow concurrent writes in 6.5, it seems that writer
transactions might commit while other writers that obtained lower
sequence values haven't committed yet.  If that happens, a reader might
scan the committed tuples and advance its highest_sequence_number_seen
beyond the uncommitted values.  When the other transaction(s) commit and
NOTIFY, the reader will repeat its SELECT, but will fail to retrieve the
newly-committed tuples.  Oops.

The documentation doesn't say anything about the behavior of sequence
objects under MVCC, so I don't know if there's a way to prevent this
problem.  Do I have to stick to 6.4-equivalent total locking of the
table to prevent concurrent use of the sequence object?

Alternatively, is there a better way to design the table structure for
this application?  I would think "give me all recently modified tuples"
is a pretty standard problem.

Thanks for any info...
        regards, tom lane

On Fri, Jul 30, 1999 at 04:10:40PM -0400, Tom Lane wrote:
Tom - 
How about timestamps?

> something like this:
>     BEGIN TRANSACTION;
>     INSERT and/or UPDATE, setting each new or modified    row's updatedTime to 'now';
>     NOTIFY shared_table;
>     END TRANSACTION;
> 
> All clients are listeners for the NOTIFY messages, and whenever
> a notify is received, a client will do
>     SELECT * FROM shared_tableWHERE updateTime >= last_time_looked;

> 
> Alternatively, is there a better way to design the table structure for
> this application?  I would think "give me all recently modified tuples"
> is a pretty standard problem.
> 

Ross
-- 
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu> 
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St.,  Houston, TX 77005

"Ross J. Reedstrom" <reedstrm@wallace.ece.rice.edu> writes:
> How about timestamps?

I thought about that approach originally, but it doesn't change the
nature of the problem.  Transactions will not necessarily commit in
the same order as the timestamps they've applied to updated tuples.
        regards, tom lane

"Ross J. Reedstrom" <reedstrm@wallace.ece.rice.edu> writes:
> How about timestamps?

I thought about that approach originally, but it doesn't change the
nature of the problem.  Transactions will not necessarily commit in
the same order as the timestamps they've applied to updated tuples.
        regards, tom lane

Tom,

I missed a part of this dialogue, but if I am correct we
are talking about problems associated with concurrent updates.

Since this is such an important topic, how about creating
a description of the specific problems associated with the issue,
with the goal of establishing a comprehensive solution(s)
to handle the different needs of various client programs?

My thought would be to have a serial number which can be requested
from the server for updates, so that applications which must/want to
process updates sequentially can do so.
(updates=updates/deletes/inserts)


Troy

Troy Korjuslommi                Tksoft OY, Inc.
tjk@tksoft.com                  Software Development
                                Open Source Solutions
                                Hosting Services