1

I am using a SQLite Database in my C# program.

The program saves values every second in the DB, and also checks everytime if the tables already exist:

class DataManager : IDataManager
{
    private bool machineTableIsCreated = false;
    private bool machineAlreadyInDb = false;
    private bool machineValueTableAlreadyExists = false;

    public DataManager()
    {
        machineTableIsCreated = CheckIfMachineTableExists();
    }

    //Writing the Values in a CSV-File
    public void WriteInCsv(dynamic value, string name)
    {
        string strFilePath = @"C:\SVN\" + name + ".csv";
        File.AppendAllText(strFilePath, Convert.ToString(value) + "\n");
    }

    //Writing the Values into the Database
    public void WriteInDb(string name, string netId, int port, List<BeckhoffVariable> variables)
    {
        BeckhoffMachineDto machine = new BeckhoffMachineDto
        {
            Name = name,
            NetId = netId,
            Port = port,
            Variables = variables
        };

        
        if (!machineTableIsCreated)
        {
            CreateMachineTable();
            machineTableIsCreated = true;
        }

        machineAlreadyInDb = CheckIfMachineIsAlreadyInDb(machine);
        if (!machineAlreadyInDb)
        {
            InsertMachineInDb(machine);
        }

        machineValueTableAlreadyExists = CheckIfMachineValueTableExists(machine.Name);
        if (!machineValueTableAlreadyExists)
        {
            //Creates Table for the Machine where its values are stored
            CreateTable(machine.Name);
        }

        //Inserts the Value read from the Machine into the DB
        InsertData(machine.Name, machine.Variables);
    }

    private bool CheckIfMachineValueTableExists(string name)
    {
        //Open connection to DB 
        using (var connection = new SQLiteConnection("Data Source=C:/SVN/trunk/database.db;PRAGMA journal_mode=WAL;Pooling=True;"))
        {
            connection.Open();

            //Creating Command to execute
            using (var command = connection.CreateCommand())
            {
                command.CommandText = $"SELECT * FROM SQLITE_MASTER WHERE type ='table' AND name ='{name}';";

                //read result of command
                using (var reader = command.ExecuteReader())
                {
                    //Check if result isnt null
                    if (reader.HasRows)
                    {
                        //connection.Close();
                        return true;
                    }
                    //connection.Close();
                    return false;
                }
            }
        }
    }

    private bool CheckIfMachineIsAlreadyInDb(BeckhoffMachineDto machine)
    {
        using (var connection = new SQLiteConnection("Data Source=C:/SVN/trunk/database.db;PRAGMA journal_mode=WAL;Pooling=True;"))
        {
            connection.Open();

            using (var command = connection.CreateCommand())
            {
                command.CommandText = $"SELECT * FROM machines WHERE Name = '{machine.Name}' AND NetId = '{machine.NetId.Replace('.', '-')}' AND Port = '{machine.Port}';";
                command.CommandTimeout = 1;

                using (var reader = command.ExecuteReader())
                {
                    if (reader.HasRows)
                    {
                        //connection.Close();
                        return true;
                    }
                    //connection.Close();
                    return false;
                }
            }
        }
    }

    private bool CheckIfMachineTableExists()
    {
        using (var connection = new SQLiteConnection("Data Source=C:/SVN/trunk/database.db;PRAGMA journal_mode=WAL;Pooling=True;"))
        {
            connection.Open();

            using (var command = connection.CreateCommand())
            {
                command.CommandText = "SELECT name FROM sqlite_master WHERE type='table' AND name='machines';";
                command.CommandTimeout = 1;

                using (var reader = command.ExecuteReader())
                {
                    if (reader.HasRows)
                    {
                        //connection.Close();
                        return true;
                    }
                    //connection.Close();
                    return false;
                }
            }
        }
    }

