title: How to use database transactions in JavaScript with async functions?
body: <p>Consider the code below that gets some data from an external service and inserts it into some table in a database. It starts the transactions and commits it when all the data is inserted or rolls it back if an error occurres:</p>
  
  <pre><code>const mysql = require('mysql2/promise');
  
  class SomeClass
  {
      async connect(params)
      {
          this.db = await mysql.createConnection(params);
      }
  
      async insertSomeRowsIntoSomeTable()
      {
          await this.db.execute('START TRANSACTION;');
  
          try {
              for (let i = 0; i &lt; 100; i++) {
                  //get the values to insert from an external service, for example.
                  await const values = service.getValues();
                  await this.db.execute('INSERT SomeTable .... &lt;the values&gt;...');
              }
              await this.db.execute('COMMIT;');
          } 
          catch (e) {
              await this.db.execute('ROLLBACK;');
          }
      }
  }
  </code></pre>
  
  <p>but what if there is another async method <code>updateSomething</code> that updates a row in some table with a single update query and the client calls it while <code>insertSomeRowsIntoSomeTable</code> method is still running? Obviously, the updated data will be lost if the an error occurres in <code>insertSomeRowsIntoSomeTable</code>.</p>
  
  <p>If there is another method that initiates its own transaction, for example, we unexpectedly get the nested transaction.</p>
  
  <p>The only idea I have at the moment is to make all the operations with the database atomic by moving all the transaction logic to the stored procedures, for example, but it look like a workaround but not a solution.</p>
  
  <p>Having a separate database connection in each async method is not an option, for the obvious reasons.</p>
  
  <p>Does anyone have a better idea?</p>