Database is locked errors when >25 Records

Discussion of open issues, suggestions and bugs regarding LiteDAC (SQLite Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
Bob Boyd
Posts: 16
Joined: Thu 28 Jul 2016 19:26

Database is locked errors when >25 Records

Post by Bob Boyd » Tue 09 Oct 2018 19:30

There is something going on with the TLiteTable components that is generating ‘Database is locked’ errors when there are > 25 records in a table and any manipulation of records is attempted such as adding or deleting records to the table with >25 records or any other table with the same database connection.

The application opens two TLiteConnections, one for the user’s selected source database and another for the user’s private database for that contains the user’s temporary records for editing, reporting and data acquisitions. The private database that is stored in the C:\Users\...\AppData\Local\PWC60 folder. The UI has a TLiteTable objects that are connected to the private database which has an ATTACH of the source database transferring records between source and the private for any user processes such as acquiring and posting the data acquisitions records. The errors have only occurred for the private database connection.

The typical steps involved:
Load the TestQue table in the private DB with Equipment IDs/Attachment records for data acquisition.
Edit private Test Queue records to custom the test configuration.
Acquire measurement data for the selected record in the private Test Queue.
Review the data acquisition results that are saved to the private Post Queue table.
Post the data acquisition results from the private Post Queue table to the source DB Archive table.
Delete the private Test Queue record after the data acquisition.

If the Test Queue is loaded with more than 25 records, the errors will occur the first time the UI attempts to access tables in the private database, usually for record editing or somewhere in the data acquisition process that includes record posting and deletion.

I was able to eliminate the errors for the situation described here for the private Test Queue records by enabling the FetchAll property. That solution is not feasible for all private tables on several user selection forms or when a TLiteTable table object is connected to the open source database where the SmartFetch properties are used for tables that could have hundreds of thousands or legacy Archived data from decades of data acquisitions. Without using SmartFetch on the source tables, the application memory usage sky rockets to nearly the size of the source database which are now in the gigabyte range, so those properties must be used by the application.

This problem is similar to the previously reported ‘Incorrect Query Results After External DB Connection Revisions’ issue with incorrect query results after an INSERT query when it loads >25 records.

MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: Database is locked errors when >25 Records

Post by MaximG » Wed 10 Oct 2018 06:50

To investigate the issue, compose and send us the simplest sample, execution of which causes the issue. It's more convenient to do using the e-support form: https://www.devart.com/company/contactform.html

Post Reply