Can SQLite query go in hung/stuck state?
(1) By SM (surabhi.nitsurat) on 2020-04-17 13:44:28 [link] [source]
I m writing Error handling for a c# application using Sqlite and System.Data.Sqlite library. I m using try catch block to look at any exceptions thrown by the DB calls.
I understand there can be only one writer at a time operating on the DB, and failure to acquire lock will throw exception. Is there any possibility for any DB operation to go in a hung/stuck state due to any reason(malformed query, locks, corrupt DB) which also needs to be handled? Does sqlite support any timeouts for running operations apart from lock acquiring timeouts?
You can create a query that is effectively an infinite loop. For example:
WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c) SELECT x FROM c WHERE x<0;
There are a number of ways to set a timeout on a query. Perhaps the easiest to implement is to start a timer in a separate thread and then invoke sqlite3_interrupt() if the timer expires. You can also set a progress callback that is invoked periodically, and on each invocation of the callback, check the current time to see if the query has been running too long, and stop the query by returning non-zero from the progress callback itself.
The above methods work for C-code. I don't know about the bindings to C#, but I'm suspect something similar must exist there.
(3) By SM (surabhi.nitsurat) on 2020-04-18 22:16:01 in reply to 2 [link] [source]
Thank you for the above suggested methods to handle timeout.
I m using only very basic and simple CRUD statements in my application. If my query logic being simple enough cannot end up in an infinite loop, should I still worry about handling db operation being stuck due to any other factor?
There's really only those two things to worry about inside SQLite: access starvation (two locks both waiting for each other) and the programmer coming up with a recursive WITH statement which doesn't terminate. Any good programmer will never encounter either of them.
Most hangs I've dealt with which were reported as SQLite problems were actually hardware problems. SQLite does complicated things with storage very quickly SQLite so it's often the thing that was happening when your disk drive fails, or when a connection inside your computer comes loose.
(5) By SM (surabhi.nitsurat) on 2020-04-19 07:06:10 in reply to 4 [link] [source]
Would sqlite_interrupt be able to stop the running commands in such events of hardware failure as well?
A broken connection inside the computer that causes "a hung/stuck state" (your title) normally freezes the whole compjuter. Since the computer isn't doing anything at all it won't execute any more instructions until it's rebooted. So it would never get to execute sqlite3_interrupt() or any other instruction.
Hardware failure on a non-boot drive (e.g. if your Windows computer boots from C: but your database is stored on D: or E:) probably won't cause "a hung/stuck state". It's more likely to make SQLite return SQLITE_IOERR which your software can deal with however it likes.
(The above explanation is oversimplified for brevity.)