On Fri, 18 Mar 2005, Eli Burke wrote:
How about using a second in-memory database for your modifications, then using ATTACH to merge the changes. I'm fairly sure I remember Dr. Hipp stating that moving complete rows between attached database tables is very speedy. Depending on the particulars of your situation you could do a couple different things:
* if you are just adding new rows, open an in-mem database and create an identical table to do all your inserts. Insert the new rows, then attach the db, and finally start a transaction and: INSERT INTO old-table SELECT * FROM memory.new-table;
* if you must modify existing data, you could do the process in reverse-- select the contents of one or more tables into the memory db, modify them, then inside a transaction, delete the contents of your main db's table(s) and select them back into the main table(s).
If only one table is getting modified but the readers don't need to access it, this will get around the problem of one transaction holding an EXCLUSIVE lock for long periods of time. If you are touching more tables, you may have create and track some sort of dirty indicator or last update time.
Because you are using a transaction, it is still atomic. But because you are just moving the rows from another database, it should solve the problem of holding the lock for too long. In-memory databases are created by naming them ":memory:" You can find more info by doing a mailing list search on "ATTACH in memory" or poking around the docs for ATTACH.
this is quite interesting - i'll play with it. thanks!
-a -- =============================================================================== | EMAIL :: Ara [dot] T [dot] Howard [at] noaa [dot] gov | PHONE :: 303.497.6469 | When you do something, you should burn yourself completely, like a good | bonfire, leaving no trace of yourself. --Shunryu Suzuki ===============================================================================
