How to make MySQL 5.1 connection ignore kills?

Question

According to MySQL 5.1's manual, section 13.7.6.4 KILL Syntax, when you kill a connection, a thread-specific kill flag is set for the thread. The flag will then be checked from time to time, even during some long-running queries, so that it can interrupt the query.

Is there a way to instruct a thread not to check the kill flag while inside some kind of delimited critical section?

What I'm looking for is something like:

BEGIN IGNORE_KILLS;
-- if this thread is killed here, it will simply ignore it
END IGNORE_KILLS;
-- here, the thread would be able to tell if someone tried to kill it previously

Assuming that it does not exist, or that someone could offer me a better solution to my problem, let me explain why would I want it.

I have an application that must react to lines that are inserted in a table, and this must be scalable. According to MySQL expert Baron Schwartz, one should not keep polling the database, and he proposes, as an alternative, to use SELECT SLEEP(...)/KILL.

The problem is that after the connection waiting on select sleep(...) is notified (by being killed) that it has work to do, I'd like to guarantee that, during its work (what I called critical section above), it won't stop doing what it should do even in the case that the killer thread goes crazy and kills it again.

Answer 1

I might have solved my problem...

I was afraid that 2 different killer threads could: (1) show processlist, (2) choose the same connection to kill, (3) then both would kill it.

The first kill would trigger the execution of whatever I wanted, while the second kill could potentially blow things up.

The easiest, and nearly obvious, solution that I missed is to look at the other side of the problem: instead of making the worker thread unkillable, the killer threads must cooperate: the killer threads would (1) select get_lock('killer_thread'), then only after acquiring the lock they would (2) show processlist, (3) kill the chosen threads and finally (4) select release_lock('killer_thread').

Answer 2

I assume that you are referring to this article which Baron wrote for the Engine Yard blog...

While he did provide some ways to reduce the pain of implementing a queue in an RDBMS, the real takeaway from this article is to not do it at all. There are dozens of products available that are designed to be queues, and databases are not one of them. This will bite you in the future.