I had a very long day yesterday fighting with something that I didn’t notice was caused by something seemingly unrelated. I made this commit in the repo because I noticed that when a view index grows large, it takes a long time to delete it. That seems odd, why should it take a long time to delete something? Well it turns out because it needs to update the internal SQLite index as well. I’m not sure exactly how this works but it seems like it will remove one row at a time and update the index for each. This takes a long long time and so I realized it was faster to DROP the table all together and recreate it.
However, there is a mistake. Can you see it? Probably not. I didn’t notice it for a while. It manifested as follows: On a certain unit test I was seeing stale data come out of the database no matter what I did. The stale data in question was the latest AUTOINCREMENT sequence from the revs table in the database. It would keep returning the same value over and over (even while other connections to the same database from outside the program returned different data). I tried throwing endless experiments at it. This kind of behavior is inherent to transactions, so I even went as far as to disable transactions all together but still I got stale data. If you remember from a few posts ago, the SQLite storage engine uses separate connections for reading and writing. The read connection was stuck. Nothing would unstick it. It was like it was stuck in a transaction (SQLite will return the same data from inside a transaction until it ends) but I don’t use transactions at all with the read connection.
OR DO I? Let’s go back to the commit above. This queries the database to figure out how many rows are in the table in question. However, I forgot to close the query after I used it. This inherently started a transaction that never ended and caused the problem that I spent 6 hours chasing. Truly the butterfly effect! You can see the updated version that I use now.