PostgreSQL:命令已经在进行中 [英] PostgreSQL: command is already in progress
问题描述
我的异步函数尝试从表中选择一条记录.此函数接受从另一个函数传递的一些参数.
My asynchronous function tries to select a single record from a table. This function accepts a few arguments passed from another function.
因此，某些进程(至少6个)可以同时使用它.我经常收到错误消息命令已在执行中" .
So, some processes (6 at least) can use it simultaneously. Often I get an error with the message "command is already in progress".
我知道问题隐藏在阅读器中，因为当另一个进程尝试访问它时，阅读器很忙.
I know that the problem hides in the reader, because the reader is busy when another process tries to access it.
让我在下面发布完整的代码:
Let me publish the full code below:
async private void InsertToLog(List<Printer> APrinter, List<PrinterToGridBinds> AGridBind, int index)
{
if (!String.IsNullOrEmpty(APrinter[index].Type.Trim()) && !String.IsNullOrEmpty(AGridBind[index].extBatchNumber.Trim()) && !String.IsNullOrEmpty(AGridBind[index].extBatchCounter.Trim()) && !String.IsNullOrEmpty(AGridBind[index].extDIOCounter.Trim()))
{
string dio_count = "0";
string GetDIOCounter = string.Format(@"SELECT dio_counter FROM {0} WHERE device_type = '{1}' AND batch_number = '{2}' ORDER BY id DESC
LIMIT 1;", log_table_name, lst_PrinterStruct[index].Type, AGridBind[index].extBatchNumber);
try
{
NpgsqlCommand db_getCounter = new NpgsqlCommand(GetDIOCounter, conn);
if (conn.State != ConnectionState.Open)
conn.Open();
using (DbDataReader reader = await db_getCounter.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
dio_count = reader[0].ToString().Trim();
}
AGridBind[index].extDIOCounter = (Int32.Parse(dio_count) + Int32.Parse(AGridBind[index].extDIOCounter.Trim())).ToString();
string Insert_SQL = String.Format(@"INSERT INTO {0} (device_type, batch_number, start_date, batch_counter, dio_counter) VALUES ('{1}', '{2}', '{3}', '{4}', '{5}') ON CONFLICT ON CONSTRAINT unique_log_key DO UPDATE SET batch_counter='{4}', dio_counter='{5}';", log_table_name, APrinter[index].Type.Trim(),
AGridBind[index].extBatchNumber.Trim(), DateTime.Now.ToString(), AGridBind[index].extBatchCounter.Trim(), AGridBind[index].extDIOCounter.Trim());
var db_cmd = new NpgsqlCommand(Insert_SQL, conn);
int res = await db_cmd.ExecuteNonQueryAsync();
}
catch (Exception e)
{
string FMessage = String.Format("Printer {0} \r\n Can not write to table\r\n Error: {1}",
lst_PrinterStruct[index].Name, e.Message);
MessageBox.Show(FMessage);
}
finally
{
conn.Close();
}
}
}
如您所见，读者在这里被使用包裹.
As you can see, the reader is wrapped by using here.
无论如何，我有我所拥有的(错误).因此，我的问题是如何避免出现此错误消息(我的意思是命令已在执行中")?
Anyway, I have what I have (an error). So, my question is how to avoid this error message (I'm about "command is already in progress")?
我对可能的决定有一些想法.也许:
I have a few ideas about possible decisions. Maybe:
-
将
DbDataReader设置为参数.阅读器的名称可以通过 Random 函数生成.但是我不知道该怎么做.
Set the
DbDataReaderas parameter. And the name of the reader can be generated by the Random function. But I don't know how to do that.
等到当前进程完成并关闭阅读器，然后运行第二个进程，等等.但是我不知道如何告诉新进程等到前一个进程完成.
Wait until the current process has finished and closes the reader, then run the second etc. But I don't know how to tell the new process to wait until the previous has finished.
所以，伙计，我需要你的帮助.
So, I need your help, guys.
推荐答案
如果多个线程共享一个数据库连接，则必须同步它们，以便只有其中一个同时使用该连接.您只能在PostgreSQL数据库会话中一次运行一条语句.
If several threads share one database connection, you have to synchronize them so that only one of them uses the connection at the same time. You can only run one statement at a time in a PostgreSQL database session.
由于同步很麻烦，并且可能会损害您的并发性，因此最好使用连接池，并且每个线程都需要从该池请求连接.
Since synchronization is cumbersome and may hurt your concurrency, you'd be better off using a connection pool, and each thread requests a connection from the pool if it needs one.
这篇关于PostgreSQL:命令已经在进行中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
