{"id":30,"date":"2015-03-05T10:37:17","date_gmt":"2015-03-05T10:37:17","guid":{"rendered":"http:\/\/www.datatype.co.in\/blog\/?p=30"},"modified":"2020-07-26T10:38:43","modified_gmt":"2020-07-26T10:38:43","slug":"transaction-in-mysql","status":"publish","type":"post","link":"https:\/\/datatype.co.in\/blog\/transaction-in-mysql\/","title":{"rendered":"Transaction in MySQL"},"content":{"rendered":"\n<p>Sometime during software development, we face a situation where we need to execute more than two sql queries to successfully complete a process. All the dependent queries has to be executed in all-or-nothing mode. Say, to complete X process, we need to insert data in one table and update two another tables. Unfortunately, for various reasons, sometime some of the intended queries fails and create inconsistency in database.&nbsp; To get rid of such situation, we can use mysql transaction feature.<\/p>\n\n\n\n<p>A transaction is a set of inter-dependent SQL statements that needs to execute in all-or-nothing mode. A transaction is successful if all SQL statements executed successfully. A failure of any statement will trigger the system to rollback to the &nbsp;original state to avoid data inconsistency.<\/p>\n\n\n\n<p>Example (with PHP):<\/p>\n\n\n\n<pre class=\"wp-block-code prettyprint\"><code>mysql_query(\"START TRANSACTION\"); \/\/ start the transaction \n$q1 = mysql_query(\"some sql query\"); \n$q2 = mysql_query(\"some other sql query\"); \nif(q1 &amp; q2){ \nmysql_query(\"COMMIT\"); \/\/ commit if all queries are executed \n} else{\nmysql_query(\"ROLLBACK\"); \/\/ rollback to initial stage, recover data \n}<\/code><\/pre>\n\n\n\n<p>\n\nNote: It is good to use innoDB Engine while creating tables where mysql transaction has to be used as innoDB is transaction safe storage engine.\n\n<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Sometime during software development, we face a situation where we need to execute more than two sql queries to successfully complete a process. All the dependent queries has to be&nbsp;[ &hellip; ]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[22],"tags":[23,24],"class_list":["post-30","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-mysql-commit-and-rollback","tag-mysql-transactions","list-style-post"],"_links":{"self":[{"href":"https:\/\/datatype.co.in\/blog\/wp-json\/wp\/v2\/posts\/30","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/datatype.co.in\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/datatype.co.in\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/datatype.co.in\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/datatype.co.in\/blog\/wp-json\/wp\/v2\/comments?post=30"}],"version-history":[{"count":3,"href":"https:\/\/datatype.co.in\/blog\/wp-json\/wp\/v2\/posts\/30\/revisions"}],"predecessor-version":[{"id":33,"href":"https:\/\/datatype.co.in\/blog\/wp-json\/wp\/v2\/posts\/30\/revisions\/33"}],"wp:attachment":[{"href":"https:\/\/datatype.co.in\/blog\/wp-json\/wp\/v2\/media?parent=30"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/datatype.co.in\/blog\/wp-json\/wp\/v2\/categories?post=30"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/datatype.co.in\/blog\/wp-json\/wp\/v2\/tags?post=30"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}