David Wilson wrote:
(I originally missed replying to all here; sorry about the duplicate,
Vance, but figured others might be interested.
On Wed, Apr 16, 2008 at 1:55 PM, Vance Maverick <vmaverick@xxxxxxx> wrote:
Another approach would be to queue the log entries in a "staging" table,
so that a single process could move them into the log. This is fairly
heavyweight, but it would guarantee the consistent sequencing of the log
as seen by a reader (even if the order of entries in the log didn't
always reflect the true commit sequence in the staging table). I'm
hoping someone knows a cleverer trick.
Consider a loop like the following
advisory lock staging table
if (entries in table)
copy entries to main log table as a single transaction
release advisory lock on staging table
read out and handle most recent log entries from main table
The advisory lock is automatically released on client disconnect, and
doing the whole thing within one transaction should prevent any
partial-copies on failures.
It doesn't matter that there are concurrent inserts to the staging
table because the staging table is always wiped all at once and
transferred in a synchronous fashion to the main table. You also can't
lose data, because it's always in one of the two tables.
If you want to clean up the the staging table I have some concerns about
the advisory lock. I think you mean exclusive table lock.
There are other two options as well:
* Track which data is copies and remove those from the staging table
that are in the new table.
* Use a serializable mode for the staging-to-log-copying transactions.
In this way you can just copy the table and trow away everything
(without checking). This seems rather cheap and allows for concurrent
processing.
- Joris
