I have multiple threads running and accessing tables. I keep getting "table locked" errors from SQLite (using the object manager) and would like to be able to lock records I'm about to alter and be able to wait until I can acquire a lock before starting an alter operation.
Is there a mechanism to do this or would I have to use OS semaphores to control my access ?
I have never experienced such error with SQLite. There is no built-in mechanism for record locking in Aurelius itself, and I believe neither in SQLite. You will have to control it yourself.
Am I correct in saying that at any point in time, for SQLite, only 1 operation can be active at one time ?
My testing has indicated that with multiple threads, I get the locked database error very easily.
By wrapping each operation with a semaphore, I no longer have locked database errors.
In this case, a locked operation was
gCriticalSection.Enter;
lConnection := TSQLiteConnection.Create('Database=' + nm + ';EnableForeignKeys=True');
lManager := TObjectManager.Create(lConnection);
x := lManager.Find.Where(Linq['Id'] = 1).UniqueResult;
x.Data := x.Data + 1;
lManager.Update(x);
lManager.Flush;
lManager.Free;
gCriticalSection.Leave;
This is effectively a global lock but seems to work well. Any attempt to perform an operation outside of the gCriticalSection being locked risks the database being locked. This global lock needs to be across everything thats using the database. Ie if you have multiple threads, even if they each have their own manager, they have to use a global semaphore to prevent any more than one thread from performing work at one time.
It also works if you have a single global connection and manager rather than having the 2 inside the lock, but you can only have one.
As an observation, I seem to be getting about 50 transactions per sec on an SSD and about 4 per second on a spinner.
If I have this wrong and I should be able to run multiple managers simultaneously, I'd be interested in knowing how it's done.
Even with the above, if I use another program to view the database while the program is updating, it can still cause the table to become locked.
That's normal behavior and use for SQLite, yes. Another suggestion, always wrap data modification with transactions, it makes SQLite much faster.
I had already read about the wrapping of transactions in SQLite so had already tried that.
I also tried using FireBird and MariaDb (MySql) and seem to have similar problems with them.
My current testing with MariaDb is that 1 thread is fine. Adding more than one thread and it access violates unless I also wrap the Aurelius update code in a semaphore and use the same connection and manager in each thread. If I have just 1 thread in the app and run multiple instances, it works as expected, but it seems that either Aurelius isn't meant to be used in multiple threads simultaneously, or that I'm doing something wrong in my code.
I tried looking in the Demo folder, but was unable to find any multithreaded examples.
Are there any examples available for SQLite, Firebase or MySql (I'd prefer MySql) demonstrating multithreaded use of Aurelius without having to wrap the update code in semaphores ?
Hi there,
Firebird do not have table locking.
A "Record lock" may happen if you try to update same record that was updated in other transaction not commited yet. You can setup Firebird transaction to WAIT so that instead of raising an exception it will wait for the concurrent transaction to be commited (or rolled back) before continue with the Update.
If you have many theads updating the same record (in short thansactions) than this is the way to go.
Regards,
This has nothing to do with Aurelius. Multithreading database development with Delphi (at least with most components) is like that: the same connection cannot be used from different multithreads.
This is stated in FireDAC documentation, for example:
FireDAC is thread-safe if the following conditions are met:
- A connection object and all objects associated with it (such as TFDQuery, TFDTransaction, and so on) are used by a single thread at each moment.
- FDManager is activated before threads start, by setting FDManager.Active to True.
This means that after a thread opens a query and until its processing is finished, the application cannot use this query and the connection objects in another thread. Similarly, after a thread starts a transaction and until the transaction is finished, the application cannot use this transaction and the connection objects in another thread.
Thanks for the info Wagner.
I'm pretty sure I found where my error was. I now have the following sample working
The sample is here https://pastebin.com/SMs5X4pT
There's now no semaphore protection on the database (I did add protection for a counter) and I no longer get any access violations or duplicate add errors.
Where I think my problem was, was that I executed the UpdateDatabase at the beginning of each thread and I don't think that was correct. The sample program now only initializes it once and wont start the remaining threads until UpdateDatabase has completed. It seems that if any threads start running while UpdateDatabase is active, it causes the access violations and probably other issues. It doesn't make sense to be potentially altering the schema while trying to access the data at the same time 
The sample is set for 10 threads, but I have tested it up to about 50 (definitely overkill) and it completed as expected without errors.
Thanks for the follow up. Note that you have a memory leak in your code, the boolean parameter here should be True so the lConnection is destroyed when interface is released:
lConnectionIntf := TFireDacConnectionAdapter.Create(lConnection, 'MySql', True);
Thanks for that leak info. I hadn't spotted that.