Introduction

SQL transactions are a fundamental aspect of database management that play a crucial role in maintaining data integrity.
Among the key components of transactions, the COMMIT statement stands out as a linchpin for ensuring that changes made to a database are permanent.
In this blog post, we'll explore what SQL transactions and the COMMIT statement are, the problems they solve, and how to use SQL queries with and without transactions.

What is a SQL Transaction?
In the database world, a transaction is a sequence of one or more SQL statements that are executed as a single unit of work.
The primary purpose of transactions is to maintain the consistency and integrity of data.
A transaction typically involves multiple steps, such as reading data, making changes, and saving those changes to the database.

The COMMIT Statement

The COMMIT statement is a critical part of the SQL transaction model. When a COMMIT statement is executed,
it marks the end of a transaction, confirming that all changes made during the transaction are permanent and should be saved to the database.
In other words, COMMIT is like saying, "I'm done with my changes; please make them permanent."

Problems Solved by COMMIT

1. Data Consistency: COMMIT ensures that a transaction is completed successfully before making changes permanent. If an error occurs during the transaction, the COMMIT is not executed, and the changes are rolled back, maintaining the consistency of the data.
2. Concurrency Control: In multi-user environments, transactions help manage concurrent access to data. COMMIT ensures that one transaction's changes do not interfere with another's until explicitly instructed to do so.

Use COMMIT

How to Use SQL Queries with and without Transactions (COMMIT):

Without Transactions (Auto-Commit mode)

In many database systems, SQL statements are executed in auto-commit mode by default. Each statement is treated as a separate transaction, and changes are automatically committed to the database.

-- Auto-commit mode (default behavior)
INSERT INTO employees (id, name, salary) VALUES (1, 'John Doe', 50000);
-- The above statement is automatically committed

With Transactions (Manual COMMIT)

Consider a scenario where you need to transfer funds between two bank accounts.
Using transactions ensures the atomicity of the operation, meaning either the entire transaction succeeds, or it fails entirely with no partial changes committed.

-- Start a transaction
BEGIN TRANSACTION;

-- Deduct funds from Account A
UPDATE accounts SET balance = balance - 1000 WHERE account_id = 'A';

-- Add funds to Account B
UPDATE accounts SET balance = balance + 1000 WHERE account_id = 'B';

-- Commit the transaction
COMMIT;
-- or Rollback the transaction in case of errors
-- ROLLBACK;

Benefits of Using Transactions:

1. Atomicity:
2. Ensures that a series of SQL statements are treated as a single, indivisible unit of work.
3. Either all changes are committed, or none are.
4. Consistency:
5. Maintains the integrity and consistency of data, as the changes are applied as a cohesive whole.
6. Isolation:
7. Provides a level of isolation between transactions, preventing interference between concurrent operations.
8. Durability:
9. Guarantees that committed transactions are permanent and survive system failures.

Conclusion

Understanding SQL transactions and the role of COMMIT is crucial for maintaining data integrity and consistency.
Whether you're working in auto-commit mode or manually managing transactions, being mindful of these concepts ensures that your database operations are robust and reliable.

Originally posted on https://oholsyhevskyi.com/blog/commit-and-rollback-in-db