/cdn.vox-cdn.com/uploads/chorus_asset/file/24933461/236790_Apple_watch_9_Ultra_2_AKrales_0710.jpg)
Transactions
In this section, we will explore the concept of transactions and ACID properties—an important topic in database management to ensure data integrity and reliability. What is a Transaction? Imagine a database transaction as the action of withdrawing money from your bank account. You need to perform multiple actions, such as checking your balance, entering the amount to withdraw, and completing the withdrawal. You expect the entire process to run smoothly without errors. Similarly, a database transaction is a unit of work that consists of a collection of queries, which must be executed as a whole or not at all. This all-or-nothing principle ensures data integrity and reliability. Transactions are primarily used to change or modify data. Transaction Lifespan Use BEGIN to start a transaction. Once all operations are successfully performed, use COMMIT to finalize and save the changes to the database. If any errors occur during the transaction, use ROLLBACK to undo all changes that have not yet been saved to the database. Example: How a Transaction Works Scenario: Transferring money between bank accounts. Table: bank_accounts id balance 1 $1000 2 $200 Transaction Steps: User 1 wants to send $100 to User 2. BEGIN; -- Start Transaction SELECT balance FROM bank_accounts WHERE id = 1; -- Check User 1's balance (should be > $100) UPDATE bank_accounts SET balance = balance - 100 WHERE id = 1; -- Deduct $100 from User 1 UPDATE bank_accounts SET balance = balance + 100 WHERE id = 2; -- Add $100 to User 2 COMMIT; -- Finalize and save the transaction Conclusion This example demonstrates how transactions work in practice to ensure data integrity. By wrapping the operations in a transaction, we guarantee that the money transfer is completed entirely or not at all, preventing issues like incorrect balances if an error occurs midway. In the next section, we will explore the ACID properties, which further define how transactions ensure consistency, durability, and reliability in databases.
In this section, we will explore the concept of transactions and ACID properties—an important topic in database management to ensure data integrity and reliability.
What is a Transaction?
Imagine a database transaction as the action of withdrawing money from your bank account. You need to perform multiple actions, such as checking your balance, entering the amount to withdraw, and completing the withdrawal. You expect the entire process to run smoothly without errors.
Similarly, a database transaction is a unit of work that consists of a collection of queries, which must be executed as a whole or not at all. This all-or-nothing principle ensures data integrity and reliability. Transactions are primarily used to change or modify data.
Transaction Lifespan
- Use
BEGINto start a transaction. - Once all operations are successfully performed, use
COMMITto finalize and save the changes to the database. - If any errors occur during the transaction, use
ROLLBACKto undo all changes that have not yet been saved to the database.
Example: How a Transaction Works
Scenario: Transferring money between bank accounts.
Table: bank_accounts
| id | balance |
|---|---|
| 1 | $1000 |
| 2 | $200 |
Transaction Steps:
User 1 wants to send $100 to User 2.
BEGIN; -- Start Transaction
SELECT balance FROM bank_accounts WHERE id = 1; -- Check User 1's balance (should be > $100)
UPDATE bank_accounts SET balance = balance - 100 WHERE id = 1; -- Deduct $100 from User 1
UPDATE bank_accounts SET balance = balance + 100 WHERE id = 2; -- Add $100 to User 2
COMMIT; -- Finalize and save the transaction
Conclusion
This example demonstrates how transactions work in practice to ensure data integrity. By wrapping the operations in a transaction, we guarantee that the money transfer is completed entirely or not at all, preventing issues like incorrect balances if an error occurs midway.
In the next section, we will explore the ACID properties, which further define how transactions ensure consistency, durability, and reliability in databases.
