On 18 December 2017 at 18:03, Dinu <dinumar...@gmail.com> wrote: > Rowan Worth-2 wrote > > I'm not sure what you're worried about? Dropping and recreating identical > > indices within a transaction won't cause a visible structure change to > > concurrent readers -- that's the point of a transaction. > > I honestly don't see how in any DB system the client process would not > crash > if the index it's running a curson on were to be removed. Even if SQLite > were to pull this magic out of the hat,
The entire point of SQL transactions is to implement this magic! All other clients see the state either before or after the transaction. > starving client processes for the > lack of an index (a full scan query would probably take in excess of 30s) > would quickly pile up the clients to the point where one would have to kill > them anyway. > The other clients won't run without an index. They won't have their current index ripped out from under them either. They _will_ have to wait for the deletion transaction to finish up. But that's an issue regardless - once the delete transaction's memory cache spills, it will have exclusive access to the database until the transaction COMMI-- ah but wait, I'm talking about rollback journal mode, because that's what I'm familiar with. WAL has different concurrency characteristics. I'll leave the details to someone else, but I'm 100% sure you still get atomic transactions. > So with this in mind, I'm really not looking for a barbaric fix to this, > I'm > more of tryng to understand the problem and find a viable, semantically > stable solution (and maybe trigger some improvements in SQLite, if there's > a > system bug). > Seeing what effect dropping/recreating the indices has on the overall speed _IS_ a step in understanding the problem, no? -Rowan _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
