pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedMVCC and concurrent clients
4+ messages / 3 participants
[nested] [flat]
* MVCC and concurrent clients
@ 1999-07-30 20:10 Tom Lane <tgl@sss.pgh.pa.us>
1999-07-31 23:05 ` Re: [SQL] MVCC and concurrent clients Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 1999-07-30 20:10 UTC (permalink / raw)
To: pgsql-sql
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's updateSequenceNumber 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 do
SELECT * 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [SQL] MVCC and concurrent clients
1999-07-30 20:10 MVCC and concurrent clients Tom Lane <tgl@sss.pgh.pa.us>
@ 1999-07-31 23:05 ` Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu>
1999-08-01 14:55 ` Re: [SQL] MVCC and concurrent clients Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Ross J. Reedstrom @ 1999-07-31 23:05 UTC (permalink / raw)
To: pgsql-sql
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_table
WHERE 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [SQL] MVCC and concurrent clients
1999-07-30 20:10 MVCC and concurrent clients Tom Lane <tgl@sss.pgh.pa.us>
1999-07-31 23:05 ` Re: [SQL] MVCC and concurrent clients Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu>
@ 1999-08-01 14:55 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-08-02 20:44 ` Re: [SQL] MVCC and concurrent clients tjk@tksoft.com <tjk@tksoft.com>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 1999-08-01 14:55 UTC (permalink / raw)
To: Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu>; +Cc: pgsql-sql
"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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [SQL] MVCC and concurrent clients
1999-07-30 20:10 MVCC and concurrent clients Tom Lane <tgl@sss.pgh.pa.us>
1999-07-31 23:05 ` Re: [SQL] MVCC and concurrent clients Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu>
1999-08-01 14:55 ` Re: [SQL] MVCC and concurrent clients Tom Lane <tgl@sss.pgh.pa.us>
@ 1999-08-02 20:44 ` tjk@tksoft.com <tjk@tksoft.com>
0 siblings, 0 replies; 4+ messages in thread
From: tjk@tksoft.com @ 1999-08-02 20:44 UTC (permalink / raw)
To: tgl@sss.pgh.pa.us; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~1999-08-02 20:44 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-07-30 20:10 MVCC and concurrent clients Tom Lane <tgl@sss.pgh.pa.us>
1999-07-31 23:05 ` Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu>
1999-08-01 14:55 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-08-02 20:44 ` tjk@tksoft.com <tjk@tksoft.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox