Atomicity

Atomicity in SQL refers to one of the ACID properties, which ensures that we use transactions while querying data using SQL.
In the context of SQL databases, atomicity guarantees that all operations within a particular logical unit are completed, or none of them are.

Transaction processing in SQL

Key features

• Rollback : If any part fails (e.g., due to error or constraint violation), the entire transaction is rolled back, reverting changes;

• Commit : If all operations succeed, the transaction commits, making changes permanent.

Creating transactions in SQL

In SQL, each individual statement is considered as a transaction.
However, we can manually create transactions that contain more than one statement.

Let's imagine a scenario where we have two tables:

• the BankAccounts table, which includes the following columns: account_number (Primary Key), account_holder , and balance ;

• UserLogs table with columns: account_number , action , timestamp etc. The combination of account_number and timestamp is a composite primary key of this relation.

Now, let's consider a scenario where we intend to create a new bank account and concurrently generate a log entry to signify the addition of the new account.
It's imperative that these two actions, adding the account and logging the event, are treated as a single logical unit and must be wrapped in a single transaction. Here's a very basic example of how we might do this with a transaction:

sql
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- Begin the transaction
BEGIN;

-- Insert a new bank account with account number 109, account holder Emma Watson, and initial balance of 0
INSERT INTO BankAccounts (account_number, account_holder, balance)
VALUES (109, 'Emma Watson', 0);

-- Step 2: Add log entry if the account was added
-- Insert a log entry into the UserLogs table indicating that a new account was added with account number 109
INSERT INTO UserLogs (account_number, action)
VALUES (109, 'New account added');

-- Commit the transaction, making the changes permanent
COMMIT;

In the query above, we use the BEGIN block to signify that all following statements must be considered as a single unit - if one of them is not executed, none of the statements must be executed.
The COMMIT keyword signifies the end of the transaction block.