Database is locked using SQLite in C#

Question

When ExecuteNonQuery(), my database gets locked but if I comment that part ExecuteReader works fine. How to avoid database getting locked? I know that first is a write into database when creating indexes and the second time is reading but there are cases when this code below works fine. I need that index to be created to gain speed. There are a lot of functions created this way to get the data from the database (which is a .rdb file) and there are times that some of them are not working

List<TargetedOutputRDB1> entityList = new List<TargetedOutputRDB1>();
//
DbDataReader reader = null;
SimulationRDB1 simulation = (SimulationRDB1)crash.Simulation;
DbConnection dbConnection = ((RDB1Connection)crash.DbConnection).DbProviderFactory.CreateConnection();
dbConnection.ConnectionString = crash.DbConnection.ConnectionString;
bool connectionWasClosed = (dbConnection.State == ConnectionState.Closed);
//
try
{
    DbCommand dbCommand = dbConnection.CreateCommand();
    dbCommand.Transaction = (((RDB1Connection)simulation.DbConnection).DbTransaction != null && ((RDB1Connection)simulation.DbConnection).DbTransaction.Connection == simulation.DbConnection ? ((RDB1Connection)simulation.DbConnection).DbTransaction : null); //must be set!
    //
    if (dbConnection.State == ConnectionState.Closed)
        dbConnection.Open();
    //

    dbCommand.CommandText = string.Format("CREATE INDEX IF NOT EXISTS TOIndexSC ON TargetedOutputs (SimulationId, CrashId)");
    dbCommand.ExecuteNonQuery();


    dbCommand.CommandText = string.Format("SELECT * FROM TargetedOutputs WHERE SimulationId={0} AND CrashId={1}", ((SimulationRDB1)crash.Simulation).ID, crash.ID);
    reader = dbCommand.ExecuteReader();

    //
    while (reader.Read())
    {
        long id = (long)reader["Id"];
        TargetedOutputRDB1 entity = new TargetedOutputRDB1(simulation, id, reader, false);
        entityList.Add(entity);
    }
    //
    return entityList.ToArray();
}

catch (Exception ex)
{
    throw ex;
}
finally
{
    if (reader != null)
        reader.Close();
    //
    if (connectionWasClosed)
        dbConnection.Close();

    //dbConnection.Dispose();
}

With getting locked do you mean the the table being inaccessible for x time? This is expected behavior for Sql as it needs to complete its index mapping before your allowed to read from the index. If the table contains a lot of data or if its a complex index (like a long varchar) it might take a 'while' before the indexing is complete. — Nick Otten, Apr 21 '16 at 14:37
Getting locked means that after execute "ExecuteNonQuery" statement and waiting a while, if I try catch the statement then I can see the error, "database is locked". — Ștefan Blaga, Apr 21 '16 at 15:02
@ȘtefanBlaga do you think you can provide a small example of database schema that could help to reproduce the problem locally? — Natalie Perret, Apr 21 '16 at 18:55
Oh a actual lock error. That means that the sqllite server is waiting for another connection to finish it works (although the connection might have long since died). See this question how to resolve it: http://stackoverflow.com/questions/151026/how-do-i-unlock-a-sqlite-database — Nick Otten, Apr 22 '16 at 06:19
This has helped me http://stackoverflow.com/a/40544228/6229375 — Dominic Jonas, Nov 11 '16 at 08:50

Database is locked using SQLite in C#

0 Answers0