MySQL Connector/C++ hangs on read()

Question

I am writing a multi-threaded application in C++ using Boost threads (pthread). The application spawns 100 threads and each thread does the following task (I am writing a code snippet that will be running in each thread):

try {

    driver = get_driver_instance();
    con = driver->connect(SettingsClass.HostName, \
                  SettingsClass.UserName,SettingsClass.Password);

    // SettingsClass is a global static class whose members 
    // (HostName, UserName, Password, etc) are initialized once
    // before *any* thread is created.

    con->setSchema("MyDatabase");

    driver->threadInit();

    string dbQuery = "select A, B, C from XYZTable where D=?";
    prepStmt = con->prepareStatement(dbQuery);
    prepStmt->setInt(1, 1);
    rSet = prepStmt->executeQuery();

    /* Do Something With rSet, the result set */

    delete rSet;
    delete prepStmt;
    if (con != NULL && !con->isClosed()) {
        con -> close();
    driver->threadEnd();
    delete con;
    } 
    catch (SQLException &e) 
    {
        /* Log Exception */                            
    }

On running the process (the app, as earlier mentioned, i.e. with 100 such threads), I attach gdb midway and observe that more than 40% of the threads have hanged in the read() call. All the backtraces have mysql library functions (vio_read(), etc) and none are from my code as my code does not perform any I/O.

Could anyone point out why is this issue arising. Should I check my code / network or MySQL server configuration? Have I used the C++ connector library properly?

Thinskinned

100 threads? That could mean more or less 100mb of memory!!! – Afrikaans

first: you are not spawning 100 threads because you think you get data out of the database faster, are you? second: what mysql engine are the tables set to, inno or MyISAM? – Conservationist

I am using InnoDB @Conservationist – Thinskinned

@Mark: Both my servers have 16 gigs of memory each! – Thinskinned

@Thinskinned Still, more threads doesn't necessarily mean higher performance. – Afrikaans

@Mark, That be true, but I am trying to figure out the bottleneck. Is it in the network, the Database Server or my code ? – Thinskinned

@Thinskinned you are most likely having issues with locks on that table/rows. Try #918140 (the answer with 33 upvotes) and see if the hangs vanish but nonethe less, its not good practice to spawn so many threads to blister the database. Rather fetch them in one thread to a temporary location and let your threads spawn after for computation work (if necc) – Conservationist

@Conservationist what about insert queries? – Thinskinned

innoDB should not lock the complete table during insert but only the rows. In the example above you do not insert but read from your database. If you follow the linked example, the last one given, you will have to start the no-lock option before selecting from your table and end it with COMMIT; . The INSERT commands should not bypass the locking mechanism. – Conservationist