Re: [sqlite] [SQLite3] [Mono] [Linux] "db is locked"
Some ideas:
Sqlite may return that the database is locked immediately if it detects a
deadlock situation. Something like: a different connection holds a reserved
lock (waiting for read connections to close so it can promote to exclusive),
and the current connection tries to promote from a read lock to a reserved or
exclusive lock. Busy timeout will never resolve this situation so the
connection attempting to promote just returns that the database is locked.
You can also get guaranteed timeouts if you use multiple connections on the
same thread, or if your threading logic causes a deadlock. An example might be:
//...
DataReader dataReader = outerCmd.ExecuteReader()
foreach (var row in dataReader)
{
using (SQLiteConnection innerCon = GetNewConnection())
//attempt to write with inner conn
}
The above might happen in a called function to obscure what is happening. Such
things might be valid on a different dbms that does table or row level locking,
but can't be used with SQLite's Db level locking (I got bitten by this when I
thought 'oh yeah, change from sql server to sqlite? I'll just update the SQL
and change the providers. The logic will translate fine.')
Finally, I have seen inexplicable timeouts if I mix SQLite and
TransactionScope. In my case the sqlite connections didn't need to participate
in the transaction so I fixed it by setting enlist=false on the connection
string and not investigating further.
> On 4 Jul 2018, at 5:14 am, Simon Slavin wrote:
>
>> On 3 Jul 2018, at 8:08pm, Phani Rahul Sivalenka
>> wrote:
>>
>> As per our observation, the initial write operations on the sqlite db file
>> throw "db is locked" error. After a certain time (around an hour) write
>> operations start working and we are able to do all the operations as
>> required. [...]
>
>> “data
>> source=/var/www/html/ChargerDatabase.db;DateTimeKind=Utc;Version=3;Pooling=True;Synchronous=Off;journal
>> mode=Memory;Busy Timeout=3;Default Timeout=30”
>
> Test each of your timeouts by removing one, then the other, then both. See
> if the removal of one of them changes the described behaviour.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [SQLite3] [Mono] [Linux] "db is locked"
On 3 Jul 2018, at 8:08pm, Phani Rahul Sivalenka wrote: > As per our observation, the initial write operations on the sqlite db file > throw "db is locked" error. After a certain time (around an hour) write > operations start working and we are able to do all the operations as > required. [...] > “data > source=/var/www/html/ChargerDatabase.db;DateTimeKind=Utc;Version=3;Pooling=True;Synchronous=Off;journal > mode=Memory;Busy Timeout=3;Default Timeout=30” Test each of your timeouts by removing one, then the other, then both. See if the removal of one of them changes the described behaviour. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [SQLite3] [Mono] [Linux] "db is locked"
As per our observation, the initial write operations on the sqlite db file
throw "db is locked" error. After a certain time (around an hour) write
operations start working and we are able to do all the operations as
required.
On Mon, Jun 25, 2018 at 7:37 PM Phani Rahul Sivalenka
wrote:
> > SQLiteConnectionStringBuilder connBuilder = new
> > SQLiteConnectionStringBuilder();
>
>
>
> We are setting these configs via the connection string in xml. This is
> same as the above. The following is the connection string that we are using:
>
>
>
> “data
> source=/var/www/html/ChargerDatabase.db;DateTimeKind=Utc;Version=3;Pooling=True;Synchronous=Off;journal
> mode=Memory;Busy Timeout=3;Default Timeout=30”
>
>
>
>
>
> > Try setting the value to 3 and see if this fixes your problem.
>
>
>
> “Busy Timeout” is set to 3 as seen in the connection string.
>
> “Default Timeout” has a units of seconds as per the library and is the
> timeout used for each command. This value is set to 30 sec.
>
>
>
>
>
> > How are you disposing of your SQLiteDataReader object after the reading is
> > finished ?
>
>
>
> This is the format that we are following while using the reader or using
> ExecuteNonQuery.
>
>
>
> using (SQLiteConnection connection = GetNewSQLiteConnection())
>
> {
>
> try
>
> {
>
> connection.Open();
>
>
>
> using (SQLiteCommand command = new
> SQLiteCommand(query, connection))
>
> {
>
> // For Read Op -
>
> using (var reader =
> command.ExecuteReader())
>
> {
>
> // ….
>
>
> reader.Close();
>
> }
>
> //
> ---
>
>
>
> // For Write Op
>
> result =
> command.ExecuteNonQuery();
>
> //
> --
>
>
>
> command.Reset();
>
> }
>
> }
>
> catch(Exception ex)
>
> {
>
> // ….
>
> }
>
> finally
>
> {
>
> if (connection.State !=
> ConnectionState.Closed)
>
> {
>
> connection.Close();
>
> }
>
> }
>
> }
>
>
>
> Rahul.
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [SQLite3] [Mono] [Linux] "db is locked"
> SQLiteConnectionStringBuilder connBuilder = new
> SQLiteConnectionStringBuilder();
We are setting these configs via the connection string in xml. This is same as
the above. The following is the connection string that we are using:
“data
source=/var/www/html/ChargerDatabase.db;DateTimeKind=Utc;Version=3;Pooling=True;Synchronous=Off;journal
mode=Memory;Busy Timeout=3;Default Timeout=30”
> Try setting the value to 3 and see if this fixes your problem.
“Busy Timeout” is set to 3 as seen in the connection string.
“Default Timeout” has a units of seconds as per the library and is the timeout
used for each command. This value is set to 30 sec.
> How are you disposing of your SQLiteDataReader object after the reading is
> finished ?
This is the format that we are following while using the reader or using
ExecuteNonQuery.
using (SQLiteConnection connection = GetNewSQLiteConnection())
{
try
{
connection.Open();
using (SQLiteCommand command = new SQLiteCommand(query,
connection))
{
// For Read Op -
using (var reader = command.ExecuteReader())
{
// ….
reader.Close();
}
// ---
// For Write Op
result = command.ExecuteNonQuery();
// --
command.Reset();
}
}
catch(Exception ex)
{
// ….
}
finally
{
if (connection.State != ConnectionState.Closed)
{
connection.Close();
}
}
}
Rahul.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [SQLite3] [Mono] [Linux] "db is locked"
On 21 Jun 2018, at 10:01pm, Phani Rahul Sivalenka
wrote:
> When we do a read operation and write/update operation on the SQLite DB,
> the write operation gets timed out saying the “db is locked”.
[...]
> - Busy Timeout : 30sec
> - Default Timeout : 30sec
I'm going to assume you're doing something like
SQLiteConnectionStringBuilder connBuilder = new SQLiteConnectionStringBuilder();
connBuilder.Version = 3;
connBuilder.DataSource = filePath;
connBuilder.BusyTimeout = 30;
connBuilder.DefaultTimeout = 30;
If this isn't what you're doing please tell us.
SQLite itself uses busyTimeout. The unit of busyTimeout is milliseconds. Try
setting the value to 3 and see if this fixes your problem. I don't know
what defaultTimeout means to the library you're using but try doing the same
thing with that one.
How are you disposing of your SQLiteDataReader object after the reading is
finished ? Once the object has locked the file, while it still exists it can
maintain a lock on the file. One way to make sure that the object has been
disposed of is to use this structure:
using (SQLiteCommand cmd =
new SQLiteCommand("SELECT * FROM sqlite_master;", cnn)) {
using (SQLiteDataReader reader = cmd.ExecuteReader()) {
// extract data from reader so you don't need it any more
}
}
You will need to dispose of your writing object too. Possibly in a similar way.
Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] [SQLite3] [Mono] [Linux] "db is locked"
Hello, We have an ASP.NET Web application running with Mono on Linux. Uses System.Data.SQLite library with an interop generated with a specific set configuration to interact with the SQLite file. When we do a read operation and write/update operation on the SQLite DB, the write operation gets timed out saying the “db is locked”. In addition to this, we have a few sync threads the try to update the DB at regular intervals, this made it hard to use WAL journal mode, as the DB does not have a window where it can sync the wal file with the DB. The following are the main configurations that we applied on the DB: *Via Connection string:* - Journal Mode : Memory - Busy Timeout : 30sec - Default Timeout : 30sec - Pooling : true - Synchronous : Off *While generating the interop:* - Threading Mode : SQLITE_THREADSAFE: 1 (Serialized) Please suggest what can be done to fix this issue. Thanks. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
