2013/8/17 Federico Razzoli <federico_raz@yahoo.it>

I'm not sure that would be useful (particularly if your transactions always leave the database in a consinstent state... which should always be the case, unless you use non-transactional engines, but then data integrity is not supposed to be vital).

hi, the problem is not transactional or not transactional...

some time you are running a SELECT without transaction involved, and next query you start a transaction (BEGIN TRANSACTION for example)

the problem happen when you try to KILL the SELECT, and instead of killing it, you kill the BEGIN TRANSACTION

it's not a engine problem, i don't know how to call, but it's something like "a problem of sync between what you want to do, and what really happen", because when you get the thread id of a connection, you don't get the "query id" from that connection, you kill the thread, not the query... the point here is not kill the thread, is kill the thread only if it is running that query id

However, there is a little trick I use when debugging stored programs. It is not intended to solve the "problem" you are reporting, but probably it does... see below.

If a stored programs has a loop, it could be an infinite loop because of a bug. To quicly kill them, I have a simple procedure called kill_like(). I think that an example is the better way to explain it:

CALL my_proc();
CALL _.kill_like('%my_proc%');

kill_like() returns an error if 2 or more queries were found, because in that case the pattern wasn't restrictive enough. Only the query is killed, not the connection.

I think this solves your problem because if the query finishes while you type the command, a NOT FOUND condition is reported and nothing happens.

Here's the (trivial!) code

DELIMITER ||

CREATE DATABASE IF NOT EXISTS `_`;

DROP PROCEDURE IF EXISTS `_`.`kill_like`;
CREATE PROCEDURE `_`.`kill_like`(IN `sql_pattern` TEXT)
`whole_proc`:
BEGIN
SELECT
`ID`
FROM `information_schema`.`PROCESSLIST`
WHERE `INFO` LIKE `sql_pattern`
INTO @query_id;

IF FOUND_ROWS() = 0 THEN
SET @message_text = 'Query not found';
/*!50500
SIGNAL SQLSTATE '02000'
SET MESSAGE_TEXT = @message_text;
*/
SELECT @message_text AS `error`;
LEAVE `whole_proc`;
ELSEIF FOUND_ROWS() > 1 THEN
SET @message_text = 'More than 1 query match the pattern';
/*!50500
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = @message_text;
*/
SELECT @message_text AS `error`;
LEAVE `whole_proc`;
END IF;

KILL QUERY @query_id;
END;

||
DELIMITER ;

I hope this helps.

well this may work, and may not work, you don't set a lock between information_schema.PROCESS_LIST and KILL command, the query can change between the time of process_list return and the kill command, and yes, i have a very high lucky and i killed a query that was not the right query because i used the thread id, instead of the query id

i think the solution is a new parameter to KILL command, the query_id information...

Federico

Thanks federico! :)

i think i will do a patch, just talking with sergei at developers to know what more i'm missing