    private void InsertMachineInDb(BeckhoffMachineDto machine)
    {
        using (var connection = new SQLiteConnection("Data Source=C:/SVN/trunk/database.db;PRAGMA journal_mode=WAL;Pooling=True;"))
        {
            connection.Open();

            using (var command = connection.CreateCommand())
            {
                command.CommandText = "INSERT INTO machines (Name, NetId, Port, VariableCoolingFlow, VariableCoolingIn, VariableCoolingOut, " +
                "VariableTempAc, VariableTempDc, VariableTempAcBelow, VariableTempDcBelow) VALUES(@machineName, @machineNetId," +
                " @machinePort, @machineVar0, @machineVar1, @machineVar2, @machineVar3, @machineVar4, @machineVar5, @machineVar6); ";

                command.CommandType = CommandType.Text;
                command.Parameters.Add(new SQLiteParameter("@machineName", machine.Name));
                command.Parameters.Add(new SQLiteParameter("@machineNetId", machine.NetId.Replace('.', '-')));
                command.Parameters.Add(new SQLiteParameter("@machinePort", machine.Port));
                command.Parameters.Add(new SQLiteParameter("@machineVar0", machine.Variables[0].Name.Replace('.', '-')));
                command.Parameters.Add(new SQLiteParameter("@machineVar1", machine.Variables[1].Name.Replace('.', '-')));
                command.Parameters.Add(new SQLiteParameter("@machineVar2", machine.Variables[2].Name.Replace('.', '-')));
                command.Parameters.Add(new SQLiteParameter("@machineVar3", machine.Variables[3].Name.Replace('.', '-')));
                command.Parameters.Add(new SQLiteParameter("@machineVar4", machine.Variables[4].Name.Replace('.', '-')));
                command.Parameters.Add(new SQLiteParameter("@machineVar5", machine.Variables[5].Name.Replace('.', '-')));
                command.Parameters.Add(new SQLiteParameter("@machineVar6", machine.Variables[6].Name.Replace('.', '-')));

                command.ExecuteNonQuery();
            }
            //connection.Close();
        }
    }

    private static void CreateMachineTable()
    {
        using (var connection = new SQLiteConnection("Data Source=C:/SVN/trunk/database.db;PRAGMA journal_mode=WAL;Pooling=True;"))
        {
            connection.Open();

            using (var command = connection.CreateCommand())
            {
                command.CommandText = "CREATE TABLE machines (Name VARCHAR(40), NetId VARCHAR(30), Port VARCHAR(40), VariableCoolingFlow VARCHAR(45)," +
                " VariableCoolingIn VARCHAR(45), VariableCoolingOut VARCHAR(45), VariableTempAc VARCHAR(45), VariableTempDc VARCHAR(45)," +
                " VariableTempAcBelow VARCHAR(45), VariableTempDcBelow VARCHAR(45));";
                command.ExecuteNonQuery();
            }
            //connection.Close();
        }
    }

    //Creates The Tables for the Values a Machine has
    private static void CreateTable(string name)
    {
        using (var connection = new SQLiteConnection("Data Source=C:/SVN/trunk/database.db;PRAGMA journal_mode=WAL;Pooling=True;"))
        {
            connection.Open();

            using (var command = connection.CreateCommand())
            {
                command.CommandText = $"CREATE TABLE {name} (TemperatureCoolingFlow VARCHAR(20), TemperatureCoolingOut VARCHAR(20), TemperatureCoolingIn VARCHAR(20)," +
                " TemperatureDTSAc VARCHAR(20), TemperatureDTSDc VARCHAR(20), TemperatureDTSAcBelow VARCHAR(20), TemperatureDTSDcBelow VARCHAR(20))";

                command.ExecuteNonQuery();
            }
            //connection.Close();
        }
    }

    //Inserts the Machine Values in the Table of the Machine
    private static void InsertData(string name, List<BeckhoffVariable> variables)
    {
        using (var connection = new SQLiteConnection("Data Source=C:/SVN/trunk/database.db;PRAGMA journal_mode=WAL;Pooling=True;"))
        {
            connection.Open();

            using (var command = connection.CreateCommand())
            {
                command.CommandText = $"INSERT INTO {name} (TemperatureCoolingFlow, TemperatureCoolingOut, TemperatureCoolingIn, TemperatureDTSAc," +
                $" TemperatureDTSDc, TemperatureDTSAcBelow, TemperatureDTSDcBelow) VALUES(@machineVar0, @machineVar1, @machineVar2, " +
                $" @machineVar3, @machineVar4, @machineVar5, @machineVar6); ";

                command.CommandType = CommandType.Text;
                command.Parameters.Add(new SQLiteParameter("@machineVar0", Convert.ToString(variables[0].Value)));
                command.Parameters.Add(new SQLiteParameter("@machineVar1", Convert.ToString(variables[1].Value)));
                command.Parameters.Add(new SQLiteParameter("@machineVar2", Convert.ToString(variables[2].Value)));
                command.Parameters.Add(new SQLiteParameter("@machineVar3", Convert.ToString(variables[3].Value)));
                command.Parameters.Add(new SQLiteParameter("@machineVar4", Convert.ToString(variables[4].Value)));
                command.Parameters.Add(new SQLiteParameter("@machineVar5", Convert.ToString(variables[5].Value)));
                command.Parameters.Add(new SQLiteParameter("@machineVar6", Convert.ToString(variables[6].Value)));

                command.ExecuteNonQuery();
            }
            //connection.Close();
        }
    }

