How to handle locked file errors?

[vfclists]

I have been getting errors trying to write to a SQLite database in quick succession.

Reading the SQLite faq at http://www.sqlite.org/faq.html#q5 indicates that locking problems can be fixed by calls to sqlite3_busy_handler() and sqlite3_busy_timeout().

Is there support for this somewhere in the Zeoslib component or in the code itself?

[mdaems]

This code isn't provided yet. However, when you look at the implementation of the encryption option in TZSQLiteConnection.Open it should be very easy to implement sqlite3_busy_timeout().

The busy_handler is a little more difficult. That's more like the problem discussed here

If you can implement one of these things I'll be happy to add it to the zeoslib codebase.

Mark

[johank]

I implemented support for sqlite3_busy_timeout() by adding the following piece of code to TZSQLiteConnection.Open()

Code: Select all

  { Set busy timeout if requested }
  Timeout_ms := StrToIntDef(Info.Values['busytimeout'], -1);
  if Timeout_ms >= 0 then
  begin
    FPlainDriver.BusyTimeout(FHandle, Timeout_ms);
  end;

Now you can set a busy timeout for the connection like this:
conn := TZConnection.Create(nil);
conn.Protocol := 'sqlite-3';
conn.Database := 'TestDB.sqlite';
conn.ReadOnly := False;
conn.Properties.Add('busytimeout=30000'); // Timeout in milliseconds.

This solved my "database is locked" problems when multiple threads were accessing the same SQLite DB using separate connections.

[seawolf]

Thank you, but In this way this function can be called just when a connection
is tring to connect to the db. This function can be used also before lock a table so I think every time it should be necessary disconnect and connect to the db.

[mdaems]

seawolf,
This would be a sqlite specific feature, so it should remain in the dbc layer.
Do you think it would be useful to add a TZSQLiteConnection method to set busy_timeout?

OR... do I see correctly this is not a 'per connection' setting but a setting influencing all connections that use the connection? In that case it shouldn't be tied to TZSQLiteConnection but probably to the complete driver.
And I think that one is easily available as ZDbcSqLite.SQLiteDriver. But I'm not sure if it's easy to use it's methods as it's declared as an IZDriver.

What do you think?

Meanwhile I'm commiting johank's patch.

Mark