Okay, I put in some instrumentation. Basically I print out all database queries as they happen, along with all calls to sqlite3OsLock() and sqlite3OsUnlock() (including their lockType argument and any abnormal return code). Also I print out how many times sqlite3InvokeBusyHandler() has to call the busy handler.
For the moment, the solution that is working for me is to disable syncing with PRAGMA synchronous = OFF. This is acceptable in this particular application because a power failure or OS crash will necessitate restarting the data gathering process anyway. I'll explain later why this change helps. In looking at the logs, I'm seeing several unlucky locking patterns. Let's just pick one. Process A gets starved by process B since B does many write transactions in a row, each time successfully getting an EXCLUSIVE lock. When B calls fdatasync() (actually fsync()), it blocks while holding EXCLUSIVE. A wakes up, but A can't immediately get a SHARED lock because B holds EXCLUSIVE. A goes to sleep while holding no locks, then B wakes up when the fsync() completes. B then releases locks but grabs EXCLUSIVE again to complete its next transaction, and the cycle repeats. A still can't get its SHARED lock, so it goes to sleep again, and then B continues to monopolize the lock. This goes on long enough to exhaust A's patience, at which point SQLITE_BUSY propagates to the application. If A tries to get SHARED while B holds EXCLUSIVE, I'd like to see A's desire for SHARED to be registered somehow so that A is granted SHARED the moment B drops EXCLUSIVE. Then when B tries to get EXCLUSIVE a second time, it is forced to wait in PENDING until A releases SHARED. I imagine one way to do this would be for A to block inside fcntl() rather than usleep(), so that the OS can atomically switch the lock over to A the very instant B releases it. But as far as I know, the only API that can give fcntl() locking a timeout is setitimer() and SIGALRM. I wouldn't want SQLite to mess with that, not without being given permission by a special preprocessor #define. SQLITE_ENABLE_SETLK_TIMEOUT, perhaps? In the above case, A is just trying to get SHARED. This is a difference from the test program I posted, which only needs write transactions. My real application needs to do a read transaction as part of database initialization in order to confirm the schema version is correct. That's probably a waste of time, but it's in there for now. But I think the more important difference is that B is doing many transactions in a tight loop. I guess I could put a sleep in the loop to give other processes a chance. I saw another pattern that I'm having a harder time explaining. It looked almost like the process holding EXCLUSIVE got stuck waiting for the process trying to get EXCLUSIVE. The latter would eventually timeout, at which point the former would proceed. Shrug! I should investigate this further, but look at the time. Okay, why does disabling syncing help? Taking fdatasync() out of the picture means B doesn't have to go to sleep holding EXCLUSIVE. It grabs EXCLUSIVE, tells the OS what it wants written to disk, then releases EXCLUSIVE without waiting to confirm that all the bits landed on the platters. ------------------------------------------- CONFIDENTIALITY NOTICE: This email and any attachments are for the sole use of the intended recipient and may contain material that is proprietary, confidential, privileged or otherwise legally protected or restricted under applicable government laws. Any review, disclosure, distributing or other use without expressed permission of the sender is strictly prohibited. If you are not the intended recipient, please contact the sender and delete all copies without reading, printing, or saving.. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
