How to timeout a mysql++ query in c++

Question

I am using mysql++ in order to connect to a MySQL database to perform a bunch of data queries. Due to the fact that the tables I am reading from are constantly being written to, and that I need a consistent view of the data, I lock the tables first. However, MySQL has no concept of 'NOWAIT' in its lock query, thus if the tables are locked by something else that keeps them locked for a long time, my application sits there waiting. What I want it to do is to be able to return and say something like 'Lock could no be obtained' and try again in a few seconds. My general attempt at this timeout is below.

If I run this after locking the table on the database, I get the message that the timeout is hit, but I don't know how to then get the mysql_query line to terminate. I'd appreciate any help/ideas!

volatile sig_atomic_t success = 1;

void catch_alarm(int sig) {
        cout << "Timeout reached" << endl;
        success = 0;
        signal(sig,catch_alarm);
}

// connect to db etc.
// *SNIP

signal (SIGALRM, catch_alarm);
alarm(2);
mysql_query(p_connection,"LOCK TABLES XYZ as write");

Answer 1

You could execute the blocking query in a different thread and never being bothered with the timeout. When some data arrives you notify the thread that needs to know about the status of the transaction.

Answer 2

If I was writing from scratch I would do that, but this is a server application that we are just doing an upgrade to rather than a large rework.

Answer 3

instead of trying to fake transactions with table locks, why not switch to innodb tables where you get actual transactions? just make sure to set the default transaction isolation level to REPEATABLE READ.

Answer 4

As I said, it is not so easy to 'switch' or re-architect when this is a live, in production system. I'm slightly frustrated that MySQL provides no methods to check for locks or choose not to hang waiting on a lock.

Answer 5

I don't know if this is a good idea in terms of resource usage and "best practices" and "cleanliness" and all the rest... but you have now repeatedly described the handcuffs that bind you in terms of re-architecting a "clean" system... so here goes.....

Could you open a new, separate connection just for sending the LOCK statement? Then close that connection when you catch the timeout alarm? By closing/destroying the connection that was dedicated to the LOCK statement, would not that essentially "cancel" the LOCK statment? I am not certain if such events would occur as I have described/guessed, but maybe it is something to test out.

Answer 6

My experience described so far indicates to me that closing a connection in which a query is running causes a seg fault. Therefore dispatching that query into a different connection wouldn't really help, as that would also seg fault.

Answer 7

You can implement a "cancel-like" behavior this way:

You execute the query on a separate thread, that keeps running whether or not the timeout occurs. The timeout occurs on the main thread, and sets a variable to "1" marking that it occurred. Then you do whatever you want to do on your main thread.

The query thread, once the query completes, checks if the timeout has occurred. If it hasn't, it does the rest of the work it needs to do. If it HAS, it just unlocks the tables it just locked.

I know it sounds a bit wasteful, but the lock-unlock period should be basically instantaneous, and you get as close to the result you want as possible.