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. 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. 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. -- - David T. Wilson david.t.wilson@xxxxxxxxx
