c# - 多线程是否会导致从 SQL Lite 读取数据不准确？
问题描述
我不知道如何准确解释我的问题，但问题是我想从 SQL Lite 数据库中读取数据值，如果我只运行一个线程，从 sqllite 数据库中读取的数据准确率将在 99% 以上。
Thread thr27 = new Thread(ListAFromTester11);
thr27.Start();
但是如果我运行多个线程，从 sqllite 数据库读取数据的准确率会下降到 95%。
Thread thr27 = new Thread(ListAFromTester11);
thr27.Start();
Thread thr28 = new Thread(ListAFromTester27);
thr28.Start();
Thread thr29 = new Thread(ListAFromTester8);
thr29.Start();
Thread thr30 = new Thread(ListAFromTester25);
thr30.Start();
下面是 ListAFromTester25 的示例
public void ListAFromTester25()
{
bool m_bSuccT = false;
while (!m_bSuccT)
{
Thread.Sleep(42000);
m_listA_25.Clear();
m_bSuccT = m_dbClient.DownloadSummaryFromDB_25(m_listA_25);
if (m_bSuccT)
{
m_listB_25.Clear();
m_bSuccT = m_dbClient.DownloadListSiteFromDB(m_listB_25, 25);
if (m_bSuccT)
{
m_bD10_25 = true;
}
}
}
}
下面是 DownloadListSiteFromDB(m_listB_25, 25) 的示例
public bool DownloadListSiteFromDB(List<StructB> list, Int32 D10ID)
{
if (D10ID == 1)
m_dbConnLocal = m_dbConnLocal_1;
else if (D10ID == 3)
m_dbConnLocal = m_dbConnLocal_3;
else if (D10ID == 4)
m_dbConnLocal = m_dbConnLocal_4;
else if (D10ID == 5)
m_dbConnLocal = m_dbConnLocal_5;
else if (D10ID == 6)
m_dbConnLocal = m_dbConnLocal_6;
else if (D10ID == 7)
m_dbConnLocal = m_dbConnLocal_7;
else if (D10ID == 8)
m_dbConnLocal = m_dbConnLocal_8;
else if (D10ID == 9)
m_dbConnLocal = m_dbConnLocal_9;
else if (D10ID == 10)
m_dbConnLocal = m_dbConnLocal_10;
else if (D10ID == 11)
m_dbConnLocal = m_dbConnLocal_11;
else if (D10ID == 12)
m_dbConnLocal = m_dbConnLocal_12;
else if (D10ID == 13)
m_dbConnLocal = m_dbConnLocal_13;
else if (D10ID == 14)
m_dbConnLocal = m_dbConnLocal_14;
else if (D10ID == 15)
m_dbConnLocal = m_dbConnLocal_15;
else if (D10ID == 16)
m_dbConnLocal = m_dbConnLocal_16;
else if (D10ID == 18)
m_dbConnLocal = m_dbConnLocal_18;
else if (D10ID == 19)
m_dbConnLocal = m_dbConnLocal_19;
else if (D10ID == 21)
m_dbConnLocal = m_dbConnLocal_21;
else if (D10ID == 22)
m_dbConnLocal = m_dbConnLocal_22;
else if (D10ID == 25)
m_dbConnLocal = m_dbConnLocal_25;
else if (D10ID == 26)
m_dbConnLocal = m_dbConnLocal_26;
else if (D10ID == 27)
m_dbConnLocal = m_dbConnLocal_27;
else if (D10ID == 31)
m_dbConnLocal = m_dbConnLocal_31;
else if (D10ID == 32)
m_dbConnLocal = m_dbConnLocal_32;
else if (D10ID == 33)
m_dbConnLocal = m_dbConnLocal_33;
else if (D10ID == 34)
m_dbConnLocal = m_dbConnLocal_34;
else if (D10ID == 35)
m_dbConnLocal = m_dbConnLocal_35;
else if (D10ID == 36)
m_dbConnLocal = m_dbConnLocal_36;
else if (D10ID == 37)
m_dbConnLocal = m_dbConnLocal_37;
else if (D10ID == 38)
m_dbConnLocal = m_dbConnLocal_38;
else if (D10ID == 39)
m_dbConnLocal = m_dbConnLocal_39;
//
try
{ //change query value for pressure_bin from 2.31 to 1.00
//<niq> 15/09/2021 change query value for temperature_bin from 2.32 to 3.50
string sql = @" SELECT Slot,
SUM(CASE WHEN BinName = 'continuity_bin' THEN Qty ELSE 0 END ) AS 'Continuity',
SUM(CASE WHEN BinName = 'temperature_bin' THEN Qty ELSE 0 END ) AS 'Temperature',
SUM(CASE WHEN BinName = 'pressure_bin' THEN Qty ELSE 0 END ) AS 'Pressure',
SUM(CASE WHEN BinName = 'pass' THEN Qty ELSE 0 END ) AS 'Pass',
SUM(CASE WHEN BinName = 'total' THEN Qty ELSE 0 END ) AS 'Total',
CASE WHEN(
CAST (SUM(CASE WHEN BinName = 'continuity_bin' THEN Qty ELSE 0 END ) AS FLOAT) /CAST (SUM(CASE WHEN BinName = 'total' THEN Qty ELSE 0 END )
AS FLOAT) *100) > 1.92 Then 1 ELSE 0 END AS ContinuityTrigger,
CASE WHEN(
CAST (SUM(CASE WHEN BinName = 'pressure_bin' THEN Qty ELSE 0 END ) AS FLOAT) /CAST (SUM(CASE WHEN BinName = 'total' THEN Qty ELSE 0 END )
AS FLOAT) *100) > 1.00 Then 1 ELSE 0 END AS PressureTrigger,
CASE WHEN(
CAST (SUM(CASE WHEN BinName = 'temperature_bin' THEN Qty ELSE 0 END ) AS FLOAT) /CAST (SUM(CASE WHEN BinName = 'total' THEN Qty ELSE 0 END )
AS FLOAT) *100) > 3.50 Then 1 ELSE 0 END AS TemperatureTrigger,
(Select Lot||SubLot FROM Data )as LotID, (Select Device FROM Data) as DeviceID, (Select State FROM Data )as D10Status
FROM (SELECT CAST (0 as int) as Slot, BinName, Qty FROM BINNINGSITE0 where BinName in ('continuity_bin', 'temperature_bin','pressure_bin')
UNION ALL
SELECT CAST (0 as int), 'pass', IFNULL(Sum(Qty),0) FROM BinningSite0 Where BinName like '%pass%'
UNION ALL
SELECT CAST (0 as int), 'total', IFNULL(Sum(Qty),0) FROM BinningSite0 Where Qty > 200
union ALL
SELECT CAST (1 as int), BinName, Qty FROM BINNINGSITE1 where BinName in ('continuity_bin', 'temperature_bin','pressure_bin')
UNION ALL
SELECT CAST (1 as int), 'pass', IFNULL(Sum(Qty),0) FROM BinningSite1 Where BinName like '%pass%'
UNION ALL
SELECT CAST (1 as int), 'total', IFNULL(Sum(Qty),0) FROM BinningSite1 Where Qty > 200
union ALL
SELECT CAST (2 as int), BinName, Qty FROM BINNINGSITE2 where BinName in ('continuity_bin', 'temperature_bin','pressure_bin')
UNION ALL
SELECT CAST (2 as int), 'pass', IFNULL(Sum(Qty),0) FROM BinningSite2 Where BinName like '%pass%'
UNION ALL
SELECT CAST (2 as int), 'total', IFNULL(Sum(Qty),0) FROM BinningSite2 Where Qty > 200
union ALL
SELECT CAST (3 as int), BinName, Qty FROM BINNINGSITE3 where BinName in ('continuity_bin', 'temperature_bin','pressure_bin')
UNION ALL
SELECT CAST (3 as int), 'pass', IFNULL(Sum(Qty),0) FROM BinningSite3 Where BinName like '%pass%'
UNION ALL
SELECT CAST (3 as int), 'total', IFNULL(Sum(Qty),0) FROM BinningSite3 Where Qty > 200) AS A
GROUP BY Slot ";
using (SQLiteCommand command = new SQLiteCommand(sql, m_dbConnLocal))
{
m_dbConnLocal.Open();
using (SQLiteDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
if (reader.GetString(11)== "R")
{
list.Add(
new StructB
{
EquipmentName = "D10_" + D10ID.ToString(),
SlotID = reader.GetInt32(0),
Continuity = reader.GetInt32(1),
Temperature = reader.GetInt32(2),
Pressure = reader.GetInt32(3),
Pass = reader.GetInt32(4),
Total = reader.GetInt32(5),
ContinuityTrigger = Convert.ToBoolean(reader.GetInt32(6)),
PressureTrigger = Convert.ToBoolean(reader.GetInt32(7)),
TemperatureTrigger = Convert.ToBoolean(reader.GetInt32(8)),
LotID = reader.GetString(9),
DeviceID = reader.GetString(10),
D10Status = reader.GetString(11)
}
);
}
else
{
list.Add(
new StructB
{
EquipmentName = "D10_" + D10ID.ToString(),
SlotID = reader.GetInt32(0),
Continuity = 0,
Temperature = 0,
Pressure = 0,
Pass = 0,
Total = 0,
ContinuityTrigger = Convert.ToBoolean(0),
PressureTrigger = Convert.ToBoolean(0),
TemperatureTrigger = Convert.ToBoolean(0),
LotID = "",
DeviceID = "",
D10Status = "E"
}
);
}
}
reader.Close();
}
}
}
catch (Exception ex)
{
DataProcessor.WriteLog("Error Catching", "DownloadListSiteFromDB: DB Error D1 - Exception: " + D10ID + " " + ex.Message);
//MessageBox.Show("DB Error D1 - Exception: " + D10ID +ex.Message);
return false;
}
finally
{
m_dbConnLocal.Close();
}
return true;
}
在多线程中，行m_dbConnLocal.Open(); 会有如下错误
InvalidOperationExecution Occured : Operation is not valid due to the current state of the object.
这是我的 dbconnection 的片段。
SQLiteConnection m_dbConnLocal_31 = new SQLiteConnection("Data Source=\\\\\\\\sklplfs03\\D10Devprogs\\D10_Monitor\\D10_31.db;;version=3;new=False;Timeout=30000;datetimeformat=CurrentCulture");
SQLiteConnection m_dbConnLocal_32 = new SQLiteConnection("Data Source=\\\\\\\\sklplfs03\\D10Devprogs\\D10_Monitor\\D10_32.db;version=3;new=False;Timeout=30000;datetimeformat=CurrentCulture");
SQLiteConnection m_dbConnLocal_33 = new SQLiteConnection("Data Source=\\\\\\\\sklplfs03\\D10Devprogs\\D10_Monitor\\D10_33.db;version=3;new=False;Timeout=30000;datetimeformat=CurrentCulture");
那么我应该改变在单独变量中编写这些连接字符串的方式并改用 Dictionary<int, DbConnection> 吗？
我不知道如何解决这个问题，我被卡住了。谁能帮帮我吗？
解决方案
推荐阅读
- selenium - Instagram 故事圈上的 Xpath
- google-apps-script - 在 Google 表格中垂直显示数组数据
- android - Android 上的“未找到证书”PWA 应用程序，端口 433
- python - 将凭据从 react-native 客户端传递到 server-python
- javascript - API请求仍然在设定的时间间隔内多次
- python - Pandas Dataframe 与多个键合并（“AND”或“OR”？）
- c# - Office VSTO插件，后台开始选项卡添加自定义按钮
- sql-server - 如何改进 SQL 中不同时间范围的聚合查询
- java - 如何独立编译java类
- json - 对于长时间不可更新的内容、json 或 db 表，什么更好？