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.
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.
Thanks for your feedback!