How to handle locked file errors?

Forum related to SQLite

Moderators: gto, cipto_kh, EgonHugeist

vfclists
Fresh Boarder
Fresh Boarder
Posts: 15
Joined: 30.10.2007, 00:07

How to handle locked file errors?

Postby vfclists » 01.06.2010, 10:10

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?

User avatar
mdaems
Zeos Project Manager
Zeos Project Manager
Posts: 2766
Joined: 20.09.2005, 15:28
Location: Brussels, Belgium
Contact:

Postby mdaems » 14.06.2010, 23:22

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
Image

johank
Fresh Boarder
Fresh Boarder
Posts: 2
Joined: 24.08.2011, 13:10

Postby johank » 24.08.2011, 13:24

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
Zeos Dev Team *
Zeos Dev Team *
Posts: 385
Joined: 04.06.2008, 19:50
Contact:

Postby seawolf » 24.08.2011, 21:07

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.

User avatar
mdaems
Zeos Project Manager
Zeos Project Manager
Posts: 2766
Joined: 20.09.2005, 15:28
Location: Brussels, Belgium
Contact:

Postby mdaems » 30.08.2011, 13:21

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
Image


Return to “SQLite”

Who is online

Users browsing this forum: No registered users and 1 guest