(1) By anonymous on 2021-09-25 16:04:35
In the latest versions of SQLite.Interop.dll the api entry sqlite3_busy_handler is no longer being exported. Is it possible to have it back?
(2) By mistachkin on 2021-10-01 16:41:42 in reply to 1 [link]
I'm curious about your use case. Perhaps the managed assembly can provide for it? Calling directly into the native interop assembly is unsupported.
I needed this for a workaround. Maybe this is not needed anymore, but as until now it was working alright I didn´t look into it anymore. My app opens and closes connections frequently. It does not reuse a single connection to SQLite (and this behavior cannot be easily changed). SQLite cannot handle multiple open connections so I have to be careful not to open a new connection when one was already open, but that was not a problem because the system is not multitasking and connections are short-lived. The thing is that starting from System.Data.SQLite version 1.0.82 connections started being freed through sqlite3_close_v2, which left "zombie" native connections (blocking the db) if there were pending statements that could be processed. Sometimes it was the case the some statements generated by IDataReaders would not be garbage collected in time or they would be in the finalization queue at commit time, so the commit would be blocked waiting for the GC to eliminate the statement and it would eventually timeout. I hooked sqlite3_busy_handler to get notified of the impending timeout and so force a GC. It was hacky but that did the trick, and I could not find any proper workaround for this problematic behavior. Maybe it was an internal problem from the data reader when handling the statements (I did close them properly but the native statement still remained alive). Do you happen to know if this behavior has been modified or any other way to handle it without the need of sqlite3_busy_handler?
(4.3) By mistachkin on 2021-10-13 03:56:32 edited from 4.2 in reply to 3 [link]
Are you able to manually dispose of the SQLiteDataReader and SQLiteCommand objects associated with a connection? If not, is there is some external code making use of those objects? EDIT #1: Meanwhile, I'm going to add a managed event that exposes the busy handler. EDIT #2: Please see [https://system.data.sqlite.org/index.html/info/2541fcec64c42a1d].
I cannot be sure if all data readers and commands were manually disposed but I suspect so (it's been already some years since I debugged this). What I do know, as I have a mechanism in place to track of all open connections, is that when this problem appeared all opened connections had already been properly closed. I believe that it is part of the contract of IDbConnection.Close() that it should actually close the connection (when no pooling is active) and if some command or reader tries to execute it should fail. So the native connection staying open after being explicitly closed seems to me an undesirable behavior. Thank you for formally exposing the busy handler so that I can finally get rid of all the reflection stuff I had to use.