<div id="readability-page-1" class="page"><div id="main" role="main">
  <article>
    <div>
        
      
      

<h2 id="environment-and-prerequisite">Environment and Prerequisite</h2>

<ul>
  <li>MySQL</li>
</ul>



<h2 id="background">Background</h2>

<ul>
  <li>Thinking about ways to avoid making mistakes while using a database, I came up with the following ideas</li>
</ul>



<h2 id="start-transaction-commit-and-rollback-statements">START TRANSACTION, COMMIT, and ROLLBACK Statements</h2>

<h3 id="start-transaction">START TRANSACTION</h3>

<ul>
  <li>Start a new transaction.</li>
  <li>Remember the queries until COMMIT or ROLLBACK.</li>
</ul>

<h3 id="commit">COMMIT</h3>

<ul>
  <li>Commits the current transaction, making its changes permanent.</li>
  <li>The cases that trigger implicit commits can be found in the <a href="https://dev.mysql.com/doc/refman/8.0/en/implicit-commit.html">link</a>.</li>
</ul>

<h3 id="rollback">ROLLBACK</h3>

<ul>
  <li>Rolls back the current transaction, canceling its changes.</li>
  <li>Statements that cannot be rolled back can be referred to in the <a href="https://dev.mysql.com/doc/refman/8.0/en/cannot-roll-back.html">link</a>.</li>
</ul>



<h2 id="usage">Usage</h2>

<ul>
  <li>An example of mistakenly modifying the values of the row with id 2 in the example_table and then correcting it by updating the values of the row with id 3.</li>
  <li>In addition, TRANSACTION can be used effectively when many changes are needed in the database.</li>
</ul>

<div><pre><code><span>START</span> <span>TRANSACTION</span><span>;</span>
<span>UPDATE</span> <span>example_table</span> <span>SET</span> <span>status</span> <span>=</span> <span>'family'</span> <span>WHERE</span> <span>id</span> <span>=</span> <span>2</span><span>;</span>
<span>ROLLBACK</span><span>;</span>
<span>START</span> <span>TRANSACTION</span><span>;</span>
<span>UPDATE</span> <span>example_table</span> <span>SET</span> <span>status</span> <span>=</span> <span>'family'</span> <span>WHERE</span> <span>id</span> <span>=</span> <span>3</span><span>;</span>
<span>COMMIT</span><span>;</span>
</code></pre></div>



<h2 id="reference">Reference</h2>

<ul>
  <li><a href="https://dev.mysql.com/doc/refman/8.0/en/commit.html">https://dev.mysql.com/doc/refman/8.0/en/commit.html</a></li>
  <li><a href="https://dev.mysql.com/doc/refman/8.0/en/implicit-commit.html">https://dev.mysql.com/doc/refman/8.0/en/implicit-commit.html</a></li>
  <li><a href="https://dev.mysql.com/doc/refman/8.0/en/cannot-roll-back.html">https://dev.mysql.com/doc/refman/8.0/en/cannot-roll-back.html</a></li>
</ul>

      
        
        
        </div>
    
    
    

  </article>
</div></div>

TWpower’s Tech Blog

Environment and Prerequisite
MySQL
Background
Thinking about ways to avoid making mistakes while using a database, I came up with the following ideas
START TRANSACTION, COMMIT, and ROLLBACK Statements
START TRANSACTION
Start a new transaction.
Remember the queries until COMMIT or ROLLBACK.
COMMIT
Commits the current transaction, making its changes permanent.
The cases that trigger implicit commits can be found in the link.
ROLLBACK
Rolls back the current transaction, canceling its changes.
Statements that cannot be rolled back can be referred to in the link.
Usage
An example of mistakenly modifying the values of the row with id 2 in the example_table and then correcting it by updating the values of the row with id 3.
In addition, TRANSACTION can be used effectively when many changes are needed in the database.
START TRANSACTION;
UPDATE example_table SET status = 'family' WHERE id = 2;
ROLLBACK;
START TRANSACTION;
UPDATE example_table SET status = 'family' WHERE id = 3;
COMMIT;
Reference
https://dev.mysql.com/doc/refman/8.0/en/commit.html
https://dev.mysql.com/doc/refman/8.0/en/implicit-commit.html
https://dev.mysql.com/doc/refman/8.0/en/cannot-roll-back.html

[MySQL](EN) Database manipulation with the use of TRANSACTION

댓글