Creatting a db instance in a thread - what am i doing wrong

[sandy771]

I am using the code below within a threads resume method to gain access to all of the rows in a table.

When Commit is executed I am getting a database locked exception. If I pass the connection from the main application to the thread rather than create it within the thread then the code works fine.

I use a separate Connection as the hashing code is relatively slow but takes place on many hundreds of thousands of objects so I use StartTransaction and Commit to save changes to the database more efficiently.

There can be other changes being made to the table at the same time in the main thread (although there are none during my testing when I get the database locked exception).

Is this the best way of going about this? Am I doing anything fundamentally wrong? why am I getting the database locked error?

UniConnection1 = new TUniConnection(NULL);
UniConnection1->SpecificOptions->Add("ClientLibrary="+sqlitepath);
UniConnection1->Database = databasepath;
UniConnection1->ProviderName = "SQLite";
UniConnection1->SpecificOptions->Add("UseUnicode=True");
UniConnection1->Connected = true;

HashQuery = new TUniQuery(NULL);
HashQuery->Connection = UniConnection1;
HashQuery->Options->QueryRecCount = true;
HashQuery->UniDirectional = true;
HashQuery->SQL->Clear();
HashQuery->SQL->Add("select * from rtable where search > 0 and MD5 is NULL");
HashQuery->Execute();

HashQuery->First();
UniConnection1->StartTransaction();
do
{
// hash data
if((hashed%1000)==0)
{
UniConnection1->Commit();
UniConnection1->StartTransaction();
}
HashQuery->Next();
}while(!HashQuery->Eof);
UniConnection1->Commit();


Oh, I am using a unicode collation function for sorting (not required on the hash column) do I need to set the same collation function on any query/connection that access the dataabse?

[AlexP]

Hello,

To solve this problem, try to turn the EnableSharedCache and ReadUncommitted options on or try to increase the BusyTimeout property value:

Code: Select all

UniConnection1.SpecificOptions.Values['EnableSharedCache '] := 'True';
UniConnection1.SpecificOptions.Values['ReadUncommitted'] := 'True';

UniConnection1.SpecificOptions.Values['BusyTimeout'] := '>0';

If it does not help, please send us a small sample demonstrating the problem and a database file to alexp*devart*com.

All users' function/collation are linked to TUniConnection. If you need to use function/collation in queries associated with the definite TUniConnection, you should set them in this TUniConnection. In case you don't need to use function/collation, you don't have to set them.