MySQL transaction and buffered list of requests

Question

I have a C++ code that parses files and updates the MySQL Database according to the content of these files. I run my code in Windows 10, with MySQL 5.7, and my base uses the InnoDB engine. The MySQL calls are performed through a wrapper of my own around libmysql.

In an attempt to optimize this code, I append the update requests in a buffer until reaching the maximum size of the buffer, then send the whole buffer (containing N updates) at the same time. This whole process is done inside a single transaction.

Here is how my code looks:

MySQLWrapper.StartTransaction();

string QueryBuffer = "";

// Element count is the number of elements parsed from the files
for( int i = 0 ; i < ElementCount ; ++i )
{
bool FlushBuffer = 
( i> 0 && ! (( i + 1 ) % N) )    ||
( i == ElementCount - 1 ); // determines if we have reached the buffer max number of requests

QueryBuffer += "INSERT INTO mytable (myfield) VALUES (" Element[ i ] ");";

if( FlushBuffer )
{
MySQLWrapper.SendRequest( QueryBuffer );
QueryBuffer.assign("");
}
}
MySQLWrapper.Commit();

The implementation of SendRequest(string Request ) would basically be:

void SendRequest(string Request)
{
mysql_query( SQLSocket, Request.c_str())
}

However, when committing the transaction, the transaction happens to have been broken: MySQL indicates that that the state is incorrect for comitting. I have tried to do the same thing but sending requests ony by one, and this error does not happen at the moment of the commit.

So, my 2 questions are:

1. Do you know why the fact to send multiple requests at a time breaks my transaction?
2. Do you think that the use of a buffered list of requests can really optimize my code?

Answer 1

Instead of multiple INSERTs, create one INSERT with multiple values. IOW, before the loop, have the INSERT INTO TABLE (columns), then inside the loop, append (values), for each value set.

MySQLWrapper.StartTransaction();

string QueryBuffer = "INSERT INTO mytable (myfield) VALUES ";

// Element count is the number of elements parsed from the files
for( int i = 0 ; i < ElementCount ; ++i )
{
    bool FlushBuffer = 
    ( i> 0 && ! (( i + 1 ) % N) )    ||
    ( i == ElementCount - 1 ); // determines if we have reached the buffer max number of requests

    QueryBuffer += "(" Element[ i ] ")";
    if( flushbuffer ) {
        QueryBuffer += ";";
    } else {
        QueryBuffer += ",";
    }

    if( FlushBuffer )
    {
        MySQLWrapper.SendRequest( QueryBuffer );
        QueryBuffer.assign("");
    }
}
MySQLWrapper.Commit();

The resulting SQL statement will be something like:

INSERT INTO mytable
(myfield)
VALUES
(1),
(2),
(3),
(3);