David Wilson wrote:
> On Tue, Apr 22, 2008 at 9:52 AM, Joris Dobbelsteen
> <joris@familiedobbelsteen.nl> 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
