为什么SQLite数据库文件被WAL模式锁定并且启用了pool ?

Question

我在c#程序中使用SQLite数据库。

程序每秒钟在DB中保存值，并且每次检查表是否已经存在:

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();
}
}
}

问题是它在运行程序时被锁定了好几次。

我已经开启了WAL模式并且池化开启了True。

我真不明白为什么它还被锁着。

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';"

问题是，当我写值我进入DB，它从DB中选择一些东西，并抛出一个数据库锁定错误，但WAL和池应该防止这些。

有谁知道我该怎么解决我的问题吗?

Answer 1

显然，这个问题的解决方案是将sqlite数据库的同步pragma更改为NORMAL!当使用WAL模式时，同步pragma的最佳设置是NORMAL，但db的默认设置是FULL。但是FULL不适合WAL。当我修改它时，不再有数据库锁定错误。