{"id":658,"date":"2009-03-18T09:37:56","date_gmt":"2009-03-18T07:37:56","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=658"},"modified":"2009-03-18T09:41:41","modified_gmt":"2009-03-18T07:41:41","slug":"lock-tables-in-myisam-is-not-a-poor-mans-tranactions-substitute","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/lock-tables-in-myisam-is-not-a-poor-mans-tranactions-substitute","title":{"rendered":"LOCK TABLES in MyISAM is NOT a poor man&#8217;s tranactions substitute"},"content":{"rendered":"<p>I get to hear that a lot: that LOCK TABLES with MyISAM is some sort of replacement for transactions; some model we can work with which gives us &#8216;transactional flavor&#8217;.<\/p>\n<p>It isn&#8217;t, and here&#8217;s why.<\/p>\n<p>When we speak of a transactional database\/engine, we check out its ACID compliance. Let&#8217;s break out the ACID and see what LOCK TABLES provides us with:<\/p>\n<ul>\n<li><strong>A<\/strong>: Atomicity. MyISAM does not provide atomicity.\u00a0 If we have LOCK TABLES followed by two statements, then closed by UNLOCK TABLES, then it follows that a crash between the two statements will have the first one applied, the second one not not applied. No mechanism ensures an &#8220;all or nothing&#8221; behavior.<\/li>\n<li><strong>C<\/strong>: Consistency. An error in a statement would roll back the entire transaction in a transactional database. This won&#8217;t work on MyISAM: every statement is &#8220;committed&#8221; immediately.<\/li>\n<li><strong>I<\/strong>: Isolation. Without LCOK TABLES, working with MyISAM resembles using the <strong>read uncommitted<\/strong>, or <strong>dirty read<\/strong> isolation level. With LOCK TABLES &#8211; it depends. If you were to use LOCK TABLES &#8230; WRITE on all tables in all statements, you would get the <strong>serializable<\/strong> isolation level. Actually it would be more than <strong>serializable<\/strong>. It would be <em>truely serial<\/em>.<\/li>\n<li><strong>D<\/strong>: Durability. Did the INSERT succeed? And did the power went down just after? MyISAM provides not guarantees that the data will be there.<\/li>\n<\/ul>\n<p><!--more-->So of all ACID properties, the only thing we could get is a <strong>serializable<\/strong> isolation level, and that, too, only if we used LOCK TABLES &#8230; WRITE\u00a0 practically everywhere.<\/p>\n<p>Where does the notion come from, then?<\/p>\n<p>There&#8217;s one thing which LOCK TABLES does help us with: race conditions. It effectively creates a mutex block. The same effect could be achieved when using GET_LOCK() and RELEASE_LOCK(). Perhaps this is the source of confusion.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>I get to hear that a lot: that LOCK TABLES with MyISAM is some sort of replacement for transactions; some model we can work with which gives us &#8216;transactional flavor&#8217;. It isn&#8217;t, and here&#8217;s why. When we speak of a transactional database\/engine, we check out its ACID compliance. Let&#8217;s break out the ACID and see [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"enabled":false},"version":2}},"categories":[5],"tags":[13],"class_list":["post-658","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-myisam"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-aC","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/658","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/comments?post=658"}],"version-history":[{"count":11,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/658\/revisions"}],"predecessor-version":[{"id":679,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/658\/revisions\/679"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=658"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=658"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=658"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}