Sqlite: busy timeout

[piula]

First of all, many, many thanks for supporting SQLite! This is a feature I have been waiting many years for :-))

Please don't take my issue reports as a criticism. I've been using SQLite for some years meanwhile and just want to help.

Steps to reproduce this issue

May I suggest using a busy timeout of e.g. 30 seconds for SQLite database connections.

Documentation: https://sqlite.org/c3ref/busy_timeout.html

Current behavior

When the database is in locked, i.e. in use by another application, nothing happens when trying to INSERT or UPDATE any data. HeidiSQL should receive an error message "sqlite_busy" from SQLite, but seems to suppress it (?). So the user currently doesn't have any feedback about his operation has succeeded or not.

Expected behavior

When setting a busy timeout, SQLite waits the specified amount of time for the database to become available again (i.e. for the other application to release its file lock), and then retries the INSERT or UPDATE operation. If the lock ain't released for the given timeout period, an "sqlite_busy" error is returned. It should be added to the log for the user being able to notice the error.

Possible solution

Either use the sqlite3_busy_timeout API function or "PRAGMA busy_timeout=30000" when establishing the database connection.

Check for the return code of API functions and provide the user with the error that has occured.

See also: https://www.sqlite.org/errlog.html

Environment

• HeidiSQL 11.0.0.5919 (64-bit)
• built-in sqlite.dll version 3.30.1
• Windows 8.1 64-bit

[ansgarbecker]

Thanks for the details. Should be easy to set that busy-timeout when the connection starts.
Indeed HeidiSQL does not yet examine the result code of sqlite3_step() after a non-result query, which may be SQLITE_BUSY (or some other error as well). I will make this more precise, throwing an error if we don't get one of the "ok" codes.

[ansgarbecker]

Next build checks the error code after firing a query (and displays an appropriate dialog), and sets that busy timeout to what the session has in its "Query timeout" setting:

For me this works as expected, first time I saw errors in SQLite :)