Recently, I had a problem with a client connection (with iis webservices) which blocked other transactions.
The client did nothing but sleep and hold a transaction (a problem probably connected with Gap and Next-Key Locks).
Eventually I made a fresh install of MySQL 5.5 (which has a very useful table in ‘information_schema’) and created a new schema as follows:

create database administrator;
use administrator;
create table INNODB_LOCKS SELECT * FROM information_schema.INNODB_LOCKS LIMIT 0;
create table INNODB_TRX SELECT * FROM information_schema.INNODB_TRX LIMIT 0;
create table INNODB_LOCK_WAITS SELECT * FROM information_schema.INNODB_LOCK_WAITS LIMIT 0;
create table PROCESSLIST SELECT *, 0 `trx_id` FROM information_schema.PROCESSLIST LIMIT 0;

CREATE TABLE IF NOT EXISTS `GHOST_TRX_LOG` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `insert_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `thread_id` bigint(21) unsigned DEFAULT NULL,
  `thread_user` varchar(16) DEFAULT NULL,
  `thread_host` varchar(64) DEFAULT NULL,
  `thread_info` text,
  `thread_status` varchar(64) DEFAULT NULL,
  `status` tinyint(3) unsigned NOT NULL COMMENT '1 to_validate 2 validated 3 no validation',
  PRIMARY KEY (`id`),
  KEY `ts` (`insert_timestamp`,`thread_user`),
  KEY `user` (`thread_user`),
  KEY `host` (`thread_host`),
  KEY `status` (`status`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

Next, I have written an event and added it to the Event Scheduler:

How does it work?

It simply kills all transactions which take more time than set by TRANSACTION_TIME_LIMIT_V (my limit is set to 2 minutes). After that it makes a snapshot of the activities and transactions and puts it into the administrator database. Thanks to this, I can observe and log what is really going on.

I hope this helps anyone!

DROP EVENT IF EXISTS KILL_GHOST_TRX;
 
CREATE definer=root@localhost EVENT KILL_GHOST_TRX
ON SCHEDULE EVERY 1 MINUTE
DO

MAIN_BLOCK: BEGIN

	-- private variables
	DECLARE __done INT DEFAULT 0;
	
	-- public variables
	DECLARE TRANSACTION_TIME_LIMIT_v TIMESTAMP DEFAULT DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 2 MINUTE); 
	DECLARE THREAD_ID_v BIGINT(21) UNSIGNED DEFAULT 0;
	DECLARE THREAD_STATE_v, THREAD_HOST_v VARCHAR(64);
	DECLARE THREAD_INFO_v LONGTEXT;
	DECLARE THREAD_COMMAND_v, THREAD_USER_v, TRX_STATE_v VARCHAR(16);
	DECLARE CURSOR_QTY INT(11) DEFAULT 0;
	DECLARE BLOCKING_TRX_ID_v VARCHAR(18);
	DECLARE p_Kill TINYINT DEFAULT 0;
	
	-- cursors
	DECLARE GHOST_TRX_CURSOR CURSOR FOR SELECT SQL_CALC_FOUND_ROWS blocking_trx_id FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS GROUP BY 1; 
	
	-- handlers
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET __done = 1;
	DECLARE CONTINUE HANDLER FOR NOT FOUND 		SET __done = 1;
 
	-- [START]
	-- Is there any blocking? 
	OPEN GHOST_TRX_CURSOR;
	
	SET CURSOR_QTY = (Select FOUND_ROWS());
	
	IF CURSOR_QTY > 0 THEN
		
		BLOCKING_LOOP: LOOP FETCH 
			GHOST_TRX_CURSOR INTO BLOCKING_TRX_ID_v;		
			IF __done = 1 THEN
			  -- nothing to do
			  LEAVE BLOCKING_LOOP;
			END IF;
				
			IF p_Kill = 0 THEN
				PREPARE p_Kill FROM "KILL /* KILL_GHOST_TRX */ ? ;";
			END IF;
	
			SELECT trx.trx_mysql_thread_id	, IFNULL(trx_state, 'none'), 		trx_query, 		`USER`,			`HOST`, 		IFNULL(`STATE`, 'none')
				INTO THREAD_ID_v			, TRX_STATE_v,	THREAD_INFO_v,	`THREAD_USER_v`,	`THREAD_HOST_v`, `THREAD_STATE_v` 
				FROM INFORMATION_SCHEMA.INNODB_TRX trx JOIN INFORMATION_SCHEMA.PROCESSLIST ps ON trx.trx_mysql_thread_id = ps.ID 
					WHERE trx_started < TRANSACTION_TIME_LIMIT_v AND `USER` NOT IN ('system_user', 'root','event_scheduler') LIMIT 1;
			 
			if THREAD_ID_v IS NOT NULL AND THREAD_ID_v > 0 THEN	    
			
				-- Save logs...
				INSERT IGNORE INTO INNODB_TRX			SELECT * FROM information_schema.INNODB_TRX;
				INSERT IGNORE INTO INNODB_LOCKS			SELECT * FROM information_schema.INNODB_LOCKS;
				INSERT IGNORE INTO INNODB_LOCK_WAITS	SELECT * FROM information_schema.INNODB_LOCK_WAITS;
				INSERT IGNORE INTO PROCESSLIST			SELECT *, THREAD_ID_v FROM information_schema.PROCESSLIST;
			
				SET @THREAD_ID = THREAD_ID_v;
			    EXECUTE p_Kill USING @THREAD_ID;
			
			    INSERT INTO GHOST_TRX_LOG	(`thread_id`,	`thread_user`,		`thread_host`, 		`thread_info`,		`thread_status`, `status`) 
			    	VALUES 					(`THREAD_ID_v`,	`THREAD_USER_v`,	`THREAD_HOST_v`,	`THREAD_INFO_v`,	`THREAD_STATE_v`, 1);
			ELSE
			    INSERT INTO GHOST_TRX_LOG	(`thread_id`,	`thread_user`,	`thread_host`, 		`thread_info`,		`thread_status`, `status`) 
			    	VALUES 					(`THREAD_ID_v`,	`THREAD_USER_v`,	`THREAD_HOST_v`,	`THREAD_INFO_v`,	`THREAD_STATE_v`, 3);
			END IF;
			SET __done = 0;
			
		END LOOP;
		CLOSE GHOST_TRX_CURSOR;	
		
		IF p_Kill  THEN
			DEALLOCATE PREPARE p_Kill;
		END IF;
	END IF;	
	-- DELETE FROM GHOST_TRX_LOG WHERE insert_timestamp < NOW() - INTERVAL 31 DAY;
	END MAIN_BLOCK;