Recently, I had a problem with a client connection (with iis webservices) which blocked other transactions.
\nThe client did nothing but sleep and hold a transaction (a problem probably connected with Gap and Next-Key Locks).
\nEventually 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:<\/p>\n
\r\ncreate database administrator;\r\nuse administrator;\r\ncreate table INNODB_LOCKS SELECT * FROM information_schema.INNODB_LOCKS LIMIT 0;\r\ncreate table INNODB_TRX SELECT * FROM information_schema.INNODB_TRX LIMIT 0;\r\ncreate table INNODB_LOCK_WAITS SELECT * FROM information_schema.INNODB_LOCK_WAITS LIMIT 0;\r\ncreate table PROCESSLIST SELECT *, 0 `trx_id` FROM information_schema.PROCESSLIST LIMIT 0;\r\n\r\nCREATE TABLE IF NOT EXISTS `GHOST_TRX_LOG` (\r\n `id` int(11) NOT NULL AUTO_INCREMENT,\r\n `insert_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,\r\n `thread_id` bigint(21) unsigned DEFAULT NULL,\r\n `thread_user` varchar(16) DEFAULT NULL,\r\n `thread_host` varchar(64) DEFAULT NULL,\r\n `thread_info` text,\r\n `thread_status` varchar(64) DEFAULT NULL,\r\n `status` tinyint(3) unsigned NOT NULL COMMENT '1 to_validate 2 validated 3 no validation',\r\n PRIMARY KEY (`id`),\r\n KEY `ts` (`insert_timestamp`,`thread_user`),\r\n KEY `user` (`thread_user`),\r\n KEY `host` (`thread_host`),\r\n KEY `status` (`status`)\r\n) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;\r\n<\/pre>\nNext, I have written an event and added it to the Event Scheduler:<\/p>\n
How does it work?<\/h3>\n
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.<\/p>\n
I hope this helps anyone! <\/p>\n
\r\nDROP EVENT IF EXISTS KILL_GHOST_TRX;\r\n \r\nCREATE definer=root@localhost EVENT KILL_GHOST_TRX\r\nON SCHEDULE EVERY 1 MINUTE\r\nDO\r\n\r\nMAIN_BLOCK: BEGIN\r\n\r\n\t-- private variables\r\n\tDECLARE __done INT DEFAULT 0;\r\n\t\r\n\t-- public variables\r\n\tDECLARE TRANSACTION_TIME_LIMIT_v TIMESTAMP DEFAULT DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 2 MINUTE); \r\n\tDECLARE THREAD_ID_v BIGINT(21) UNSIGNED DEFAULT 0;\r\n\tDECLARE THREAD_STATE_v, THREAD_HOST_v VARCHAR(64);\r\n\tDECLARE THREAD_INFO_v LONGTEXT;\r\n\tDECLARE THREAD_COMMAND_v, THREAD_USER_v, TRX_STATE_v VARCHAR(16);\r\n\tDECLARE CURSOR_QTY INT(11) DEFAULT 0;\r\n\tDECLARE BLOCKING_TRX_ID_v VARCHAR(18);\r\n\tDECLARE p_Kill TINYINT DEFAULT 0;\r\n\t\r\n\t-- cursors\r\n\tDECLARE GHOST_TRX_CURSOR CURSOR FOR SELECT SQL_CALC_FOUND_ROWS blocking_trx_id FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS GROUP BY 1; \r\n\t\r\n\t-- handlers\r\n\tDECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET __done = 1;\r\n\tDECLARE CONTINUE HANDLER FOR NOT FOUND \t\tSET __done = 1;\r\n \r\n\t-- [START]\r\n\t-- Is there any blocking? \r\n\tOPEN GHOST_TRX_CURSOR;\r\n\t\r\n\tSET CURSOR_QTY = (Select FOUND_ROWS());\r\n\t\r\n\tIF CURSOR_QTY > 0 THEN\r\n\t\t\r\n\t\tBLOCKING_LOOP: LOOP FETCH \r\n\t\t\tGHOST_TRX_CURSOR INTO BLOCKING_TRX_ID_v;\t\t\r\n\t\t\tIF __done = 1 THEN\r\n\t\t\t -- nothing to do\r\n\t\t\t LEAVE BLOCKING_LOOP;\r\n\t\t\tEND IF;\r\n\t\t\t\t\r\n\t\t\tIF p_Kill = 0 THEN\r\n\t\t\t\tPREPARE p_Kill FROM \"KILL \/* KILL_GHOST_TRX *\/ ? ;\";\r\n\t\t\tEND IF;\r\n\t\r\n\t\t\tSELECT trx.trx_mysql_thread_id\t, IFNULL(trx_state, 'none'), \t\ttrx_query, \t\t`USER`,\t\t\t`HOST`, \t\tIFNULL(`STATE`, 'none')\r\n\t\t\t\tINTO THREAD_ID_v\t\t\t, TRX_STATE_v,\tTHREAD_INFO_v,\t`THREAD_USER_v`,\t`THREAD_HOST_v`, `THREAD_STATE_v` \r\n\t\t\t\tFROM INFORMATION_SCHEMA.INNODB_TRX trx JOIN INFORMATION_SCHEMA.PROCESSLIST ps ON trx.trx_mysql_thread_id = ps.ID \r\n\t\t\t\t\tWHERE trx_started < TRANSACTION_TIME_LIMIT_v AND `USER` NOT IN ('system_user', 'root','event_scheduler') LIMIT 1;\r\n\t\t\t \r\n\t\t\tif THREAD_ID_v IS NOT NULL AND THREAD_ID_v > 0 THEN\t \r\n\t\t\t\r\n\t\t\t\t-- Save logs...\r\n\t\t\t\tINSERT IGNORE INTO INNODB_TRX\t\t\tSELECT * FROM information_schema.INNODB_TRX;\r\n\t\t\t\tINSERT IGNORE INTO INNODB_LOCKS\t\t\tSELECT * FROM information_schema.INNODB_LOCKS;\r\n\t\t\t\tINSERT IGNORE INTO INNODB_LOCK_WAITS\tSELECT * FROM information_schema.INNODB_LOCK_WAITS;\r\n\t\t\t\tINSERT IGNORE INTO PROCESSLIST\t\t\tSELECT *, THREAD_ID_v FROM information_schema.PROCESSLIST;\r\n\t\t\t\r\n\t\t\t\tSET @THREAD_ID = THREAD_ID_v;\r\n\t\t\t EXECUTE p_Kill USING @THREAD_ID;\r\n\t\t\t\r\n\t\t\t INSERT INTO GHOST_TRX_LOG\t(`thread_id`,\t`thread_user`,\t\t`thread_host`, \t\t`thread_info`,\t\t`thread_status`, `status`) \r\n\t\t\t \tVALUES \t\t\t\t\t(`THREAD_ID_v`,\t`THREAD_USER_v`,\t`THREAD_HOST_v`,\t`THREAD_INFO_v`,\t`THREAD_STATE_v`, 1);\r\n\t\t\tELSE\r\n\t\t\t INSERT INTO GHOST_TRX_LOG\t(`thread_id`,\t`thread_user`,\t`thread_host`, \t\t`thread_info`,\t\t`thread_status`, `status`) \r\n\t\t\t \tVALUES \t\t\t\t\t(`THREAD_ID_v`,\t`THREAD_USER_v`,\t`THREAD_HOST_v`,\t`THREAD_INFO_v`,\t`THREAD_STATE_v`, 3);\r\n\t\t\tEND IF;\r\n\t\t\tSET __done = 0;\r\n\t\t\t\r\n\t\tEND LOOP;\r\n\t\tCLOSE GHOST_TRX_CURSOR;\t\r\n\t\t\r\n\t\tIF p_Kill THEN\r\n\t\t\tDEALLOCATE PREPARE p_Kill;\r\n\t\tEND IF;\r\n\tEND IF;\t\r\n\t-- DELETE FROM GHOST_TRX_LOG WHERE insert_timestamp < NOW() - INTERVAL 31 DAY;\r\n\tEND MAIN_BLOCK;\r\n\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[7,3],"tags":[],"_links":{"self":[{"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=\/wp\/v2\/posts\/415"}],"collection":[{"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=415"}],"version-history":[{"count":15,"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=\/wp\/v2\/posts\/415\/revisions"}],"predecessor-version":[{"id":448,"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=\/wp\/v2\/posts\/415\/revisions\/448"}],"wp:attachment":[{"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=415"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=415"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=415"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}