Hi,
CREATE TABLE rev(current); INSERT INTO rev VALUES(0); CREATE TABLE data(..., revision); Readers: SELECT * FROM data JOIN rev ON revision = current WHERE ... ; // or "SELECT current FROM rev" into var and passing it value in "SELECT * FROM data WHERE revision=?" Writer: // insert new records, old records remains SELECT current + 1 FROM rev; // into some variable BEGIN; INSERT INTO data(..., revision) VALUES (..., current ); INSERT INTO data(..., revision) VALUES (..., current ); ... ... repeat 1000 times ... COMMIT; BEGIN; INSERT INTO data(..., revision) VALUES (..., current ); INSERT INTO data(..., revision) VALUES (..., current ); ... ... repeat 1000 times ... COMMIT; // repeat inserting until all records are inserted; 1000 is a number of records taken by a wild guess ) // now switch readers to new records BEGIN; UPDATE rev SET current = current + 1; COMMIT; // no need for a begin/commit just to point that this runs inside its own transaction // now delete old records again incrementally // repeat this block until records stop deleting from table BEGIN; SELECT Max(rowid) - 1000 FROM data; // into variable MaxId DELETE FROM data WHERE revision = current - 1 AND rowid > MaxId; COMMIT; // done, there are only new records in a table, repeat above steps to insert new bunch of records Regads, GG Wed 28 Nov 2012 09:47:50 от Alejandro Martínez <[email protected]>: > > > > >I have one process that each 30 minutes refills several tables in this > manner: > > sqlite3_open_v2(CACHEDB_PATH, &sqcache_conn, SQLITE_OPEN_CREATE | > SQLITE_OPEN_READWRITE, NULL) > > - For each table: > > begin deferred transaction; delete from [table]; > insert into table ... > insert into table ... > insert into table ... > insert into table ... > [up to 180.000 rows] > commit; > > and sometimes the commit fails, so it is retried. (why would it fail? its > the only writter) > > And then i have many other processes that open that sqlite database read > only. sqlite3_open_v2(_dbfile, &sqcache_conn, SQLITE_OPEN_READONLY, NULL) > and sqlite3_busy_timeout(sqcache_conn, 5000) > > These processes create very simple prepared statements to query that tables. > > And the big problem i'm having, is that when i step these prepared > statements, they lock for 5 seconds and then fail. > > And i put that busy timeout just for completeness, cause i wasn't expecting > any locking because for being a read only query. > > I really need these queries not to lock or fail. > > What am i doing wrong? > Any suggestions? > > Thank you, > Alejandro > _______________________________________________ > sqlite-users mailing list > >[email protected] > >http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
