What methods exist to control the integrity and atomicity of transactions in relational database management systems?

In relational DBMSs, the following methods are used to control integrity and ensure transaction atomicity:

• Transactions and ACID: A transaction is a sequence of operations that is executed completely or not at all (atomicity). ACID is a set of properties of transactions:

  • Atomicity — all operations within a transaction are either executed or rolled back.
  • Consistency — a transaction transfers the database from one correct state to another.
  • Isolation — parallel transactions do not affect each other.
  • Durability — after a transaction is committed, changes are preserved even in case of failures.

• Data integrity control mechanisms:

  • Constraints — primary keys, foreign keys, unique constraints, checks.
  • Triggers — automatic procedures that execute upon data changes.

• Locking and logging mechanisms:

  • Locks — to ensure transaction isolation.
  • Transaction log — for data recovery and rollback.

Example of using a transaction in Java with JDBC:

Connection conn = dataSource.getConnection();
try {
    conn.setAutoCommit(false); // start transaction
    // Execute SQL operations
    PreparedStatement ps = conn.prepareStatement("INSERT INTO accounts (id, balance) VALUES (?, ?)");
    ps.setInt(1, 1);
    ps.setBigDecimal(2, new BigDecimal("1000"));
    ps.executeUpdate();
    
    // Other operations
    
    conn.commit(); // commit transaction
} catch (SQLException e) {
    conn.rollback(); // rollback on error
} finally {
    conn.setAutoCommit(true);
    conn.close();
}