Resolved How do I prevent or solve 'database is locked' error in sqlite?

Kamen

Member
Joined
Nov 30, 2020
Messages
16
Programming Experience
1-3
Hello, everyone.
I have some problems in c# SQLite data update and creating.
I'm using SQLite as a database in my c# windows form application.
By the way, sometimes I've got a "database is locked" error in SQLite, when I try to insert or update data.
While searching the way on google, I got know that it will be able to happen because multi-threads is working or the database is in processing.
But I can't find what thread or process hurt this database updating correctly.
I want to prevent the error and finally solve this problem asap.
Thanks for your time.

C#:
static SQLiteConnection CreateConnection(string dbName)
{

    SQLiteConnection sqlite_conn;
    // Create a new database connection:
    sqlite_conn = new SQLiteConnection("Data Source=" + dbName + ".db; PRAGMA journal_mode = WAL; Version = 3; New = True; Compress = True; Connection Timeout=0 ");
    // Open the connection:
    try
    {
        sqlite_conn.Open();
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex);
    }
    return sqlite_conn;
}


sqlite_conn = CreateConnection(constants.dbName);
InsertLog(sqlite_conn, 2, "xxx", "yyyyy");


public void InsertLog(SQLiteConnection conn, int logType, string logTitle, string logContent, string logContent_2 = "")
{
    if (conn.State == ConnectionState.Closed)
    {
        conn.Open();
    }
    try
    {
        DateTime logDate = DateTime.Now;
        SQLiteCommand sqlite_cmd;
        string Createsql = "INSERT INTO " + constants.tbNames[14] + " (logType, logTitle, logContent, logContent_2, logDate) VALUES (@logType, @logTitle, @logContent, @logContent_2, @logDate)";
        sqlite_cmd = conn.CreateCommand();
        //sqlite_cmd.CommandTimeout = 0;
        sqlite_cmd.CommandText = Createsql;
        sqlite_cmd.Parameters.AddWithValue("@logType", logType);
        sqlite_cmd.Parameters.AddWithValue("@logTitle", logTitle);
        sqlite_cmd.Parameters.AddWithValue("@logContent", logContent);
        sqlite_cmd.Parameters.AddWithValue("@logContent_2", logContent_2);
        sqlite_cmd.Parameters.AddWithValue("@logDate", logDate);

        sqlite_cmd.ExecuteNonQuery();

    }
    catch (Exception e)
    {
        conn.Close();
        //Console.WriteLine("db_error==" + e);
        return;
    }
    conn.Close();
    return;
}
 
Last edited by a moderator:

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
2,416
Location
Chesapeake, VA
Programming Experience
10+
The brute force way to find out is to set a breakpoint on your CreateConnection() and see who is creating connections to the database and make sure that they close the connection before someone else calls CreateConnection().
 

Sheepings

Retired Programmer
Staff member
Joined
Sep 5, 2018
Messages
1,877
Location
UK
Programming Experience
10+
You're opening the connection on line 10. That code is rather useless, and it's not wise to be cutting into your connection string like that either. You can use a connection string builder to build up your connection string properly.

Don't pass around connection strings, or connections, commands, etc, it's totally irrelevant. There is no object orientated purpose behind doing so.
Create connection and commands where you use them, and dispose of them when you've completed using them. Generally, people use using blocks for this.

Your commands should be constructed as new commands along with your connection. With your query and connection added to your command. You might find Jan Bodnar tutorial more useful : C# SQLite tutorial - programming SQLite databases in C#
 

Kamen

Member
Joined
Nov 30, 2020
Messages
16
Programming Experience
1-3
You're opening the connection on line 10. That code is rather useless, and it's not wise to be cutting into your connection string like that either. You can use a connection string builder to build up your connection string properly.

Don't pass around connection strings, or connections, commands, etc, it's totally irrelevant. There is no object orientated purpose behind doing so.
Create connection and commands where you use them, and dispose of them when you've completed using them. Generally, people use using blocks for this.

Your commands should be constructed as new commands along with your connection. With your query and connection added to your command. You might find Jan Bodnar tutorial more useful : C# SQLite tutorial - programming SQLite databases in C#
Hello. Thanks for your time and reply.
I'll note your advice and think it will help me constantly.
By the way, I have some questions now.
While debugging my project and searching online, I found database really try to execute 2 operations at the same time.
but an error had occurred in one place and I can't find another process position.

Exception thrown: 'System.Data.SQLite.SQLiteException' in System.Data.SQLite.dll
code = Busy (5), message = System.Data.SQLite.SQLiteException (0x800007AF): database is locked
database is locked
at System.Data.SQLite.SQLite3.Step(SQLiteStatement stmt)
at System.Data.SQLite.SQLiteDataReader.NextResult()
at System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd, CommandBehavior behave)
at System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.SQLite.SQLiteCommand.ExecuteNonQuery(CommandBehavior behavior)

at STV01.DBClass.ClosingProcessRun() in C:\Users\OOBAN01\Documents\obhan_test\STV01(2020-12-02)\STV01\DBClass.cs:line 299

Here is my debug output.

From the 2 rows with an underline, you can see ExecuteReader and ExecuteNonQuery but I'm using only ExecuteNonQuery there at that time.
I think it can make DB lock error. What do you think?
Let me know your opinion.
Looking for your good answer asap.
Regards.
 
Last edited by a moderator:

Sheepings

Retired Programmer
Staff member
Joined
Sep 5, 2018
Messages
1,877
Location
UK
Programming Experience
10+
I have given you the information required to change your code. Change your code and come back and show us your new updated code with the changes that I recommended. You should also visit the link I recently sent you as it contains examples for you to do this.
 

Kamen

Member
Joined
Nov 30, 2020
Messages
16
Programming Experience
1-3
Thanks for your time.
I tried to do as you said and so it had been better than before but not solved fully yet. I think maybe I miss out some parts related to the sqlite db connecting. However I'll fix it in myself.
By the way now I have another problem in c#.
I'd run my c# application as administrator in startup. So I added some codes for this and I can see app name in startup list but it does not work when starting OS really. If I set my c# app's excutionLevel as asInvoker in app.manifest, it is working well but not work for the requireAdministrator.
Have you any other good idea?
I hope you help me asap.
Best Regards.
 

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
2,416
Location
Chesapeake, VA
Programming Experience
10+
Please open a new thread about your new question regarding running as admin/as invoker.
 

Sheepings

Retired Programmer
Staff member
Joined
Sep 5, 2018
Messages
1,877
Location
UK
Programming Experience
10+
Regarding your current issue with your database :
database is locked
You should be using using statements. Link in my signature!

As you can only have one open connection but you can share multiple commands across that one connection. It seems you need to show your code relative to how you create a connection, and how you use it when executing commands.

As for the admin issue, please take note that each new issue needs to be addressed on a new topic. So lets keep this topic about your connection problems with SQL.
 

Sheepings

Retired Programmer
Staff member
Joined
Sep 5, 2018
Messages
1,877
Location
UK
Programming Experience
10+
Show your updated code re the SQL issue?
 
Top Bottom