On 1/20/19 4:51 PM, andrew.g...@l3t.com wrote: > James K. Lowden wrote: >> On Sat, 19 Jan 2019 08:07:42 -0500 Richard Hipp <d...@sqlite.org> wrote: >>> The busy timeout is not working because you start out your transaction >>> using a read operation - the first SELECT statement - which gets a read >>> lock. Later when you go to COMMIT, this has to elevate to a write >>> lock. But SQLite sees that some other process has already updated the >>> database since you started your read. >> Another solution is to rely on atomicity in SQL: >> >> insert into t >> select :pid, nrows, N >> from (select 1 as N union select 2 union select 3) as cardinals >> cross join (select :pid, count(*) as nrows from t) as how_many; >> >> By using a single SQL statement, you avoid a user-defined transaction >> and any proprietary transaction qualifiers. > Thank you for the suggestion, but I don't believe this is an option in my > application. There's too much back-and-forth between the database and my > logic to put it all into a single statement. Thus, transactions are > necessary. Transactions exist to allow multiple statements to become an > atomic unit, so eschewing them is basically the same thing as admitting they > don't work. There are two possibilities: > > 1. Transactions do work, but I'm misusing them and must learn how to be more > careful. In this case, I will update documentation to properly explain their > use to others. > > 2. Transactions don't work, at least not for my task. In this case, I will > do my best to investigate the problem and suggest a correction. > > Either way, the right thing for me to do is continue digging in. > > Going from BEGIN to BEGIN IMMEDIATE indeed fixed the test program I wrote, > but my application is still broken, and I don't know what's different about > it. I'm working on instrumenting the fcntl() calls to log the sequence of > operations.
Transactions work, but the way you have described your use of them has a predictable issue. The problem being if you begin with a shared lock, try to promote to an exclusive lock to write, then you need to wait for all the other shared locks to clear, and if another of them also tries to promote to an exclusive lock, which cause a deadlock, which sounds to be your issue. Starting with a BEGIN IMMEDIATE breaks this deadlock situation, so it can fix your problem, at the cost that you get less concurrency. The experimental concurrency option might also help, in that a write operation doesn't need an exclusive lock for the whole database, but does introduce more points where a transaction might fail and need to be rolled back. -- Richard DamonTh _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
