Locking Considerations for SQLite
The SQLite database has very limited support for concurrency. This has implications for the default locking strategy, hit list behavior, and transaction length.
When a READ
lock is placed on a
record or table (for example, when retrieving records), SQLite locks the whole database, preventing
other applications from inserting, updating, or deleting records anywhere in the database.
The default behavior of the SQLite connector is designed to reduce the effects of this behavior.
Caution: If you want to configure SQLite as a concurrent database, it may not be possible to resolve all issues that this shared locking behavior causes.
Default Locking
When retrieving records from a database, Uniface usually establishes a read lock on the table to ensure that another application that inserts or deletes a record in the same table does not affect the results of the retrieve. This ensures that the records loaded into a component, are those as they were when the retrieve command was first issued. No records are excluded or duplicated as a result of other programs or users inserting or deleting records.
Read locks normally block updates on the affected table. Other tables are not blocked, so other applications can insert and update records elsewhere in the database. However, SQLite read locks are shared locks that lock the whole database, so another application cannot update the database until Uniface has released its read locks.
To reduce the impact of this behavior, the SLE connector releases read locks as soon as the last record has been retrieved.
To use the normal SQLite locking behavior, you can
use the connector option locktype=
. Read
locks will then not be released until a commit or rollback occurs, thus blocking other applications
from updating the database.cautious
Caution: Setting locktype=
is not recommended when concurrency is needed. cautious
Stepped Hit Lists
To avoid locking the whole database while one user is scrolling through occurrences on a form, Uniface's stepped hit list mechanism is turned off by default, so all records are retrieved at once instead of in groups of 10. When retrieving large numbers of records, this could be a problem.
You can use the connector option
step size=Number
to turn on the hit list
mechanism and specify how many records one hit step must contain, but of course, this can result in
locked-database errors in other sessions accessing the same database during the time a hitlist is
not completed.
Short Transactions
Only one application can update an SQLite database at one time. All other applications are blocked from updating the database until the transaction is committed or rolled back.
Uniface sets a RESERVED
lock when
doing a store. This becomes an EXCLUSIVE
lock when a store is committed, and it is
released only after the commit (or rollback) completes. All
other applications are prevented from updating the database from the time the store is started
until the commit completes.
When designing an application that will use SQLite in a concurrent way, you must therefore ensure that transactions are kept as short as possible, especially when inserting, updating or deleting records. You can do this my reducing the amount of processing executed between a store and a commit command.
WAL Mode
SQLite has a Write Ahead Logging WAL) mode that writes updates to a write-ahead log
instead of to the database. In this case, the EXCLUSIVE
and
RESERVED
locks are on the log and not on the database itself. After a specified
number of transactions (by default, 1000), the logged transactions are applied to the database.
While this occurs the EXCLUSIVE
lock is applied to the database.
This can be useful if you require a high level of concurrency, for example in a web application with many Uniface Servers concurrently accessing an SQLite database. However, even when using WAL, you need to consider the following issues:
- Only one application can do an update at any time, so your application must be able to deal with store errors as a result of the database being locked.
- The write-ahead log has to be applied and synchronized to the database periodically, so your application must be able to handle both read and write errors as a result of a locked database.
- WAL mode solves some of the locking issues
described earlier, but not all. If you set
locktype=
and turn on WAL mode, you will see fewer database locked errors than without WAL mode, but there will still be some. Therefore, settingcautious
locktype=
is not recommended when concurrency is needed.cautious
To configure WAL mode, use the Command Line Shell
For SQLite (sqlite3.exe) utility with PRAGMA
parameters:
- To turn WAL mode on:
PRAGMA journal_mode=
WAL
;
- To change the synchronization point between
the write-ahead log and the database:
PRAGMA wal_autocheckpoint=
Number;
For more information on WAL, see the SQLite website: www.sqlite.org/wal.html .