What are transactions in a database? What properties and commands do they have?

A database transaction is a sequence of operations that are executed as a single unit. It either completes successfully (commit) or is completely rolled back (rollback) if an error occurs.

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 — a transaction transitions the database from one valid state to another.
• Isolation — concurrent transactions do not affect each other.
• Durability — after a transaction is committed, its changes are saved even in case of failures.

Key commands for working with transactions in SQL:

• BEGIN TRANSACTION or START TRANSACTION — start a transaction.
• COMMIT — apply the changes.
• ROLLBACK — undo the changes made in the transaction.

Example:

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

If an error occurs during the process, you can execute ROLLBACK to cancel the changes.