[sqlite] Database is locked (wal) - purely from read-only connections/queries
Jim, I am running simple select statements against views; I am not inserting into them or anything unusual like that. Also, my earlier comment re attach database was a red herring. I hit the "statement aborts" without that. Lastly, to be clear, the "SQLite error (17): statement aborts..." is debug output that I see in Visual Studio. No actual error is thrown, though it is of course disconcerting. Thank you. Vince Scafaria
[sqlite] Database is locked (wal) - purely from read-only connections/queries
Jim, I am running simple select statements against views; I am not inserting into them or anything unusual like that. Also, my earlier comment re attach database was a red herring. I hit the "statement aborts" without that. Lastly, to be clear, the "SQLite error (17): statement aborts..." is debug output that I see in Visual Studio. No actual error is thrown, though it is of course disconcerting. Thank you. Vince Scafaria
[sqlite] Database is locked (wal) - purely from read-only connections/queries
On 2/29/16, Vince Scafaria wrote: > Richard, I can confirm that having a writable connection open first, prior > to trying any reads, does avoid the "database is locked" error. However, I > still do get "SQLite error (17): statement aborts" errors. What are the > rules I must follow to avoid getting these SQLITE_SCHEMA errors? I am not > running any SQL that I would generally consider to be altering the schema. > I'm simply doing multiple concurrent reads on read-only connections and > running INSERT/UPDATE (not CREATE/DROP) statements on the writable > connection. Thank you. > Some other process might be changing the schema. ATTACH and DETACH also change the schema, and require a reparse, as do creating new application-defined functions or collating sequences or virtual tables. The query should automatically reparse and restart with no action on the part of your code. This is not something you need to worry over. -- D. Richard Hipp drh at sqlite.org
[sqlite] Database is locked (wal) - purely from read-only connections/queries
One possibility: Does ATTACH DATABASE count as a schema change? From: Vince Scafaria Sent: Monday, February 29, 2016 10:31 PM To: 'sqlite-users at mailinglists.sqlite.org' Subject: Database is locked (wal) - purely from read-only connections/queries Richard, I can confirm that having a writable connection open first, prior to trying any reads, does avoid the "database is locked" error. However, I still do get "SQLite error (17): statement aborts" errors. What are the rules I must follow to avoid getting these SQLITE_SCHEMA errors? I am not running any SQL that I would generally consider to be altering the schema. I'm simply doing multiple concurrent reads on read-only connections and running INSERT/UPDATE (not CREATE/DROP) statements on the writable connection. Thank you. Vince Scafaria
[sqlite] Database is locked (wal) - purely from read-only connections/queries
Richard, I can confirm that having a writable connection open first, prior to trying any reads, does avoid the "database is locked" error. However, I still do get "SQLite error (17): statement aborts" errors. What are the rules I must follow to avoid getting these SQLITE_SCHEMA errors? I am not running any SQL that I would generally consider to be altering the schema. I'm simply doing multiple concurrent reads on read-only connections and running INSERT/UPDATE (not CREATE/DROP) statements on the writable connection. Thank you. Vince Scafaria
[sqlite] Database is locked (wal) - purely from read-only connections/queries
Are you using any SQL VIEWs? "You cannot DELETE, INSERT, or UPDATE a view. Views are read-only in SQLite." http://sqlite.org/lang_createview.html Jim On Mon, Feb 29, 2016 at 10:31 PM, Vince Scafaria wrote: > Richard, I can confirm that having a writable connection open first, prior > to trying any reads, does avoid the "database is locked" error. However, I > still do get "SQLite error (17): statement aborts" errors. What are the > rules I must follow to avoid getting these SQLITE_SCHEMA errors? I am not > running any SQL that I would generally consider to be altering the schema. > I'm simply doing multiple concurrent reads on read-only connections and > running INSERT/UPDATE (not CREATE/DROP) statements on the writable > connection. Thank you. > > Vince Scafaria > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] Database is locked (wal) - purely from read-only connections/queries
On 2/26/16, Vince Scafaria wrote: > I am using System.Data.SQLite in .NET and encountering "database is locked" > with wal using multiple threads from the same process running simple select > statements with (separate) read-only connections. The first database connection (in any process) that opens a WAL-mode database file must create the *-wal and *-shm files used for transaction control. (Likewise, the last connection to close a WAL-mode database will delete those files.) But because new files must be created when opening, that means that a read-only database connection cannot be the first connection to open a WAL-mode database. A read-only database connection can open and read a WAL-mode database as long as some other (read/write) thread or process has already opened it, but the read-only connection cannot be the first to open it. Try this experiment: Before running your test program, connection to the database file using the sqlite3.exe command-line client and type ".schema". Leave sqlite3.exe connected to the database - just setting there doing nothing. Then while sqlite3.exe is running, rerun your test program. Let us know if that clears your problem. -- D. Richard Hipp drh at sqlite.org
[sqlite] Database is locked (wal) - purely from read-only connections/queries
I am using System.Data.SQLite in .NET and encountering "database is locked"
with wal using multiple threads from the same process running simple select
statements with (separate) read-only connections. Please see the link below and
note the Visual Studio output window when it runs.
https://drive.google.com/open?id=0By9M2uwoQgnKUnN6Z2NoWDZLS2s
Here is the bulk of the code in case more helpful than the download:
class Program
{
///
/// Configure for x64 and hit F5 in Visual Studio
/// Check the output window and note "database is locked"
///
///
static void Main(string[] args)
{
// the db file was copied to bin
// note: the db was last opened with wal
var dbPath = new
FileInfo(Assembly.GetExecutingAssembly().Location).Directory.FullName;
var dbFile = Path.Combine(dbPath, "Simple.db3");
var csb = new SQLiteConnectionStringBuilder();
csb.DataSource = dbFile;
csb.ReadOnly = true;
var connStr = csb.ConnectionString;
RunTest(connStr);
}
private static void RunTest(string connStr)
{
var f1 = Task.Factory.StartNew(() => DoReadTest(connStr),
TaskCreationOptions.LongRunning);
var f2 = Task.Factory.StartNew(() => DoReadTest(connStr),
TaskCreationOptions.LongRunning);
var f3 = Task.Factory.StartNew(() => DoReadTest(connStr),
TaskCreationOptions.LongRunning);
var f4 = Task.Factory.StartNew(() => DoReadTest(connStr),
TaskCreationOptions.LongRunning);
Task.WaitAll(new List { f1, f2, f3, f4 }.ToArray());
}
private static void DoReadTest(string connStr)
{
var untilTime = DateTime.UtcNow.AddSeconds(10);
int numRuns = 0;
long totalMS = 0;
// hammer the db with reads
do
{
var sw = new Stopwatch();
sw.Start();
ReadTestWorker(connStr);
numRuns++;
totalMS += sw.ElapsedMilliseconds;
} while (DateTime.UtcNow < untilTime);
Console.WriteLine($"Thread {Thread.CurrentThread.ManagedThreadId}
avg ms: { totalMS / numRuns }, Total runs {numRuns }");
}
private static void ReadTestWorker(string connStr)
{
const string sql = "SELECT MAX(Id) AS MaxId FROM TestTable;";
using (var connection = new SQLiteConnection(connStr))
{
connection.Open();
using (var cmd = connection.CreateCommand())
{
cmd.CommandText = sql;
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var msg = $"Thread
{Thread.CurrentThread.ManagedThreadId} says max id is : {reader[0]}";
//Console.WriteLine(msg);
}
}
}
}
}
}
Thank you,
Vince
