[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
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"
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
[sqlite] Best approach for applying DB migrations to an existing SQLite DB
Our application is an ASP.NET MVC application with an N-Tier architecture running on Mono in Linux. Interaction with SQLite DB is done through ADO.Net using System.Data.SQLite provider. Can you suggest best approach for implementing and applying migrations to the DB? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] [System.Data.SQLite] BusyTimeout and DefaultTimeout are not picked up from the connection string
Hello,
The question is related to the System.Data.SQLite library's connection
string parsing.
We have defined the connection string as follows:
data
source=;DateTimeKind=Utc;Version=3;Pooling=True;Synchronous=Off;journal
mode=Memory;busytimeout=30001;default timeout=31
When a new SQLiteConnection instance is created using the above connection
string, the BusyTimeout and DefaultTimeout are still shown as 0 and 30
respectively on that connection. However, once we open the connection by
calling .Open() method, the values from the connection string are seen in
connection properties.
Expected busy timeout when a db lock occurs is now 30 sec as per the
connection string and as reflected on the connection object. But when we
log the elapsed time from the opening of the connection to its timing out
after the db lock error (inside the catch block) we see that the timeout is
around 60 sec.
--
var stopwatch = Stopwatch.StartNew();
try
{
var conn = new SQLiteConnection(ConnectionString);
// Busy Timeout = 0, Default Timeout = 30
conn.Open();
// Busy Timeout = 30001, Default Timeout = 31
// DB lock while executing the command created on this connection
}
catch
{
log(stopwatch.ElapsedMilliseconds); // around 6
stopwatch.Stop();
}
---
Please let me know if we are doing anything wrong.
Thanks in advance.
Rahul
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
