{"id":13503,"date":"2023-12-26T19:21:11","date_gmt":"2023-12-27T02:21:11","guid":{"rendered":"https:\/\/www.mysqltutorial.org\/?page_id=13503"},"modified":"2023-12-26T19:34:15","modified_gmt":"2023-12-27T02:34:15","slug":"mysql-transactions","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-stored-procedure\/mysql-transactions\/","title":{"rendered":"MySQL Transactions"},"content":{"rendered":"\n<p><strong>Summary<\/strong>: in this tutorial, you will learn about\u00a0MySQL transactions<strong>\u00a0<\/strong>and how to use the <code>START TRANSACTION<\/code>, <code>COMMIT<\/code>\u00a0and\u00a0<code>ROLLBACK<\/code>\u00a0statements to manage transactions in MySQL.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Introduction to MySQL transactions<\/h2>\n\n\n\n<p>In the database world, a transaction is a sequence of one or more SQL statements that are executed as a single unit of work.<\/p>\n\n\n\n<p>Transactions allow you to ensure the integrity of data by enabling a set of operations to be either fully completed or fully rolled back in case of an error.<\/p>\n\n\n\n<p>MySQL supports transactions via the <code>START TRANSACTION<\/code>, <code>COMMIT<\/code>, and <code>ROLLBACK<\/code>statements:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>START TRANSACTION<\/code> &#8211; Mark the beginning of a transaction. Note that the\u00a0<code>BEGIN<\/code>\u00a0or\u00a0\u00a0<code>BEGIN<\/code> <code>WORK<\/code>\u00a0are the aliases of the\u00a0<code>START TRANSACTION<\/code>.<\/li>\n\n\n\n<li><code>COMMIT<\/code> &#8211; Apply the changes of a transaction to the database.<\/li>\n\n\n\n<li><code>ROLLBACK<\/code> &#8211; Undo the changes of a transaction by reverting the database to the state before the transaction starts.<\/li>\n<\/ul>\n\n\n\n<p>By default, when you execute an SQL statement, MySQL automatically wraps it in a transaction and commits the transaction automatically.<\/p>\n\n\n\n<p>To instruct MySQL to not start a transaction implicitly and commit the changes automatically, you set the value of the <code>autocommit<\/code> variable to <code>0<\/code> or <code>OFF<\/code>:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-1\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">SET<\/span> autocommit = <span class=\"hljs-keyword\">OFF<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-1\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Or:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-2\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">SET<\/span> autocommit = <span class=\"hljs-number\">0<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-2\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>To enable the auto-commit mode, you set the value of the <code>autocommit<\/code> variable to <code>1<\/code> or <code>ON<\/code>:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-3\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">SET<\/span> autocommit = <span class=\"hljs-number\">1<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-3\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Or:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-4\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">SET<\/span> autocommit = <span class=\"hljs-keyword\">ON<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-4\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h2 class=\"wp-block-heading\">Basic MySQL transactions example<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">1) Setting up sample tables<\/h3>\n\n\n\n<p>First, <a href=\"https:\/\/www.mysqltutorial.org\/getting-started-with-mysql\/connect-to-mysql-server\/\">connect to the MySQL server<\/a>:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-5\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\">mysql -u root -p<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-5\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Second, <a href=\"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-create-database\/\">create a database<\/a> called <code>banks<\/code> and a table named <code>users<\/code>:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-6\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">CREATE<\/span> <span class=\"hljs-keyword\">DATABASE<\/span> banks;\n\n<span class=\"hljs-keyword\">USE<\/span> banks;\n\n<span class=\"hljs-keyword\">CREATE<\/span> <span class=\"hljs-keyword\">TABLE<\/span> <span class=\"hljs-keyword\">users<\/span> (\n    <span class=\"hljs-keyword\">id<\/span> <span class=\"hljs-built_in\">INT<\/span> PRIMARY <span class=\"hljs-keyword\">KEY<\/span>,\n    username <span class=\"hljs-built_in\">VARCHAR<\/span>(<span class=\"hljs-number\">255<\/span>) <span class=\"hljs-keyword\">NOT<\/span> <span class=\"hljs-literal\">NULL<\/span>,\n    email <span class=\"hljs-built_in\">VARCHAR<\/span>(<span class=\"hljs-number\">255<\/span>)\n);<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-6\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h3 class=\"wp-block-heading\">2) MySQL COMMIT example<\/h3>\n\n\n\n<p>First, start a transaction that <a href=\"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-insert\/\">inserts a new row<\/a> into the <code>users<\/code> table and updates the email for the user:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-7\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">START<\/span> <span class=\"hljs-keyword\">TRANSACTION<\/span>;\n\n<span class=\"hljs-keyword\">INSERT<\/span> <span class=\"hljs-keyword\">INTO<\/span> <span class=\"hljs-keyword\">users<\/span> (<span class=\"hljs-keyword\">id<\/span>, username) \n<span class=\"hljs-keyword\">VALUES<\/span> (<span class=\"hljs-number\">1<\/span>, <span class=\"hljs-string\">'john'<\/span>);\n\n\n<span class=\"hljs-keyword\">UPDATE<\/span> <span class=\"hljs-keyword\">users<\/span> \n<span class=\"hljs-keyword\">SET<\/span> email = <span class=\"hljs-string\">'john.doe@example.com'<\/span> \n<span class=\"hljs-keyword\">WHERE<\/span> <span class=\"hljs-keyword\">id<\/span> = <span class=\"hljs-number\">1<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-7\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Second, <a href=\"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-select-from\/\">retrieve data<\/a> from the <code>users<\/code> table:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-8\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">SELECT<\/span> * <span class=\"hljs-keyword\">FROM<\/span> <span class=\"hljs-keyword\">users<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-8\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Output:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-9\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\">+<span class=\"hljs-comment\">----+----------+----------------------+<\/span>\n| id | username | email                |\n+<span class=\"hljs-comment\">----+----------+----------------------+<\/span>\n|  1 | john     | john.doe@example.com |\n+<span class=\"hljs-comment\">----+----------+----------------------+<\/span>\n1 row in <span class=\"hljs-keyword\">set<\/span> (<span class=\"hljs-number\">0.00<\/span> sec)<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-9\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>The output shows that the <code>users<\/code> table has a row but it is only visible to the current session, not other sessions.<\/p>\n\n\n\n<p>If you open another session and query data from the <code>users<\/code> table, you will not see any rows in the <code>users<\/code> table. The reason is that the transaction in the first session has not been committed.<\/p>\n\n\n\n<p>Fifth, commit the transaction:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-10\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">COMMIT<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-10\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>The <code>COMMIT<\/code> statement applies all the changes made during the transaction, making them permanent and visible to other database sessions.<\/p>\n\n\n\n<p>If you open the second session, you will be able to see the data from the <code>users<\/code> table.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">3) MySQL ROLLBACK example<\/h3>\n\n\n\n<p>First, start a transaction:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-11\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">START<\/span> <span class=\"hljs-keyword\">TRANSACTION<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-11\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Second, insert a new row into the <code>users<\/code> table and update the <code>email<\/code> for the user:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-12\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">INSERT<\/span> <span class=\"hljs-keyword\">INTO<\/span> <span class=\"hljs-keyword\">users<\/span> (<span class=\"hljs-keyword\">id<\/span>, username) \n<span class=\"hljs-keyword\">VALUES<\/span> (<span class=\"hljs-number\">2<\/span>, <span class=\"hljs-string\">'jane'<\/span>);\n\n\n<span class=\"hljs-keyword\">UPDATE<\/span> <span class=\"hljs-keyword\">users<\/span> \n<span class=\"hljs-keyword\">SET<\/span> email = <span class=\"hljs-string\">'jane.doe@example.com'<\/span> \n<span class=\"hljs-keyword\">WHERE<\/span> <span class=\"hljs-keyword\">id<\/span> = <span class=\"hljs-number\">2<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-12\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Third, roll back the transaction:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-13\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">ROLLBACK<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-13\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>The <code>ROLLBACK<\/code> statement undoes all the changes made during the transaction, reverting the database to its state before the transaction started.<\/p>\n\n\n\n<p>Finally, select data from the <code>users<\/code> table:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-14\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">SELECT<\/span> * <span class=\"hljs-keyword\">FROM<\/span> <span class=\"hljs-keyword\">users<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-14\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Output:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-15\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\">+<span class=\"hljs-comment\">----+----------+----------------------+<\/span>\n| id | username | email                |\n+<span class=\"hljs-comment\">----+----------+----------------------+<\/span>\n|  1 | john     | john.doe@example.com |\n+<span class=\"hljs-comment\">----+----------+----------------------+<\/span>\n1 row in <span class=\"hljs-keyword\">set<\/span> (<span class=\"hljs-number\">0.00<\/span> sec)<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-15\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>The output shows that the transaction has been rolled back so there is no new row in the <code>users<\/code> table.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Using MySQL transactions in Stored Procedures<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">1) Setting up sample tables<\/h3>\n\n\n\n<p>First, change the current database to <code>banks<\/code>:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-16\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">CREATE<\/span> <span class=\"hljs-keyword\">DATABASE<\/span> banks;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-16\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Second, create a new table called <code>accounts<\/code> to store the account holders and balances:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-17\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">CREATE<\/span> <span class=\"hljs-keyword\">TABLE<\/span> accounts (\n    account_id <span class=\"hljs-built_in\">INT<\/span> AUTO_INCREMENT  PRIMARY <span class=\"hljs-keyword\">KEY<\/span> ,\n    account_holder <span class=\"hljs-built_in\">VARCHAR<\/span>(<span class=\"hljs-number\">255<\/span>) <span class=\"hljs-keyword\">NOT<\/span> <span class=\"hljs-literal\">NULL<\/span>,\n    balance <span class=\"hljs-built_in\">DECIMAL<\/span>(<span class=\"hljs-number\">10<\/span>, <span class=\"hljs-number\">2<\/span>) <span class=\"hljs-keyword\">NOT<\/span> <span class=\"hljs-literal\">NULL<\/span>\n);<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-17\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Third, create a table called <code>transactions<\/code> to store the transactions between accounts:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-18\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">CREATE<\/span> <span class=\"hljs-keyword\">TABLE<\/span> transactions (\n    transaction_id <span class=\"hljs-built_in\">INT<\/span> AUTO_INCREMENT PRIMARY <span class=\"hljs-keyword\">KEY<\/span>,\n    account_id <span class=\"hljs-built_in\">INT<\/span> <span class=\"hljs-keyword\">NOT<\/span> <span class=\"hljs-literal\">NULL<\/span>,\n    amount <span class=\"hljs-built_in\">DECIMAL<\/span>(<span class=\"hljs-number\">10<\/span>, <span class=\"hljs-number\">2<\/span>) <span class=\"hljs-keyword\">NOT<\/span> <span class=\"hljs-literal\">NULL<\/span>,\n    transaction_type ENUM(<span class=\"hljs-string\">'DEPOSIT'<\/span>, <span class=\"hljs-string\">'WITHDRAWAL'<\/span>) <span class=\"hljs-keyword\">NOT<\/span> <span class=\"hljs-literal\">NULL<\/span>,\n    <span class=\"hljs-keyword\">FOREIGN<\/span> <span class=\"hljs-keyword\">KEY<\/span> (account_id) <span class=\"hljs-keyword\">REFERENCES<\/span> accounts(account_id)\n);<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-18\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Finally, <a href=\"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-insert\/\">insert two rows<\/a> into the <code>accounts<\/code> table:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-19\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">INSERT<\/span> <span class=\"hljs-keyword\">INTO<\/span> accounts (account_holder, balance) \n<span class=\"hljs-keyword\">VALUES<\/span> (<span class=\"hljs-string\">'John Doe'<\/span>, <span class=\"hljs-number\">1000.00<\/span>),\n       (<span class=\"hljs-string\">'Jane Doe'<\/span>, <span class=\"hljs-number\">500.00<\/span>);<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-19\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h2 class=\"wp-block-heading\">Transferring money between two accounts<\/h2>\n\n\n\n<p>The following <a href=\"https:\/\/www.mysqltutorial.org\/mysql-stored-procedure\/getting-started-with-mysql-stored-procedures\/\">creates a stored procedure<\/a> that transfers money between two accounts:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-20\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\">DELIMITER \/\/\n\n<span class=\"hljs-keyword\">CREATE<\/span> <span class=\"hljs-keyword\">PROCEDURE<\/span> transfer(\n    <span class=\"hljs-keyword\">IN<\/span> sender_id <span class=\"hljs-built_in\">INT<\/span>,\n    <span class=\"hljs-keyword\">IN<\/span> receiver_id <span class=\"hljs-built_in\">INT<\/span>,\n    <span class=\"hljs-keyword\">IN<\/span> amount <span class=\"hljs-built_in\">DECIMAL<\/span>(<span class=\"hljs-number\">10<\/span>,<span class=\"hljs-number\">2<\/span>)\n)\n<span class=\"hljs-keyword\">BEGIN<\/span>\n    <span class=\"hljs-keyword\">DECLARE<\/span> rollback_message <span class=\"hljs-built_in\">VARCHAR<\/span>(<span class=\"hljs-number\">255<\/span>) <span class=\"hljs-keyword\">DEFAULT<\/span> <span class=\"hljs-string\">'Transaction rolled back: Insufficient funds'<\/span>;\n    <span class=\"hljs-keyword\">DECLARE<\/span> commit_message <span class=\"hljs-built_in\">VARCHAR<\/span>(<span class=\"hljs-number\">255<\/span>) <span class=\"hljs-keyword\">DEFAULT<\/span> <span class=\"hljs-string\">'Transaction committed successfully'<\/span>;\n\n    <span class=\"hljs-comment\">-- Start the transaction<\/span>\n    <span class=\"hljs-keyword\">START<\/span> <span class=\"hljs-keyword\">TRANSACTION<\/span>;\n\n    <span class=\"hljs-comment\">-- Attempt to debit money from account 1<\/span>\n    <span class=\"hljs-keyword\">UPDATE<\/span> accounts <span class=\"hljs-keyword\">SET<\/span> balance = balance - amount <span class=\"hljs-keyword\">WHERE<\/span> account_id = sender_id;\n\n    <span class=\"hljs-comment\">-- Attempt to credit money to account 2<\/span>\n    <span class=\"hljs-keyword\">UPDATE<\/span> accounts <span class=\"hljs-keyword\">SET<\/span> balance = balance + amount <span class=\"hljs-keyword\">WHERE<\/span> account_id = receiver_id;\n\n    <span class=\"hljs-comment\">-- Check if there are sufficient funds in account 1<\/span>\n    <span class=\"hljs-comment\">-- Simulate a condition where there are insufficient funds<\/span>\n    IF (<span class=\"hljs-keyword\">SELECT<\/span> balance <span class=\"hljs-keyword\">FROM<\/span> accounts <span class=\"hljs-keyword\">WHERE<\/span> account_id = sender_id) &lt; <span class=\"hljs-number\">0<\/span> <span class=\"hljs-keyword\">THEN<\/span>\n        <span class=\"hljs-comment\">-- Roll back the transaction if there are insufficient funds<\/span>\n        <span class=\"hljs-keyword\">ROLLBACK<\/span>;\n        SIGNAL SQLSTATE '45000'\n            <span class=\"hljs-keyword\">SET<\/span> MESSAGE_TEXT = rollback_message;\n    ELSE\n        <span class=\"hljs-comment\">-- Log the transactions if there are sufficient funds<\/span>\n        <span class=\"hljs-keyword\">INSERT<\/span> <span class=\"hljs-keyword\">INTO<\/span> transactions (account_id, amount, transaction_type) <span class=\"hljs-keyword\">VALUES<\/span> (sender_id, -amount, <span class=\"hljs-string\">'WITHDRAWAL'<\/span>);\n        <span class=\"hljs-keyword\">INSERT<\/span> <span class=\"hljs-keyword\">INTO<\/span> transactions (account_id, amount, transaction_type) <span class=\"hljs-keyword\">VALUES<\/span> (receiver_id, amount, <span class=\"hljs-string\">'DEPOSIT'<\/span>);\n        \n        <span class=\"hljs-comment\">-- Commit the transaction<\/span>\n        <span class=\"hljs-keyword\">COMMIT<\/span>;\n        <span class=\"hljs-keyword\">SELECT<\/span> commit_message <span class=\"hljs-keyword\">AS<\/span> <span class=\"hljs-string\">'Result'<\/span>;\n    <span class=\"hljs-keyword\">END<\/span> <span class=\"hljs-keyword\">IF<\/span>;\n<span class=\"hljs-keyword\">END<\/span> \/\/\n\nDELIMITER ;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-20\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>The transfer stored procedure transfers an amount between two accounts: sender and receiver. It has three parameters:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>sender_id<\/code> &#8211; the sender id.<\/li>\n\n\n\n<li><code>receiver_id<\/code> &#8211; the receiver id.<\/li>\n\n\n\n<li><code>amount<\/code> &#8211; the amount that will be transferred between the two accounts.<\/li>\n<\/ul>\n\n\n\n<p>How it works.<\/p>\n\n\n\n<p>First, start the transaction using the <code>START TRANSACTION<\/code> statement:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-21\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">START<\/span> <span class=\"hljs-keyword\">TRANSACTION<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-21\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Second, increase the balance of the sender and decrease the balance of the receiver:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-22\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">UPDATE<\/span> \n  accounts \n<span class=\"hljs-keyword\">SET<\/span> \n  balance = balance - amount \n<span class=\"hljs-keyword\">WHERE<\/span> \n  account_id = sender_id;\n  \n<span class=\"hljs-keyword\">UPDATE<\/span> \n  accounts \n<span class=\"hljs-keyword\">SET<\/span> \n  balance = balance + amount \n<span class=\"hljs-keyword\">WHERE<\/span> \n  account_id = receiver_id;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-22\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Third, roll back the transaction if the balance of the sender&#8217;s account is not sufficient and also issue an error message:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-23\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\">IF (<span class=\"hljs-keyword\">SELECT<\/span> balance <span class=\"hljs-keyword\">FROM<\/span> accounts <span class=\"hljs-keyword\">WHERE<\/span> account_id = sender_id) &lt; <span class=\"hljs-number\">0<\/span> <span class=\"hljs-keyword\">THEN<\/span>\n   <span class=\"hljs-keyword\">ROLLBACK<\/span>;\n   \n   SIGNAL SQLSTATE '45000'\n            <span class=\"hljs-keyword\">SET<\/span> MESSAGE_TEXT = rollback_message;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-23\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Otherwise, add two rows to the <code>transactions<\/code> table and apply the changes to the database by committing the transaction (in the <code>ELSE<\/code> branch):<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-24\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">INSERT<\/span> <span class=\"hljs-keyword\">INTO<\/span> transactions (account_id, amount, transaction_type) <span class=\"hljs-keyword\">VALUES<\/span> (<span class=\"hljs-number\">1<\/span>, -amount, <span class=\"hljs-string\">'WITHDRAWAL'<\/span>);\n<span class=\"hljs-keyword\">INSERT<\/span> <span class=\"hljs-keyword\">INTO<\/span> transactions (account_id, amount, transaction_type) <span class=\"hljs-keyword\">VALUES<\/span> (<span class=\"hljs-number\">2<\/span>, amount, <span class=\"hljs-string\">'DEPOSIT'<\/span>);\n\n<span class=\"hljs-keyword\">COMMIT<\/span>;\n<span class=\"hljs-keyword\">SELECT<\/span> commit_message <span class=\"hljs-keyword\">AS<\/span> <span class=\"hljs-string\">'Result'<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-24\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h2 class=\"wp-block-heading\">Calling the transfer stored procedure<\/h2>\n\n\n\n<p>First, retrieve the balances of the accounts:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-25\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">SELECT<\/span> * <span class=\"hljs-keyword\">FROM<\/span> accounts;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-25\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Output:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-26\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\">+<span class=\"hljs-comment\">------------+----------------+---------+<\/span>\n| account_id | account_holder | balance |\n+<span class=\"hljs-comment\">------------+----------------+---------+<\/span>\n|          1 | John Doe       | 1000.00 |\n|          2 | Jane Doe       |  500.00 |\n+<span class=\"hljs-comment\">------------+----------------+---------+<\/span>\n2 rows in <span class=\"hljs-keyword\">set<\/span> (<span class=\"hljs-number\">0.00<\/span> sec)<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-26\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Second, transfer 100 from account id 1 to account id 2:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-27\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">CALL<\/span> transfer(<span class=\"hljs-number\">1<\/span>,<span class=\"hljs-number\">2<\/span>,<span class=\"hljs-number\">100<\/span>);<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-27\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Output:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-28\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\">+<span class=\"hljs-comment\">------------------------------------+<\/span>\n| Result                             |\n+<span class=\"hljs-comment\">------------------------------------+<\/span>\n| Transaction committed successfully |\n+<span class=\"hljs-comment\">------------------------------------+<\/span>\n1 row in <span class=\"hljs-keyword\">set<\/span> (<span class=\"hljs-number\">0.01<\/span> sec)\n\n<span class=\"hljs-keyword\">Query<\/span> OK, <span class=\"hljs-number\">0<\/span> <span class=\"hljs-keyword\">rows<\/span> affected (<span class=\"hljs-number\">0.01<\/span> sec)<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-28\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Since account id 1 has sufficient funds, the transaction succeeded.<\/p>\n\n\n\n<p>Third, review the balances of the accounts:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-29\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">SELECT<\/span> \n  * \n<span class=\"hljs-keyword\">FROM<\/span> \n  accounts \n<span class=\"hljs-keyword\">WHERE<\/span> \n  account_id <span class=\"hljs-keyword\">IN<\/span> (<span class=\"hljs-number\">1<\/span>, <span class=\"hljs-number\">2<\/span>);<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-29\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Output:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-30\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\">+<span class=\"hljs-comment\">------------+----------------+---------+<\/span>\n| account_id | account_holder | balance |\n+<span class=\"hljs-comment\">------------+----------------+---------+<\/span>\n|          1 | John Doe       |  900.00 |\n|          2 | Jane Doe       |  600.00 |\n+<span class=\"hljs-comment\">------------+----------------+---------+<\/span>\n2 rows in <span class=\"hljs-keyword\">set<\/span> (<span class=\"hljs-number\">0.00<\/span> sec)<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-30\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>The output indicates that account id 1 has a balance of 900 and account id 2 has a balance of 600, which is correct.<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-31\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">SELECT<\/span> * <span class=\"hljs-keyword\">FROM<\/span> transactions;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-31\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Output:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-32\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\">+<span class=\"hljs-comment\">----------------+------------+---------+------------------+<\/span>\n| transaction_id | account_id | amount  | transaction_type |\n+<span class=\"hljs-comment\">----------------+------------+---------+------------------+<\/span>\n|              1 |          1 | -100.00 | WITHDRAWAL       |\n|              2 |          2 |  100.00 | DEPOSIT          |\n+<span class=\"hljs-comment\">----------------+------------+---------+------------------+<\/span>\n2 rows in <span class=\"hljs-keyword\">set<\/span> (<span class=\"hljs-number\">0.00<\/span> sec)<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-32\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>The <code>transactions<\/code> table also has two rows that record the transfer.<\/p>\n\n\n\n<p>Fourth, attempt to transfer 1000 from account id 1 to account id 2:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-33\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">CALL<\/span> transfer(<span class=\"hljs-number\">1<\/span>,<span class=\"hljs-number\">2<\/span>,<span class=\"hljs-number\">1000<\/span>);<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-33\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Output:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-34\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\">ERROR 1644 (45000): Transaction rolled back: Insufficient funds<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-34\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Because account id 1 does not have sufficient funds, the transaction was rolled back. Also, the balances of both accounts were reverted:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-35\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">SELECT<\/span> * <span class=\"hljs-keyword\">FROM<\/span> accounts;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-35\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Output:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-36\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\">+<span class=\"hljs-comment\">------------+----------------+---------+<\/span>\n| account_id | account_holder | balance |\n+<span class=\"hljs-comment\">------------+----------------+---------+<\/span>\n|          1 | John Doe       |  900.00 |\n|          2 | Jane Doe       |  600.00 |\n+<span class=\"hljs-comment\">------------+----------------+---------+<\/span>\n2 rows in <span class=\"hljs-keyword\">set<\/span> (<span class=\"hljs-number\">0.00<\/span> sec)\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-36\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h2 class=\"wp-block-heading\">Summary<\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li>A transaction is a sequence of SQL statements that is executed as a single unit of work.<\/li>\n\n\n\n<li>Use the <code>START<\/code> <code>TRANSACTION<\/code> statement to start a transaction.<\/li>\n\n\n\n<li>Use the <code>COMMIT<\/code> statement to apply the changes made during the transaction to the database.<\/li>\n\n\n\n<li>Use the <code>ROLLBACK<\/code> statement to roll back the changes made during the transaction and revert the state of the database before the transaction starts.<\/li>\n<\/ul>\n<div class=\"helpful-block-content\" data-title=\"\">\n\t<header>\n\t\t<div class=\"wth-question\">Was this tutorial helpful? <\/div>\n\t\t<div class=\"wth-thumbs\">\n\t\t\t<button\n\t\t\t\tdata-post=\"13503\"\n\t\t\t\tdata-post-url=\"https:\/\/www.mysqltutorial.org\/mysql-stored-procedure\/mysql-transactions\/\"\n\t\t\t\tdata-post-title=\"MySQL Transactions\"\n\t\t\t\tdata-response=\"1\"\n\t\t\t\tclass=\"wth-btn-rounded wth-yes-btn\"\n\t\t\t>\n\t\t\t\t<svg\n\t\t\t\t\txmlns=\"http:\/\/www.w3.org\/2000\/svg\"\n\t\t\t\t\tviewBox=\"0 0 24 24\"\n\t\t\t\t\tfill=\"none\"\n\t\t\t\t\tstroke=\"currentColor\"\n\t\t\t\t\tstroke-width=\"2\"\n\t\t\t\t\tstroke-linecap=\"round\"\n\t\t\t\t\tstroke-linejoin=\"round\"\n\t\t\t\t\tclass=\"feather feather-thumbs-up block w-full h-full\"\n\t\t\t\t>\n\t\t\t\t\t<path\n\t\t\t\t\t\td=\"M14 9V5a3 3 0 0 0-3-3l-4 9v11h11.28a2 2 0 0 0 2-1.7l1.38-9a2 2 0 0 0-2-2.3zM7 22H4a2 2 0 0 1-2-2v-7a2 2 0 0 1 2-2h3\"\n\t\t\t\t\t><\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t<span class=\"sr-only\"> Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t<button\n\t\t\t\tdata-response=\"0\"\n\t\t\t\tdata-post=\"13503\"\n\t\t\t\tdata-post-url=\"https:\/\/www.mysqltutorial.org\/mysql-stored-procedure\/mysql-transactions\/\"\n\t\t\t\tdata-post-title=\"MySQL Transactions\"\n\t\t\t\tclass=\"wth-btn-rounded wth-no-btn\"\n\t\t\t>\n\t\t\t\t<svg\n\t\t\t\t\txmlns=\"http:\/\/www.w3.org\/2000\/svg\"\n\t\t\t\t\tviewBox=\"0 0 24 24\"\n\t\t\t\t\tfill=\"none\"\n\t\t\t\t\tstroke=\"currentColor\"\n\t\t\t\t\tstroke-width=\"2\"\n\t\t\t\t\tstroke-linecap=\"round\"\n\t\t\t\t\tstroke-linejoin=\"round\"\n\t\t\t\t>\n\t\t\t\t\t<path\n\t\t\t\t\t\td=\"M10 15v4a3 3 0 0 0 3 3l4-9V2H5.72a2 2 0 0 0-2 1.7l-1.38 9a2 2 0 0 0 2 2.3zm7-13h2.67A2.31 2.31 0 0 1 22 4v7a2.31 2.31 0 0 1-2.33 2H17\"\n\t\t\t\t\t><\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t<span class=\"sr-only\"> No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t<div class=\"wth-form hidden\">\n\t\t<div class=\"wth-form-wrapper\">\n\t\t\t<div class=\"wth-title\"><\/div>\n\t\t\t\n\t\t\t<textarea class=\"wth-message\"><\/textarea>\n\n\t\t\t<button class=\"btn btn-primary wth-btn-submit\">Send<\/button>\n\t\t\t<button class=\"btn wth-btn-cancel\">Cancel<\/button>\n\t\t\n\t\t<\/div>\n\t<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>You will learn about\u00a0MySQL transactions and how to use the START TRANSACTION, COMMIT\u00a0and\u00a0ROLLBACK\u00a0statements to manage transactions in MySQL.<\/p>\n","protected":false},"author":2,"featured_media":0,"parent":518,"menu_order":27,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-13503","page","type-page","status-publish","hentry"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.5 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>MySQL Transactions<\/title>\n<meta name=\"description\" content=\"You will learn about\u00a0MySQL transactions and how to use the START TRANSACTION, COMMIT\u00a0and\u00a0ROLLBACK\u00a0statements to manage transactions in MySQL.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.mysqltutorial.org\/mysql-stored-procedure\/mysql-transactions\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"MySQL Transactions\" \/>\n<meta property=\"og:description\" content=\"You will learn about\u00a0MySQL transactions and how to use the START TRANSACTION, COMMIT\u00a0and\u00a0ROLLBACK\u00a0statements to manage transactions in MySQL.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.mysqltutorial.org\/mysql-stored-procedure\/mysql-transactions\/\" \/>\n<meta property=\"og:site_name\" content=\"MySQL Tutorial\" \/>\n<meta property=\"article:modified_time\" content=\"2023-12-27T02:34:15+00:00\" \/>\n<meta name=\"twitter:label1\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data1\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.mysqltutorial.org\\\/mysql-stored-procedure\\\/mysql-transactions\\\/\",\"url\":\"https:\\\/\\\/www.mysqltutorial.org\\\/mysql-stored-procedure\\\/mysql-transactions\\\/\",\"name\":\"MySQL Transactions\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.mysqltutorial.org\\\/#website\"},\"datePublished\":\"2023-12-27T02:21:11+00:00\",\"dateModified\":\"2023-12-27T02:34:15+00:00\",\"description\":\"You will learn about\u00a0MySQL transactions and how to use the START TRANSACTION, COMMIT\u00a0and\u00a0ROLLBACK\u00a0statements to manage transactions in MySQL.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.mysqltutorial.org\\\/mysql-stored-procedure\\\/mysql-transactions\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.mysqltutorial.org\\\/mysql-stored-procedure\\\/mysql-transactions\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.mysqltutorial.org\\\/mysql-stored-procedure\\\/mysql-transactions\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.mysqltutorial.org\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"MySQL Stored Procedures\",\"item\":\"https:\\\/\\\/www.mysqltutorial.org\\\/mysql-stored-procedure\\\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"MySQL Transactions\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.mysqltutorial.org\\\/#website\",\"url\":\"https:\\\/\\\/www.mysqltutorial.org\\\/\",\"name\":\"MySQL Tutorial\",\"description\":\"A comprehensive MySQL Tutorial\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.mysqltutorial.org\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"MySQL Transactions","description":"You will learn about\u00a0MySQL transactions and how to use the START TRANSACTION, COMMIT\u00a0and\u00a0ROLLBACK\u00a0statements to manage transactions in MySQL.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.mysqltutorial.org\/mysql-stored-procedure\/mysql-transactions\/","og_locale":"en_US","og_type":"article","og_title":"MySQL Transactions","og_description":"You will learn about\u00a0MySQL transactions and how to use the START TRANSACTION, COMMIT\u00a0and\u00a0ROLLBACK\u00a0statements to manage transactions in MySQL.","og_url":"https:\/\/www.mysqltutorial.org\/mysql-stored-procedure\/mysql-transactions\/","og_site_name":"MySQL Tutorial","article_modified_time":"2023-12-27T02:34:15+00:00","twitter_misc":{"Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.mysqltutorial.org\/mysql-stored-procedure\/mysql-transactions\/","url":"https:\/\/www.mysqltutorial.org\/mysql-stored-procedure\/mysql-transactions\/","name":"MySQL Transactions","isPartOf":{"@id":"https:\/\/www.mysqltutorial.org\/#website"},"datePublished":"2023-12-27T02:21:11+00:00","dateModified":"2023-12-27T02:34:15+00:00","description":"You will learn about\u00a0MySQL transactions and how to use the START TRANSACTION, COMMIT\u00a0and\u00a0ROLLBACK\u00a0statements to manage transactions in MySQL.","breadcrumb":{"@id":"https:\/\/www.mysqltutorial.org\/mysql-stored-procedure\/mysql-transactions\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.mysqltutorial.org\/mysql-stored-procedure\/mysql-transactions\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.mysqltutorial.org\/mysql-stored-procedure\/mysql-transactions\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.mysqltutorial.org\/"},{"@type":"ListItem","position":2,"name":"MySQL Stored Procedures","item":"https:\/\/www.mysqltutorial.org\/mysql-stored-procedure\/"},{"@type":"ListItem","position":3,"name":"MySQL Transactions"}]},{"@type":"WebSite","@id":"https:\/\/www.mysqltutorial.org\/#website","url":"https:\/\/www.mysqltutorial.org\/","name":"MySQL Tutorial","description":"A comprehensive MySQL Tutorial","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.mysqltutorial.org\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"}]}},"_links":{"self":[{"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/pages\/13503","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/comments?post=13503"}],"version-history":[{"count":5,"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/pages\/13503\/revisions"}],"predecessor-version":[{"id":13509,"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/pages\/13503\/revisions\/13509"}],"up":[{"embeddable":true,"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/pages\/518"}],"wp:attachment":[{"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/media?parent=13503"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}