Section (2.4) - Node.js MySQL Transactions

In this tutorial, you will learn about MySQL transactions and how to handle them in your Node.js applications. Transactions are crucial when you need to perform multiple database operations that should either all succeed or all fail. They help maintain the consistency and integrity of your data.

Understanding Transactions

Transactions are a series of one or more database operations that are executed as a single unit of work. If all operations within the transaction succeed, the transaction is committed, and all changes are saved to the database. If any operation fails, the transaction is rolled back, and all changes are discarded.

Transactions follow the ACID properties:

• Atomicity: The transaction is either fully completed or entirely aborted.
• Consistency: The database remains in a consistent state before and after the transaction.
• Isolation: Each transaction is isolated from other transactions.
• Durability: Once a transaction is committed, its changes are permanent.

Starting a Transaction

To start a transaction in Node.js using the mysql package, you need to call the beginTransaction method on the connection object.

connection.beginTransaction(err => {
  if (err) throw err;
  // Perform your database operations here
});

Committing a Transaction

After executing all database operations, you can commit the transaction using the commit method.

connection.commit(err => {
  if (err) {
    return connection.rollback(() => {
      throw err;
    });
  }
  console.log('Transaction committed successfully.');
});

Rolling Back a Transaction

If any database operation within the transaction fails, you can roll back the transaction using the rollback method.

connection.rollback(() => {
  console.log('Transaction rolled back.');
});

Example: Transfer Money Between Accounts

In this example, we will transfer money between two accounts. If any part of the transfer fails, the transaction should be rolled back to maintain data consistency.

connection.beginTransaction(err => {
  if (err) throw err;

  const sql1 = 'UPDATE accounts SET balance = balance - ? WHERE id = ?';
  connection.query(sql1, [100, 1], (err, result) => {
    if (err) {
      return connection.rollback(() => {
        throw err;
      });
    }

    const sql2 = 'UPDATE accounts SET balance = balance + ? WHERE id = ?';
    connection.query(sql2, [100, 2], (err, result) => {
      if (err) {
        return connection.rollback(() => {
          throw err;
        });
      }

      connection.commit(err => {
        if (err) {
          return connection.rollback(() => {
            throw err;
          });
        }
        console.log('Transaction completed successfully.');
      });
    });
  });
});

Frequently Asked Questions

Q: How do I handle nested transactions?

A: MySQL does not support nested transactions. However, you can use savepoints to achieve similar functionality. Savepoints allow you to set a marker within a transaction, to which you can later roll back. To create a savepoint, use the SAVEPOINT statement, and to roll back to a savepoint, use the ROLLBACK TO statement.