David Wilson wrote:
On Tue, Apr 22, 2008 at 9:52 AM, Joris Dobbelsteen
<joris@xxxxxxxxxxxxxxxxxxxxx> wrote:
Describe the mechanism, because I don't really believe it yet. I think you
need to do a advisory lock around every commit of every transaction that
writes to the log table.
Consider some number of reader processes and some number of writer processes.
Writer processes touch only the staging table, and solely do inserts
into it. As a result, writer processes cannot interfere with each
other in any way and do not require any synchronization beyond that
provided by MVCC.
Reader processes are interested in polling the logging table at
intervals. In the process, they also act as staging-to-log movers.
This act (because it is destructive and because we require serialized
inserts for id generation in the log table) must take a lock that
prevents other readers from attempting the same work at the same time.
Each reader process therefore has a loop that appears as follows:
1) Obtain advisory lock.
2) Begin transaction.
3) For each row in staging table, insert copy into log table.
4) Delete all visible rows from staging table.
Ah, yes, all visible rows...
My point is that, unless you use a transaction with serializable
isolation, this all visible rows for the second statement might be
different from those that you copied into the log table.
With the normal Read committed isolation level you suffer from a
possible nonrepeatable read that might change tuple visibility between
different statements.
5) Commit transaction.
6) Release advisory lock.
7) Handle not-yet-seen rows in the logging table (This is the primary
work of the readers)
8) Sleep for the desired interval and return to 1).
We require two types of synchronization and the above takes care of both:
1) The advisory lock prevents multiple readers from doing simultaneous
staging-to-log moves.
2) The transaction block ensures that the reader will see a consistent
state on the staging table while writers may write at the same time;
writes that occur during the reader's transaction block will simply be
ignored during this round of reading.
See above, you demand its impossible for nonrepeatable reads to occur.
You need both types of synchronization to avoid problems- taking an
exclusive lock would simply be the sledgehammer method of doing the
synchronization, since it would take the place of both the advisory
lock and the transaction at the same time but would also block
writers.
I agree with you on this, but it does guarentee the impossibility of a
nonrepeatable read at the cost of concurrency. There seems to be a
better solution indeed.
- Joris
