inserts per transaction before a commit

[Norbert Saint Georges]

Hi,

what is the maximum number of inserts per transaction before a commit?

--
Norbert Saint Georges
http://tetrasys.fi

[Dimitry Sibiryakov]

Norbert Saint Georges wrote 25.02.2023 18:29:
> what is the maximum number of inserts per transaction before a commit?

Practically unlimited. Remember whole database restore from backup is made in
single transaction.

--
WBR, SD.

[Norbert Saint Georges]

'Dimitry Sibiryakov' via firebird-general a écrit :

Thank you for this quick response.

unfortunately, I have this error message if I exceed 1000 inserts or
updates
FirebirdSql.Data.FirebirdClient.FbException: 'Too many concurrent
executions of the same request'

[Dimitry Sibiryakov]

Norbert Saint Georges wrote 25.02.2023 18:37:
> unfortunately, I have this error message if I exceed 1000 inserts or updates
> FirebirdSql.Data.FirebirdClient.FbException: 'Too many concurrent executions of
> the same request'

It has nothing to do with transaction, it is limit to recursion depth.

--
WBR, SD.

[Norbert Saint Georges]

'Dimitry Sibiryakov' via firebird-general a écrit :

sorry, I don't understand, I only make simple requests like below

UPDATE or INSERT INTO relation_members ( RELATION_MEMBERS_RELATION_ID,
RELATION_MEMBERS_MEMBER_TYPE, RELATION_MEMBERS_MEMBER_ID,
RELATION_MEMBERS_MEMBER_ROLE, RELATION_MEMBERS_SEQUENCE_ID) VALUES (
@RELATION_MEMBERS_RELATION_ID, @RELATION_MEMBERS_MEMBER_TYPE,
@RELATION_MEMBERS_MEMBER_ID, @RELATION_MEMBERS_MEMBER_ROLE,
@RELATION_MEMBERS_SEQUENCE_ID) matching (RELATION_MEMBERS_RELATION_ID,
RELATION_MEMBERS_MEMBER_ID);

is this recursive?

[Dimitry Sibiryakov]

Norbert Saint Georges wrote 25.02.2023 18:45:
> UPDATE or INSERT INTO relation_members ( RELATION_MEMBERS_RELATION_ID,
> RELATION_MEMBERS_MEMBER_TYPE, RELATION_MEMBERS_MEMBER_ID,
> RELATION_MEMBERS_MEMBER_ROLE, RELATION_MEMBERS_SEQUENCE_ID)  VALUES (
> @RELATION_MEMBERS_RELATION_ID, @RELATION_MEMBERS_MEMBER_TYPE,
> @RELATION_MEMBERS_MEMBER_ID, @RELATION_MEMBERS_MEMBER_ROLE,
> @RELATION_MEMBERS_SEQUENCE_ID)  matching (RELATION_MEMBERS_RELATION_ID,
> RELATION_MEMBERS_MEMBER_ID);
>
> is this recursive?

Yes if table relation_members has triggers.

--
WBR, SD.

[Norbert Saint Georges]

'Dimitry Sibiryakov' via firebird-general a écrit :

ok, 1023 crashes but 1000 passes.
Thank you again for your response

[Dimitry Sibiryakov]

Norbert Saint Georges wrote 25.02.2023 18:45:
> UPDATE or INSERT INTO relation_members ( RELATION_MEMBERS_RELATION_ID,
> RELATION_MEMBERS_MEMBER_TYPE, RELATION_MEMBERS_MEMBER_ID,
> RELATION_MEMBERS_MEMBER_ROLE, RELATION_MEMBERS_SEQUENCE_ID)  VALUES (
> @RELATION_MEMBERS_RELATION_ID, @RELATION_MEMBERS_MEMBER_TYPE,
> @RELATION_MEMBERS_MEMBER_ID, @RELATION_MEMBERS_MEMBER_ROLE,
> @RELATION_MEMBERS_SEQUENCE_ID)  matching (RELATION_MEMBERS_RELATION_ID,
> RELATION_MEMBERS_MEMBER_ID);

This query syntax is not Firebird one so the problem may be in the database
driver you use.

--
WBR, SD.

[Norbert Saint Georges]

'Dimitry Sibiryakov' via firebird-general a écrit :

it's in net 6 with the net provider client 9.1.1

[Dimitry Sibiryakov]

Norbert Saint Georges wrote 26.02.2023 15:13:
>>    This query syntax is not Firebird one so the problem may be in the database
>> driver you use.
>>
> it's in net 6 with the net provider client 9.1.1

In this case you'd better ask in .NET provider lists including a little more
of your code because Firebird itself using native API is capable to handle much
more inserts without error.

--
WBR, SD.

[Norbert Saint Georges]

'Dimitry Sibiryakov' via firebird-general a écrit :

Hi there,

For your information, the problem was neither with the dotnet drivers
nor with the format of the SQL query, but with the values in
Firebird.conf, inappropriate for the work requested.

The modified lines in Firebird.conf:

TempDirectories = /ssd4/tmp
TempTableDirectory = /ssd4/tmp
DefaultDbCachePages = 50K
UseFileSystemCache = true
FileSystemCacheThreshold = 2M
TempBlockSize = 2M
TempCacheLimit = 1024M
InlineSortThreshold = 16384
LockMemSize = 30M
LockHashSlots = 40099

the environment variables (default space too small to create the
indexes):

export FIREBIRD_TMP=/ssd4/tmp
export FIREBIRD_LOCK=/ssd4/tmp

I can send blocks of 5 million records in the same transaction without
problems with these values.