    //Returns a List of all Machines in the Database
    public List<BeckhoffMachineDto> getAllMachines()
    { 
        List<BeckhoffMachineDto> machines = new List<BeckhoffMachineDto>();

        using (var connection = new SQLiteConnection("Data Source=C:/SVN/trunk/database.db;PRAGMA journal_mode=WAL;Pooling=True;"))
        {
            connection.Open();

            machineTableIsCreated = CheckIfMachineTableExists();
            if (!machineTableIsCreated)
            {
                CreateMachineTable();
                machineTableIsCreated = true;
            }

            using (var command = connection.CreateCommand())
            {
                command.CommandText = "SELECT * FROM machines";

                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        BeckhoffMachineDto currMachine = new BeckhoffMachineDto();
                        currMachine.Name = (string)reader[0];
                        currMachine.NetId = reader[1].ToString().Replace('-', '.');
                        currMachine.Port = Convert.ToInt32(reader[2].ToString());
                        currMachine.Variables = new List<BeckhoffVariable>();

                        for (int i = 3; i <= 9; i++)
                        {
                            currMachine.Variables.Add(new BeckhoffVariable(reader[i].ToString().Replace('-', '.')));
                        }
                        machines.Add(currMachine);
                    }
                    //connection.Close();
                }
            }
        }
        return machines;
    }

    //Deletes the Machine from machines Table and deletes the whole Table from the given machine
    public void DeleteMachine(BeckhoffMachineDto selectedMachine)
    {
        using (var connection = new SQLiteConnection("Data Source=C:/SVN/trunk/database.db;PRAGMA journal_mode=WAL;Pooling=True;"))
        {
            connection.Open();

            using (var command = connection.CreateCommand())
            {
                command.CommandText = $"DROP TABLE {selectedMachine.Name}";
                command.ExecuteNonQuery();

                command.CommandText = $"DELETE FROM machines WHERE Name = '{selectedMachine.Name}' AND NetId = '{selectedMachine.NetId.Replace('.', '-')}' AND Port = '{selectedMachine.Port}'";
                command.ExecuteNonQuery();
            }
            //connection.Close();
        }
    }
}

The problem is that it gets locked quite a few times while running the program.

I already have WAL Mode on and Pooling on True.

I don't really get how it still gets locked.

SQLite error (5): database is locked in "SELECT * FROM machines WHERE Name = 'Machine1' AND NetId = '10.10.10.1.1.1' AND Port = '111';"

SQLite error (5): database is locked in "SELECT * FROM machines WHERE Name = 'Machine1' AND NetId = '10.10.10.1.1.1' AND Port = '111';"

SQLite error (5): database is locked in "SELECT * FROM machines WHERE Name = 'Machine1' AND NetId = '10.10.10.1.1.1' AND Port = '111';"

SQLite error (5): database is locked in "SELECT * FROM machines WHERE Name = 'Machine1' AND NetId = '10.10.10.1.1.1' AND Port = '111';"

SQLite error (5): database is locked in "SELECT * FROM machines WHERE Name = 'Machine1' AND NetId = '10.10.10.1.1.1' AND Port = '111';"

The Problem is that while I write the values I get into the DB, it selects something from the DB and throws an Database locked error but WAL and Pooling should prevent these.

Has anyone an idea of what I could do to solve my problem?

Dominique
  • 16,450
  • 15
  • 56
  • 112
