Locking Considerations for SQLite

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=cautious. Read locks will then not be released until a commit or rollback occurs, thus blocking other applications from updating the database.

Caution: Setting locktype=cautious is not recommended when concurrency is needed.

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=cautious and turn on WAL mode, you will see fewer database locked errors than without WAL mode, but there will still be some. Therefore, setting locktype=cautious is not recommended when concurrency is needed.

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 .