What is a database transaction?
Database transactions are sequences of operations treated as a single action. Their purpose is to ensure that the operations performed are consistent and secure, especially in systems that require high reliability, such as banking systems.
- Atomicity: Transactions are atomic, which means that all operations within the transaction must complete successfully. If any one fails, the entire change must be reversed.
How are transactions initiated and executed?
To initiate a transaction, we use the BEGIN
command. Upon completion of the necessary operations, it is paramount to secure the changes with COMMIT
. If the changes have to be undone because something went wrong, we use ROLLBACK
.
BEGIN;COMMIT;
What happens if something goes wrong?
If any of the operations in the transaction fails, the database will use ROLLBACK
to return to its previous state and revert all transactions performed up to that point.
How does Autocommit work in PGAdmin?
PGAdmin, by default, has Autocommit enabled, which means that each transaction is automatically saved. To handle transactions manually, you must deactivate this option.
- Disable Autocommit: This is crucial to avoid confusion and to handle transactions with full control.
How to handle transactions with multiple queries?
To execute multiple queries in a transaction block, BEGIN
and COMMIT
are used. This is essential for complex changes where all actions must succeed.
Example transaction with inserts
BEGIN;INSERT INTO station (name, address) VALUES ('Station Transaction', '123 Fake Street');INSERT INTO train (model, capacity) VALUES ('Model Transaction', 300);COMMIT;
What happens if an insert fails because of a duplicate key?
In case a query within a transaction fails, for example, when trying to insert an ID that already exists, the whole transaction will be rolled back.
BEGIN;INSERT INTO tren (model, capacity) VALUES ('Existing Model', 300); COMMIT;
When is the explicit use of ROLLBACK useful?
Although implicit ROLLBACK
is used automatically, there are cases where defining it explicitly is beneficial, such as when implementing custom business logic.
Practical example:
Imagine wanting to limit insertions to a maximum of three passengers for a specific flight. You could use a business condition that triggers explicit ROLLBACK
when trying to add a fourth passenger.
BEGIN;IF (SELECT COUNT(*) FROM passengers WHERE flight_id = 1) >= 3 THEN ROLLBACK;END IF;INSERT INTO passengers (name, flight_id) VALUES ('New Passenger', 1);COMMIT;
Transaction handling recommendations
- Plan your transactions: In any critical operation, make sure your queries are well designed to avoid failures.
- Test in a secure environment: Before implementing complex transactions in production, test in a development environment to avoid costly errors.
- Document and educate: Familiarize your team with best practices for secure and efficient transactions.
Proper transaction handling ensures database integrity and prevents inconsistencies that can be critical in sensitive applications. Keep learning, as practice and knowledge are key to effective database management.
Want to see more contributions, questions and answers from the community?