Can you explain what is meant by a transaction in the context of databases?

A transaction in databases is a sequence of operations that is executed as a single unit. It either completes successfully (commit) or is completely rolled back (rollback) if an error occurs. This guarantees data integrity and prevents partial updates.

The main properties of transactions are described by the acronym ACID:

• Atomicity — all operations within a transaction are either fully completed or not executed at all.
• Consistency — after a transaction is executed, the database remains in a correct state.
• Isolation — parallel transactions do not affect each other.
• Durability — after a transaction is committed, the changes are saved even in case of failures.

Example in Node.js using the pg library for PostgreSQL:

const { Client } = require('pg');

async function runTransaction() {
  const client = new Client();
  await client.connect();

  try {
    await client.query('BEGIN');
    await client.query('INSERT INTO users(name) VALUES($1)', ['Alice']);
    await client.query('INSERT INTO accounts(user_id, balance) VALUES($1, $2)', [1, 100]);
    await client.query('COMMIT');
  } catch (e) {
    await client.query('ROLLBACK');
    throw e;
  } finally {
    await client.end();
  }
}