Setting query timeout for MySQL++ operations in C++

Other Questions MySQL
1

I’m working with MySQL++ library to connect to a MySQL server and run various database operations. My issue is that I need to read from tables that are frequently updated by other processes. To get consistent data, I have to lock these tables first.

The problem is MySQL doesn’t support NOWAIT functionality for table locking. When another process already has a lock that takes forever to release, my program just hangs waiting. Instead of waiting indefinitely, I want my code to give up after a few seconds, show an error like ‘Unable to acquire lock’, and retry later.

I tried implementing a timeout mechanism using signals but I’m stuck. When I test this with a pre-locked table, the timeout message appears correctly, but the database operation doesn’t actually stop. How can I properly cancel the ongoing query?

volatile sig_atomic_t completed = 1;

void timeout_handler(int signal_num) {
    cout << "Operation timed out" << endl;
    completed = 0;
    signal(signal_num, timeout_handler);
}

// database connection setup
// *CODE OMITTED*

signal(SIGALRM, timeout_handler);
alarm(3);
mysql_query(db_connection, "LOCK TABLES ABC WRITE");

Any suggestions on how to properly terminate the mysql query when timeout occurs?

2

honestly i’d go with threading approach instead. spawn your query in a separate thread and use std::future with timeout. if timeout hits just detach the thread and create a new connection. not elegant but works better than signals which are mess with mysql api.

3

The signal approach won’t work reliably because MySQL++ operations are blocking at the C API level and signals don’t interrupt them cleanly. What you need is to set a connection timeout before executing the query. Use mysql_options() with MYSQL_OPT_READ_TIMEOUT and MYSQL_OPT_WRITE_TIMEOUT on your connection handle before connecting. Set both to your desired timeout value in seconds. This will cause the underlying MySQL connection to timeout at the network level rather than trying to interrupt an ongoing operation. Alternatively, consider using SELECT GET_LOCK() with a timeout parameter instead of table locking. It’s more flexible and supports explicit timeouts. You can then use SELECT RELEASE_LOCK() when done. This approach gives you better control over lock acquisition timeouts and is generally more robust than trying to interrupt table lock operations.