sqlite3 database locked with 7.2.12/8.0.0, ok with 7.1.4

[olivluca]

Hello,

I rebuilt an old project with the (almost) latest lazarus 3.2/fpc 3.2.2, windows 32 bits.
Just one connection to an sqlite3 database with tiNone transaction and AutoCommit, an extension and a bunch of TZQuery/TZReadOnlyQuery to show the data in TDBGrids.
Foreign keys are enabled.
I realized that when I use zeos 7.2.12, the database is locked (I cannot insert/delete/edit from another program and the sqlite console).
I tried to build it using 7.1.4 and I don't have this problem.
I checked the differences and I see nothing relevant in the sqlite transaction management between the two versions (there are no transactions since I'm using tiNone).
I tried removing my extension, no change, disabling foreign keys, same.
I added "writeln" to the following methods to see the queries sent to sqlite (I couldn't get the whole picture using TZSQLMonitor) and I see no difference (only a couple of different pragmas, which, once removed from 7.2.12, made no change at all).

• TZSQLiteBaseDriver.Execute

• TZSQLiteBaseDriver.Prepare

• TZSQLiteBaseDriver.Prepare_v2

• TZSQLiteBaseDriver.Prepare16

Maybe I missed some method?

Now, you'll say I should upgrade to 8.0.0, I tried it but it shows the same problem (though I didn't yet add the writeln to it to see the queries).


The sqlite3.dll is quite old (3.21.0) but I prefer to stick to a known good version for my application (besides, it's the same one for7.1.4, 7.2.12 and 8.0.0).

I could just build this project with 7.1.4 and forget about it, but I'd like to find why one version works and the other two don't.

[olivluca]

After adding more writelns (in methods step, finalize and reset of the plain driver) I found that for some of the queries the sqlite statement aren't reset with 7.2.12 (hence keeping the database locked) while they are reset in 7.1.4.
I couldn't find the cause yet, it's a TZReadOnlyQuery with a simple "select * from referencias". There are 3 such queries in the project, used as a lookup source for another query, If I don't open those queries the problem goes away.
There are more funky things happening in 7.2.12 (like some of the statements are reset twice in a row).
For the record, this is the definition of the TZReadOnlyQuery

Code: Select all

  object referencias: TZReadOnlyQuery
    Connection = Datos.connection
    SQL.Strings = (
      'select * from referencias'
    )
    Params = <>
    left = 176
    top = 192
    object referenciasrefid: TLargeintField
      FieldKind = fkData
      FieldName = 'refid'
      Index = 0
      LookupCache = False
      ProviderFlags = [pfInUpdate, pfInWhere]
      ReadOnly = False
      Required = False
    end
    object referenciasreferencia: TStringField
      DisplayWidth = 255
      FieldKind = fkData
      FieldName = 'referencia'
      Index = 1
      LookupCache = False
      LookupDataSet = referencias
      LookupKeyFields = 'referencia'
      LookupResultField = 'referencia'
      ProviderFlags = [pfInUpdate, pfInWhere]
      ReadOnly = False
      Required = False
      Size = 255
    end
    object referenciasdescripcion: TStringField
      DisplayWidth = 255
      FieldKind = fkData
      FieldName = 'descripcion'
      Index = 2
      LookupCache = False
      ProviderFlags = [pfInUpdate, pfInWhere]
      ReadOnly = False
      Required = False
      Size = 255
    end
    object referenciasocupacion: TLongintField
      DisplayWidth = 10
      FieldKind = fkData
      FieldName = 'ocupacion'
      Index = 3
      LookupCache = False
      ProviderFlags = [pfInUpdate, pfInWhere]
      ReadOnly = False
      Required = False
    end
    object referenciascantidad_por_caja: TLongintField
      DisplayWidth = 10
      FieldKind = fkData
      FieldName = 'cantidad_por_caja'
      Index = 4
      LookupCache = False
      ProviderFlags = [pfInUpdate, pfInWhere]
      ReadOnly = False
      Required = False
    end
  end

[olivluca]

Slowly getting there: the old zeos, when opening a query, fetched all the records all by itself, the new one relies on the dataset to to that, however the dataset only fetches the first 10 records, that's why the statement is not reset.
The sqldb TSQLQuery component has a "PacketRecords" property which, when set to -1, will fetch all records.
With zeos I can see no such property (maybe there is one in 8.0.0), I have to call FetchAll manually
There is a FetchRow property but it doesn't seem to do anything, at least with sqlite.

[marsupilami]

Hello,

could you please check if this works correctly with Zeos 8? We don't want to do work on Zeos 7.2 anymore if at all possible.

Best regards,

Jan

[olivluca]

As I said in the first message, I already tried 8.0.0. I didn't debug like I did 7.1.4/7.2.12 though. I'm doing it now.
Switching to 8.0.0 is kinda of a pain due to the changes in the package layout and the change from sqlite-3 to sqlite (edit: oh, and the TZParam).

[olivluca]

8.0.0 behaves the same as 7.2.12, the sequence of calls (reverse call stack) once opened is:

TDataSet.RecalcBufListSize
TDataSet.GetNextRecords
TZAbstractRODataset.GetRecord
TZAbstracrRODataset.FetchRows
TZAbstractRODataset.FetchOneRow
TZAbstractResultSet.Next
TZCachedResultSet.MoveAbsolute
TZCachedResultSet.Fetch
TZSQLiteResultSet.Next

the problem is that TDataSet.GetNextRecords does

Code: Select all

    while (FRecordCount<FBufferCount) and GetNextRecord

and since FBufferCount is 10 it only fetches 10 records.

I cannot see a property of the TZReadonlyQuery that would modify this behaviour (like the PacketRecords of TSQLQuery).

I can call fetchall right after opening the query (or put it in AfterOpen) but it's not enough (it locks the database again after a refresh). I can put it in AfterRefresh, but I'd have to do it for every query in the project (there aren't too many in this project but it's a kludge that I'm not really comfortable with, and who knows what other operation would lock the database).

I prefer to stay with 7.1.4 while it still compiles.

[olivluca]

Note that since the problem comes from TDataSet->TZAbstractRODataset (the sqlite specific bit is quite low in the hierarchy) it should affect all the other drivers, but maybe with a real server it is masked by its concurrency management.

[marsupilami]

Hello,

on drivers that need their data fetched into memory, we usually do that internally (dblib) or have the client library handle this (mysql, postgresql).

Could you maybe prepare a small example application that demonstrates the problem?

Best regards,

Jan

[olivluca]

Here it is (I used 7.1.4 and 7.2.12, for 8.0.0 you'll have to change the protocol from sqlite-3 to sqlite)

https://drive.google.com/file/d/1MhweRc ... sp=sharing

[olivluca]

on drivers that need their data fetched into memory, we usually do that internally (dblib) or have the client library handle this (mysql, postgresql).

Well, since only the first 10 records are fetched (it's the dataset doing it, not zeos) I don't know how you can do that. I guess that it wouldn't matter with, e.g., postgresql, because it's a read transaction and it wouldn't affect other connections.