Vini121
  • 41
  • 9
  • I don't know WAL mode and Pooling option but how many and what kind of processes or applications can access the db file at the same time? Are they one single project or many? Which ADO.NET provider are you using, and possibly which ODBC driver, or what any other thing? Does all software use the same access architecture and version to the database engine? –  Aug 06 '21 at 07:32
  • Its only one project. The WriteInDb Method is called every time a new value is written for a machine, I have a few machines. Every machine has its own DataManager class. I never specified an ADO.NET provider and no ODBC driver. – Vini121 Aug 06 '21 at 07:37
  • WAL should prevent database locking when Reading and Writing at the same time and pooling makes any open connection reusable. – Vini121 Aug 06 '21 at 07:39
  • • "*I have a few machines*" => Do you mean you use SQLite in remoting mode for several computers toward one having the server? Thus you should create a [multi-tier](https://en.wikipedia.org/wiki/Multitier_architecture) for your [client-server](https://en.wikipedia.org/wiki/Client%E2%80%93server_model) architecture to be more robust. • "*I never specified an ADO.NET provider and no ODBC driver*" => Where does SQLiteConnection come from? Microsoft? SQLite.org? DotConnect? SQLiteNet little ORM? Other? –  Aug 06 '21 at 07:40
  • Oh sorry, Machines are a class in my Project and Connects to an Machine I have, which sends me Values every second. Honestly, I dont know what ADO.NET is i just connect with my Database.db file on my Computer but I assume its SQLite.org – Vini121 Aug 06 '21 at 07:42
  • "*I don't know what ADO.NET*" => How did you create your sqlite project? Which assambly did you put in references? Which nuget have you installed? What website did you read before starting all of this? You must know that. There is also SQLitePCL, and tons of providers around the world... thus many implementations in addition to `sqlite3.exe` not necessarily used especially by 100% managed providers, not to mention the multiplatform. –  Aug 06 '21 at 07:50
  • 1
    I installed the System.Data.SQLite NuGet Packages from SQLite.org – Vini121 Aug 06 '21 at 07:54
  • 1
    And i also have the SQLitePCLRaw.core – Vini121 Aug 06 '21 at 07:58
  • 1
    I think I found the problem: I cant activate WAL mode in SQLite versions under 3.7 but how do I know wich Version I have and how do I update it to a newer Version? – Vini121 Aug 06 '21 at 08:17
  • Why did you install both SQLite.org & SQLitePCLRaw? This can be the source of the problem if you use both at the same time, thus you may have concurrency, incompatibilities, conflicts and collisions issues... You should only use one, or else BOOM! That said, I insist on the *perhaps* word, because I've not tested such a thing. But I know that using the ODBC Driver in one project and SQLite Net ORM in another, on the same DB file, at the same time, may cause some exceptions... **and problems like yours here**: *try to use only one ADO.NET provider or driver for all your project using the file*. –  Aug 06 '21 at 08:27
  • P.S. You should only use multiple providers for isolated single process migration and maintenance or such an operation. –  Aug 06 '21 at 08:30
  • 1
    Thank your for your help, but unfortunately that wasnt my problem. I really dont know how to fix the Database locked error so I assume I have to use a different database than sqlite. Very sad :( – Vini121 Aug 06 '21 at 08:42
  • 1
    If that's not this, I have no idea, sorry. I only use SQLiteNet ORM now, after I ditched the great but slow SQLite ODBC driver, and I found all the other providers tested years after years not interesting and complicated or not working well with WinForms and data bindings, thus I don't know about them, and I'm not so advanced in dealing with SQLite concurrency. Therefore, if no one can help you, try redesigning your system to create a true multi-tiered client-server to have a clean and robust architecture. –  Aug 06 '21 at 08:53

1 Answers1

2

Apparently, the solution to this problem is to change the synchronous pragma of the sqlite database to NORMAL! When using WAL mode the best setting for the synchronous pragma is NORMAL but the default of the db is FULL. But FULL doesnt work good with WAL. When I changed it, there were no longer database locked errors.

Vini121
  • 41
  • 9
  • 1
    So the Normal synchronous mode is not the right answer. It helps to minimize the locked errors to a minimum, but they can still come. Try synchronous mode off or other different pragma settings, so your project can work best! – Vini121 Aug 11 '21 at 06:28
  • 1
    And for future projects: maybe dont use SQLite if you have many database accesses throughout your whole programm, wich all use the database at a small interval. – Vini121 Aug 11 '21 at 06:30