[SOLVED] Access Violation - Database is Locked

[1HuntnMan]

I think as Xenno alerted me of the problem. I'm not handling/managing transactions properly. That's the culprit that's holding up the whole show.  Trying to learn from Handoko's advice.  Thinking about revamping the whole project but going to try to figure out this transactions issue.  I shouldn't be that much of an issue. Then start over on this project and approach it with all database manipulation in the DataModule and let the Clients Mgt, Invoices, Sales Orders just be presentation to the user, no database components, move all that to the datamodule.  But, would like to learn just how to save a frigging record! LoL!

[Xenno]

Great, 1HuntnMan.

Actually, I am a little curious, did you trace on your code below?

Code: Pascal  [Select][+][-]

1.   ...
2.   try
3.     // 1) Main R/W Connection
4.     ConnectPMSDB.DatabaseName := PMS_DB;
5.     ConnectPMSDB.Connected := True;
6.  
7.     // 2) Pragmas (run AFTER connect)
8.     ConnectPMSDB.ExecuteDirect('PRAGMA journal_mode=WAL;');
9.     ConnectPMSDB.ExecuteDirect('PRAGMA synchronous=NORMAL;');
10.     ConnectPMSDB.ExecuteDirect('PRAGMA foreign_keys=ON;');
11.     ConnectPMSDB.ExecuteDirect('PRAGMA busy_timeout=5000;');
12.  
13.     // 3) Transaction wiring (not starting StartTransaction here)
14.     TransPMSDB.DataBase := ConnectPMSDB;
15.     TransPMSDB.Action := caCommitRetaining;
16.     TransPMSDB.Active:= True;
17.   ...
18.  

It should be error "transaction not set" on the line of PRAGMA journal_mode.

If we change the code like below, we will have another error: "cannot change to into wal mode"

Code: Pascal  [Select][+][-]

1.   ...
2.   try
3.     // 1) Main R/W Connection
4.     ConnectPMSDB.DatabaseName := PMS_DB;
5.     ConnectPMSDB.Connected := True;
6.  
7.     // 3) Transaction wiring (not starting StartTransaction here)
8.     TransPMSDB.DataBase := ConnectPMSDB;
9.     TransPMSDB.Action := caCommitRetaining;
10.     TransPMSDB.Active:= True;
11.  
12.     // 2) Pragmas (run AFTER connect)
13.     ConnectPMSDB.ExecuteDirect('PRAGMA journal_mode=WAL;');
14.   ...
15.  

You may want to skip PRAGMA journal_mode line and see how it is going. I agree WAL is the recommended mode for multi connections or multithreaded. WAL helps such as running lengthy database processes (involves writing) in separate thread smoother. If the main thread tries reading the busy database, WAL works good preventing locking issues and maintains performance.

Usually we need a hack to change journal_mode. I use SQLite in my apps with journal mode WAL or TRUNCATE but I don't use SQLdb. Especially because I don't use Data Controls components in those apps.

[1HuntnMan]

Thanks for that, I finally fixed all these issues and mainly learning from another post, I was freeing objects in FormClose that are already freed and doing things in FormClose that should be in FormTerminate.  Also, when I was running not in Lazarus with debugger running but just running the app.exe I wasn't getting these DataBase is locked or Access errors.  So, the culpret was freeing objects that are automatically freed and doing things in form close that shouldn't be done. Awesome and thanks!