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
, andbalance
; UserLogs
table with columns:account_number
,action
,timestamp
etc. The combination ofaccount_number
andtimestamp
is a composite primary key of this relation.
account_number | account_holder | balance |
---|---|---|
789 | Alice Johnson | 3500.00 |
101 | Bob Brown | 4500.00 |
202 | Emily Jones | 6000.00 |
303 | Michael Davis | 800.00 |
404 | Sarah Wilson | 12000.00 |
505 | David Taylor | 300.00 |
606 | Emma Thomas | 7500.00 |
707 | Daniel Martinez | 1000.00 |
808 | Olivia Rodriguez | 900.00 |
909 | Sophia Lee | 2500.00 |
123 | John Doe | 5000.00 |
456 | Jane Smith | 2000.00 |
account_number | action | timestamp |
---|---|---|
789 | Account created for Alice Johnson | 2024-05-24 12:00:00 |
101 | Account created for Bob Brown | 2024-05-24 12:01:00 |
202 | Account created for Emily Jones | 2024-05-24 12:02:00 |
303 | Account created for Michael Davis | 2024-05-24 12:03:00 |
404 | Account created for Sarah Wilson | 2024-05-24 12:04:00 |
505 | Account created for David Taylor | 2024-05-24 12:05:00 |
606 | Account created for Emma Thomas | 2024-05-24 12:06:00 |
707 | Account created for Daniel Martinez | 2024-05-24 12:07:00 |
808 | Account created for Olivia Rodriguez | 2024-05-24 12:08:00 |
909 | Account created for Sophia Lee | 2024-05-24 12:09:00 |
123 | Account created for John Doe | 2024-05-24 12:10:00 |
456 | Account created for Jane Smith | 2024-05-24 12:11:00 |
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:
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.
Everything was clear?
Course Content
Advanced Techniques in SQL
Advanced Techniques in SQL
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
, andbalance
; UserLogs
table with columns:account_number
,action
,timestamp
etc. The combination ofaccount_number
andtimestamp
is a composite primary key of this relation.
account_number | account_holder | balance |
---|---|---|
789 | Alice Johnson | 3500.00 |
101 | Bob Brown | 4500.00 |
202 | Emily Jones | 6000.00 |
303 | Michael Davis | 800.00 |
404 | Sarah Wilson | 12000.00 |
505 | David Taylor | 300.00 |
606 | Emma Thomas | 7500.00 |
707 | Daniel Martinez | 1000.00 |
808 | Olivia Rodriguez | 900.00 |
909 | Sophia Lee | 2500.00 |
123 | John Doe | 5000.00 |
456 | Jane Smith | 2000.00 |
account_number | action | timestamp |
---|---|---|
789 | Account created for Alice Johnson | 2024-05-24 12:00:00 |
101 | Account created for Bob Brown | 2024-05-24 12:01:00 |
202 | Account created for Emily Jones | 2024-05-24 12:02:00 |
303 | Account created for Michael Davis | 2024-05-24 12:03:00 |
404 | Account created for Sarah Wilson | 2024-05-24 12:04:00 |
505 | Account created for David Taylor | 2024-05-24 12:05:00 |
606 | Account created for Emma Thomas | 2024-05-24 12:06:00 |
707 | Account created for Daniel Martinez | 2024-05-24 12:07:00 |
808 | Account created for Olivia Rodriguez | 2024-05-24 12:08:00 |
909 | Account created for Sophia Lee | 2024-05-24 12:09:00 |
123 | Account created for John Doe | 2024-05-24 12:10:00 |
456 | Account created for Jane Smith | 2024-05-24 12:11:00 |
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:
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.
Everything was clear?