Exception "Too many concurrent executions of the same request"

[Kyle Green]

Hello,

using asp.net, C#, .net provider, Firebird 5.0

I have some older code that reads a csv file into a DataTable. The
datatable is read in a loop that writes to a table. There are over
80,000 records in the DataTable, and there are 87 fields in the table
being written to.

Using Firebird 2.5 (I believe, could have been slightly newer), this
same code, using the same csv file, successfully wrote all records to
the table.

Now, with Firebird 5.0, 1001 records are written, and while trying to
write record 1002, I get this exception:

"Too many concurrent executions of the same request"

This is a simple insert, with a generator providing the id for the
primary key. There are about 5 indexes.

Any idea what might have changed in the newer Firebird to cause this failure?

Thanks,

Kyle

[Mark Rotteveel]

Can you show a reproducible example (i.e. the code you use, and the DDL
of the table(s) involved, including triggers)?


Mark
--
Mark Rotteveel

[Kyle Green]

Mark,

Thank you for your response. With 87 fields and 80,000 rows it will be
hard to give fully reproducible code,
but I think this will show that it is pretty straightforward.
It works for 1001 rows, then produces the exception. It worked without
an exception in Firebird
version 2.5 or 3. It did not have a transaction previously. When I
encountered the error
when I was reviving this code, I added the transaction to see if it
would help, but the error was the same.

First I show the script, then I show the code:

Begin Script for table:
/////////////////////////////////////////////////////////////////////
create table ZipCodeDataBus
(ZipId integer not
null /* 0 */
,ZipCode char(5) not
null /* 1 */
,PrimaryRecord char(1) default '' not
null /* 2. */
,Population integer default 0 not
null /* 3 .*/
,HouseholdsPerZipCode integer default 0 not
null /* 4 */
,WhitePopulation integer default 0 not
null /* 5 */
,BlackPopulation integer default 0
not null /* 6 */
,HispanicPopulation integer default 0 not
null /* 7 */
,AsianPopulation integer default 0 not
null /* 8 */
,HawaiianPopulation integer default 0 not
null /* 9 */
,IndianPopulation integer default 0 not
null /* 10 */
,OtherPopulation integer default 0 not
null /* 11 */
,MalePopulation integer default 0 not
null /* 12 */
,FemalePopulation integer default 0 not
null /* 13 */
,PersonsPerHousehold decimal(4, 2) default 0000.00 not
null /* 14 */
,AverageHouseValue integer default 0 not
null /* 15 */
...... 87 columns
,FinanceNumber varchar(6) default '' not
null /* 86 .*/
,UniqueZIPName varchar(1) default '' not
null /* 87 */

,constraint pkZipCodeDataBus_ZipId primary key (ZipId)
);
commit;
create index idxZip_City on ZipCodeDataBus(City);
create index idxZip_State on ZipCodeDataBus(State);
create index idxZip_AverageHouseValue on ZipCodeDataBus(AverageHouseValue);
create index idxZip_IncomePerHousehold on ZipCodeDataBus(IncomePerHousehold);
create index idxZip_AreaCode on ZipCodeDataBus(AreaCode);
create index idxZip_County on ZipCodeDataBus(County);
create index idxZip_GrowthIncreaseNumber on
ZipCodeDataBus(GrowthIncreaseNumber);

create generator ZipCodeDataBus_Gen;
set generator ZipCodeDataBus_Gen to 100000000;
commit;

set term !! ;
create trigger ZipCodeDataBus_Trg FOR ZipCodeDataBus
before insert as
begin
new.ZipId = gen_id(ZipCodeDataBus_Gen, 1);
end !!
set term ; !!
commit;

End Script for Table
/////////////////////////////////////////////////////////////////////


///////////// This code is in WebForms Page
/////////////////////////////////////////

I load a DataTable from a csv file, with 80,000 rows


Instantiate myRecordWrapper
Open the myRecordWrapper.connection
myRecordWrapper.Connection.BeginTransaction

Begin loop for each DataRow (80,000)

