0

Is it good/safe/reliable to do so, and why?

int main()
{
    MYSQL *mysqldbconn = mysql_init(NULL);

    if(mysqldbconn != NULL)
    {
        if(mysql_real_connect(mysqldbconn, host,
                              user, password,
                              NULL, port, NULL, 0) != NULL)
        {
            pid_t pid = fork();
            if(pid >= 0)
            {
                if(pid == 0)
                {
                    mysql_query(mysqldbconn, "insert into `test`.`abc`(`x`, `y`) values (1, 2)");
                    exit(0);
                }
                else
                {
                    mysql_query(mysqldbconn, "insert into `test`.`abc`(`x`, `y`) values (3, 4)");
                    usleep(1000000);
                }
            }
        }
    }

    return 0;
}

I am wondering if I should open a new connection for every child process, or I can simply connect once at the beginning and then use it in every forked child. Is it possible that the usage example above leads to dangerous data races? Is mysql_query atomic?

Thank you for your advices.

πάντα ῥεῖ
  • 1
  • 13
  • 116
  • 190
beddu
  • 71
  • 9
  • Is there a reason why you aren't using a delayed insert instead? – Ignacio Vazquez-Abrams Jul 11 '18 at 17:37
  • 4
    MySQL does not allow issuing new query before you fetch ALL the results from the previous query - otherwise it gives a `Commands out of sync` error. When you have multiple processes you have to somehow synchronize the send/receive - otherwise it is easier to use a separate connection for each process. In your example you will issue 2 overlapping queries which is not good. You are also not fetching results back - not a good practice. – IVO GELOV Jul 11 '18 at 17:42
  • 3
    Generally, no; even if you put some some locking in place to prevent overlapping use of the connection, depending on how you lock, you can risk the scenario where something like "SELECT last_insert_id()" ends up getting the value for an insert made from a different thread in the time between it's insert and select. Of course, you can hold the lock until such a task is completely done with the connection. But I said "generally"; if db access is short and intermittent, or you only have a limited number of connections to work with in the first place, the sync overhead could be worth it. – Uueerdo Jul 11 '18 at 17:59
  • 1
    tldr version of my comment above.... I wouldn't have them share the connection unless circumstances require you to do so. – Uueerdo Jul 11 '18 at 18:00
  • @IgnacioVazquez-Abrams delayed inserts are deprecated since version 5.6 - https://dev.mysql.com/doc/refman/8.0/en/insert-delayed.html – beddu Jul 11 '18 at 18:05
  • @IVOGELOV I am quite new to mysql c api and I did not know that, thank you for the precious hint. – beddu Jul 11 '18 at 18:07
  • @Uueerdo Altough I am coding for a system with limited resources, I guess that circumstances are not yet here... thanks for the advice. – beddu Jul 11 '18 at 18:13
  • @beddu I am not very familiar with the C api, but you might want to see if it has support for connection pooling; or if there are any semi-standardized wrappers that implement pooling. With connection pooling, basically, the limited connections are managed in a "pool" (hence the name), and handed out and taken back without needing to completely reopen a connection at every use. – Uueerdo Jul 11 '18 at 18:18
  • It's not a direct answer to your question, but this q&a looks like it would be good to consider. https://stackoverflow.com/questions/1455190/how-to-access-mysql-from-multiple-threads-concurrently – Uueerdo Jul 11 '18 at 18:24
  • The forked children will inherit copies of the open file descriptors from their parent, including any associated with the database connection, which is probably ok, but they will also get *copies* of the C-side data structure. That is, they will not share the C-side data. I am not aware of the consequences being explicitly documented, but this seems a recipe for disaster to me. – John Bollinger Jul 11 '18 at 18:30

0 Answers0