[SOLVED] Database is Locked error???

[1HuntnMan]

I don't know if this is because I just updated my Lazarus yesterday from v4.6 to v4.8. Anyway, mostly finished with this application and was testing a few days ago and most everything working as far as adding, deleting, updating records in all the forms. But today, I can't edit or add any records because I get "Database is Locked". I hate this error, it doesn't make sense.

Here's the way I save in every form. I have an Utils unit that I have added several repeat procedures and just pass the query to the Utils unit.
From my Utils unit, here's the way I coded to SaveQueryChanges, SafeClose and EnsureQueryReady:

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

1. procedure SaveQueryChanges(Q: TSQLQuery; Trans: TSQLTransaction);
2. begin
3.   if Q = nil then Exit;
4.  
5.   if Q.State in [dsEdit, dsInsert] then
6.     Q.Post;
7.  
8.   if Q.ChangeCount > 0 then
9.     Q.ApplyUpdates;
10.  
11.   if Q.Active then
12.     Q.Close;
13.  
14.   if Assigned(Trans) and Trans.Active then
15.   begin
16.     Trans.Commit;
17.     Trans.StartTransaction;
18.   end;
19. end;
20.  
21. procedure SafeClose(const DS: TDataSet);
22. begin
23.   //=> Close if it's assigned and currently open
24.   if not Assigned(DS) then Exit;
25.   if not DS.Active then Exit;
26.  
27.   //=> Don’t close while actively editing...
28.   if DS.State in [dsEdit, dsInsert] then Exit;
29.  
30.   { Cancel cached updates before closing, (optional but useful) }
31.   if DS is TSQLQuery then
32.     TSQLQuery(DS).CancelUpdates;
33.   DS.Close;
34. end;
35.  
36. procedure EnsureQueryReady(Q: TSQLQuery; ForceRefresh: Boolean);
37. begin
38.   if not Assigned(Q) then Exit;
39.   //=> Q.Refresh; Never refresh while editing/inserting
40.   if Q.State in [dsEdit, dsInsert] then
41.     Exit;
42.   //=> If refresh, make sure no cached updates are pending
43.   if (Q.ChangeCount > 0) then
44.     Exit;
45.   if Q.Active then
46.     begin
47.       if ForceRefresh then
48.         begin
49.           Q.Close;
50.           Q.Open;
51.         end;
52.     end
53.   else
54.     Q.Open;
55. end;
56.  

So, for example in my Appointments unit testing just editing or adding a new record, which I added 15 records last week and no issues. Here's the coded for a BitBtnSave:

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

1. procedure TFrmApptsMgt.BitBtnSaveClick(Sender: TObject);
2. begin
3.   if QryAppts.FieldByName('CNTKLNAME').AsString = '' then
4.     begin
5.       ShowMessage('WARNING! No Appointment-Contact selected!');
6.       Exit;
7.     end;
8.   try
9.     if (QryAppts.State in [dsEdit, dsInsert]) or (QryAppts.ChangeCount > 0) then
10.       begin
11.         SafeClose(QryCntks);
12.         SafeClose(QryClients);
13.         SafeClose(DSrcStates.DataSet);
14.         SafeClose(DSrcCntries.DataSet);
15.         SaveQueryChanges(QryAppts, PMSDataModule.TransPMSDB);
16.         EnsureQueryReady(QryAppts, True);
17.         EnsureQueryReady(QryCntks, False);
18.         EnsureQueryReady(QryClients, False);
19.         ReOpenQuery(PMSDataModule.QryStates);
20.         ReOpenQuery(PMSDataModule.QryCntries);
21.         UpDateTTLAppts;
22.       end;
23.   except on E: Exception do
24.     begin
25.       PMSDataModule.TransPMSDB.Rollback;
26.       ShowMessage('Error saving current Appointment record: '+E.Message);
27.     end;
28.   end;
29. end;
30.  

Some of this code may not be necessary but just trying to cover all bases because of this 'Database is Locked' error!

Any ideas, I would like to try anyone's advice, Thanks!

