Sqlite

#help

hi good morning in my application using sqlite database I have an error on database is locked please help

Top comments (4)

Vicente G. Reyes • Jul 10 '19

It would help others if you'd post the code.

Arunprasath • Jul 10 '19 • Edited

public List ListAllTablesName()
{
SQLiteConnection conn = null;
SQLiteCommand cmd = null;
SQLiteDataReader reader = null;
List TablenameList = new List();
//SqlCommandText = "SELECT tbl_name FROM sqlite_master";
try
{
using (conn = new SQLiteConnection(DataFactory.GetVitteBusinessSuiteConnectionString()))
{
using (cmd = new SQLiteCommand())
{
cmd.Connection = conn;
cmd.CommandText = "select name from sqlite_master where type='table' order by name";
conn.Open();
using (reader = cmd.ExecuteReader())
{
while (reader.Read())
{
string TableName = string.Empty;
if (reader["name"] != DBNull.Value)
TableName = Convert.ToString(reader["name"]);

                            TablenameList.Add(TableName);
                        }
                    }
                }
            }
            return TablenameList;
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
        finally
        {
            if (reader != null)
            {
                reader.Dispose();
            }
        }
    }

Roy Keene • Jul 15 '19 • Edited

Hi there,

There are a few reasons this may be the case and a few things you can do about it. First, let's talk about SQLite's model and how it achieves consistency across multiple concurrent processes and what the effects of this are.

In general, SQLite allows a single concurrent writer and multiple concurrent readers. However, to deal with the fact that transactions need to provide a consistent view, in some modes reads block writes. The journal_mode pragma documents what the various modes are and the reasons they have the properties they do.

When a database is locked, SQLite considers it busy. You can specify how long SQLite should wait for a busy database, but the default is not to wait and return that state right away so your application can do something else and retry.

The combination of these two things means you can end up with an error from SQLite about the database being busy pretty often. Fortunately there's a few things you can do about this:

Batch up and retry failed transactions on the busy database when it is not busy -- this is the most robust strategy, since a busy database can still occur with other options
Switch to the WAL journal mode, which means readers can't block writers, and writers can't block readers -- this is the easiest option but you can still end up with a busy database where multiple writers are competing, so if the database is heavily used you will still need to implement a better approach or at least a way to retry failed transactions semantically