Load 87 fields into myRecordWrapper
Load FbParameters from RecordWrapper
Compose Parameterized query
call this function, passing connection and transaction

CreateTheRecord(sQuery, passedConnection, passedTransaction, parameters)
{
bool bSuccess = true;

try
{
FbCommand mySqlCommand = new FbCommand(sQuery, passedConnection,
passedTransaction);

foreach(FbParameter parameter in parameters)
{
mySqlCommand.Parameters.Add(parameter);
}

mySqlCommand.ExecuteNonQuery();

bSuccess = true;
}

catch(FbException Ex)
{
sbErrMsg.Append(Ex.Message);
sbErrMsg.Append(": Query: ");
sbErrMsg.Append(sQuery);
WriteLogAndAlert(sbErrMsg);

bSuccess = false;
}

return bSuccess;
}

//end CreateTheRecord(...)


back in the calling function:

if fail {end loop}

End loop


if fail, ROllback, Close

else if success, Commit, Close

///////////// end code section
//////////////////////////////////////////////////

The query insert is a simple direct insert with no nested queries or
anything tricky. I log the error,
and the fields all match. And I reviewed the 1001 inserted records in
the database, and they are all correct.


Thank you for your help

Kyle

> --
> You received this message because you are subscribed to the Google Groups "firebird-net-provider" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to firebird-net-pro...@googlegroups.com.
> To view this discussion on the web visit https://groups.google.com/d/msgid/firebird-net-provider/e2f6017e-33ce-4b22-a399-a9414348b3e8%40lawinegevaar.nl.

[Mark Rotteveel]

On 13/08/2024 05:46, Kyle Green wrote:
> Thank you for your response. With 87 fields and 80,000 rows it will be
> hard to give fully reproducible code,
> but I think this will show that it is pretty straightforward.
> It works for 1001 rows, then produces the exception. It worked without
> an exception in Firebird
> version 2.5 or 3. It did not have a transaction previously. When I
> encountered the error
> when I was reviving this code, I added the transaction to see if it
> would help, but the error was the same.

[..]

> CreateTheRecord(sQuery, passedConnection, passedTransaction, parameters)
> {
> bool bSuccess = true;
>
> try
> {
> FbCommand mySqlCommand = new FbCommand(sQuery, passedConnection,
> passedTransaction);

[..]

>
> The query insert is a simple direct insert with no nested queries or
> anything tricky. I log the error,
> and the fields all match. And I reviewed the 1001 inserted records in
> the database, and they are all correct.

I don't have time to verify right now, but I think the problem is that
you never close/dispose the command you create. This results in a
statement leak, and I guess (and I have to stress it is a guess), this
somehow interferes with the statement cache introduced in Firebird 5.

I'd suggest creating the command in a `using`, or try to disable the
statement cache by setting MaxStatementCacheSize in firebird.conf to 0
(you need to restart Firebird for it to take effect).

Alternatively, rewrite your code so the command is created once and
reused for all rows, instead of creating the command anew for each row.

Mark
--
Mark Rotteveel

[Kyle Green]

Mark,

Thank you, I will try to implement your suggestions

Kyle

> --
> You received this message because you are subscribed to the Google Groups "firebird-net-provider" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to firebird-net-pro...@googlegroups.com.

> To view this discussion on the web visit https://groups.google.com/d/msgid/firebird-net-provider/d191eb77-1559-42e2-9d82-d61a5687405a%40lawinegevaar.nl.

[Kyle Green]

Mark,

As you suggested, I moved the command outside the loop, and cleared
its parameters inside the loop.

Everything works now, thanks very much for your help,

Kyle

[Mark Rotteveel]

On 20/08/2024 19:49, Kyle Green wrote:
> As you suggested, I moved the command outside the loop, and cleared
> its parameters inside the loop.
>
> Everything works now, thanks very much for your help,

Thanks for confirming. I'll see if I can reproduce this, maybe it's some
underlying bug in the statement cache implementation in Firebird.

Mark
--
Mark Rotteveel