{"id":398,"date":"2015-02-16T20:04:12","date_gmt":"2015-02-16T20:04:12","guid":{"rendered":"http:\/\/10sa.com\/sql_stories\/?p=398"},"modified":"2015-02-16T20:04:12","modified_gmt":"2015-02-16T20:04:12","slug":"unusual-mysql","status":"publish","type":"post","link":"http:\/\/10sa.com\/sql_stories\/?p=398","title":{"rendered":"Unusual: MySQL"},"content":{"rendered":"<p>PL\/SQL:<br \/>\nI wouldn&#8217;t recommend to use the following construction:<\/p>\n<pre lang=\"SQL\">IF EXISTS (SELECT 1 FROM `table_a` WHERE `id` = `_ID`) THEN\r\n    UPDATE `table_a` SET `value` = 1 WHERE `id` = `_ID`;\r\nELSE\r\n    INSERT INTO `table_a` [...];\r\nEND IF;\r\n<\/pre>\n<p>In my case there was a trigger (on table_a) who additionaly modifed &#8220;table_b&#8221;. When I changed the engine of table_a from MyISAM into InnoDB then I saw plenty of processes in state (Waiting for table level lock). The tables were locked (it was in production environment &#8211; huge traffic, it was not observable in the test environment). The solution was to replace this &#8220;EXISTS&#8221; into query and variable&#8230;<\/p>\n<pre lang=\"SQL\">SELECT IF(COUNT(*)>0, TRUE, FALSE) FROM `table_a` WHERE `id` = `_ID`\r\n    INTO EXISTS_v;\r\nIF EXISTS_v IS TRUE THEN\r\n     UPDATE [...];\r\nELSE\r\n     INSERT [...];\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>PL\/SQL: I wouldn&#8217;t recommend to use the following construction: IF EXISTS (SELECT 1 FROM `table_a` WHERE `id` = `_ID`) THEN UPDATE `table_a` SET `value` = 1 WHERE `id` = `_ID`; ELSE INSERT INTO `table_a` [&#8230;]; END IF; In my case there was a trigger (on table_a) who additionaly modifed &#8220;table_b&#8221;. When I changed the engine [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[7],"tags":[],"_links":{"self":[{"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=\/wp\/v2\/posts\/398"}],"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=398"}],"version-history":[{"count":11,"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=\/wp\/v2\/posts\/398\/revisions"}],"predecessor-version":[{"id":1076,"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=\/wp\/v2\/posts\/398\/revisions\/1076"}],"wp:attachment":[{"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=398"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=398"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=398"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}