[Handoko]

Without the whole source code to examine, I cannot know which lines caused the problem. But I found something doesn't seem right to me.

In your SaveQueryChanges, you do Transaction.Commit then start a new Transaction. I believe you should start a transaction before commit. This is how I will do database update:

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

1.   Connection              := TSQLite3Connection.Create(nil);
2.   Connection.DatabaseName := FActiveDatabaseFile;
3.   Connection.Transaction  := TSQLTransaction.Create(nil);
4.   Connection.Open;
5.   Connection.StartTransaction;
6.   try
7.     Connection.ExecuteDirect(
8.       'INSERT INTO TBLPRODUCT '            +
9.       '(PRODUCTID, NAME, PRICE) '          +
10.       'VALUES ('                           +
11.       '''' + edtProductID.Text    + ''', ' +
12.       '''' + edtProductName.Text  + ''', ' +
13.       '''' + edtProductPrice.Text + ''');'
14.       );
15.     Connection.Transaction.Commit;
16.   finally
17.     Connection.Transaction.Free;
18.     Connection.Free;
19.   end;

Again, and sorry tell you. Your code is spaghetti code, which basically means maintaining, adding new feature and debugging become unnecessary harder. In my example code above as you can see, all the processes for updating the TBLPRODUCE are put tightly in one go.

https://en.wikipedia.org/wiki/Spaghetti_code

[Soner]

Are you using Sqlite?
It can only be opened once at a time.
May be it is opened in design form., Close it and Open it in tform.oncreate or somewhere Else in the program.

Or your program is crashed and sqlite-db is still öpen.

[Zvoni]

Without the whole source code to examine, I cannot know which lines caused the problem. But I found something doesn't seem right to me.

In your SaveQueryChanges, you do Transaction.Commit then start a new Transaction. I believe you should start a transaction before commit. This is how I will do database update:

Wrong. He queries if his Transaction is Active, which implies "started".
He commits, and starts a new one.

I just wonder why that way, and not just CommitRetaining?

regarding "Database is locked"
In SQLite this is a "classic"
1) Do you have pending Transactions somewhere else? I had that same Error since i had a pending Transaction in DB Browser for SQLite while my program was running
2) Use Journal-Mode = WAL and add to OpenFlags of the connection: sofNoMutex

[Thaddy]

2) Use Journal-Mode = WAL and add to OpenFlags of the connection: sofNoMutex

But only if there is no other way: if multple users or programs can access the same SQlite db at the same time the mutex is essential. IOW that is a stop-gap solution. I guess Zvoni agrees with that.
There is a slight risk you may damage your data.
In a single user scenario it is safe.

[Zvoni]

2) Use Journal-Mode = WAL and add to OpenFlags of the connection: sofNoMutex

But only if there is no other way: if multple users or programs can access the same SQlite db at the same time the mutex is essential. IOW that is a stop-gap solution. I guess Zvoni agrees with that.
There is a slight risk you may damage your data.
In a single user scenario it is safe.

Correct.
Though OP mentions "testing", so i'd hazard a guess, he's running his program while having DB Browser for SQlite open and/or he's using the TSQLQuery-Components on the Form with a "direct" connection.

But looking at his Code again: Why in blazes is he passing the Transaction as a separate argument to his SaveQuery-Procedure?
TSQLQuery has to be connected to a Transaction-Object, and exposes it via Property

Untested

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

1. procedure SaveQueryChanges(Q: TSQLQuery);
2. begin
3.    if Not Assigend(Q) then Exit;
4.    if Q.State in [dsEdit, dsInsert] then Q.Post;
5.    if Q.ChangeCount > 0 then Q.ApplyUpdates;  
6.    Q.Transaction.CommitRetaining;  
7.    Q.Close;
8. end;

[1HuntnMan]

My database is SQLite3. Big problem, forgot that SQLite3 is mostly designed as a single user Data Base Mgt., the whole database is locked(#&*@) and had DBBrowser up with my Database loaded. Thanks Zvoni! Went back to testing and back in business.