Database transaction support refers to the need to handle a bundle of SQL transactions as a single action. The entire group of SQL instructions must all occur or have none occur at all. This is often referred to as ACID: Atomicity, Consistency, Isolation and Durability.
The requirement is to support actions such as the following banking account transaction example:
- Subtract $500.00 from the account of Fred Flinstone
- Add $500.00 to the account of Barney Rubble
ACID:
- Atomicity: The transaction must either all be carried out successfully, or none at all. If aborted, the previous operations are rolled back to their former state.
- Consistency: The database is always in a proper state and not in error.
- Isolation: Data being used for one transaction cannot be used by another transaction until the first transaction is complete.
- Durability: Once a transaction has completed correctly, its effects should remain, and not be reversible (unless another transaction or set of SQL commands purposefully directs a change).
CREATE TABLE accounts (IDpk serial PRIMARY KEY,
name char(20),
balance numeric(10,2));
INSERT INTO accounts (name,balance) VALUES ('Fred Flinstone',1000.00);
INSERT INTO accounts (name,balance) VALUES ('Wilma Flinstone',1000.00);
INSERT INTO accounts (name,balance) VALUES ('Barney Rubble',1000.00);
# select * from accounts;
idpk | name | balance
------+----------------------+---------
1 | Fred Flinstone | 1000.00
2 | Wilma Flinstone | 1000.00
3 | Barney Rubble | 1000.00
(3 rows)
Execute a transaction:
BEGIN; UPDATE accounts SET balance = balance - 500.00 WHERE name = 'Fred Flinstone'; UPDATE accounts SET balance = balance + 500.00 WHERE name = 'Barney Rubble'; COMMIT;
# select * from accounts;
idpk | name | balance
------+----------------------+---------
2 | Wilma Flinstone | 1000.00
1 | Fred Flinstone | 500.00
3 | Barney Rubble | 1500.00
(3 rows)
Transaction Errors:
The purpose of transactions is to "rollback" the database to a copacetic state if any one of the SQL statements fails in the collective transaction.
One must be aware of what the database regards as a failure.
BEGIN; UPDATE accounts SET balance = balance - 500.00 WHERE name = 'Fred Flinstone'; UPDATE accounts SET balance = balance + 500.00 WHERE name = 'Betty Rubble'; COMMIT;
Output:
=# BEGIN; BEGIN =# UPDATE accounts SET balance = balance - 500.00 WHERE name = 'Fred Flinstone'; UPDATE 1 =# UPDATE accounts SET balance = balance + 500.00 WHERE name = 'Wilma Rubble'; UPDATE 0 =# COMMIT; COMMITNote that there is no Betty in the database but the transaction still committed.
PostgreSQL even knows that there was no update: "UPDATE 0"
This is not considered a transaction failure by the database and thus does not roll back.
Verify:
=# select * from accounts;
idpk | name | balance
------+----------------------+---------
2 | Wilma Flinstone | 1000.00
3 | Barney Rubble | 1500.00
1 | Fred Flinstone | 0.00
(3 rows)
See transaction isolation to raise exceptions or to control conflicts with concurrent transations.
PostgreSQL transaction isolation
- CASCADE: Delete or update the row from the parent table and automatically delete or update the matching rows in the child table.
This will allow one delete of a record from parent table to automatically spawn a delete from a child table, using only one delete statement.
To avoid deleting the child record, one must first set the foreign key value of the parent record to NULL.
- ON DELETE CASCADE
- SET NULL: Delete or update the row from the parent table and set the foreign key column or columns in the child table to NULL. This is valid only if the foreign key columns do not have the NOT NULL qualifier.
- ON DELETE SET NULL
- NO ACTION: Do not delete record if there is a related foreign key value in the referenced table. One must delete the parent record first.
- RESTRICT: Rejects the delete or update operation for the parent table.
CREATE TABLE employee (
IDpk SERIAL PRIMARY KEY,
ssn CHAR(11) NOT NULL,
name CHAR(64),
phone CHAR(32),
dept_fk INTEGER NOT NULL
);
CREATE TABLE department (
IDpk SERIAL PRIMARY KEY,
dept_name CHAR(64)
);
ALTER TABLE employee ADD FOREIGN KEY(dept_fk) REFERENCES department (IDpk) ON DELETE CASCADE;
INSERT INTO department (dept_name) VALUES ('accounting');
INSERT INTO department (dept_name) VALUES ('engineering');
INSERT INTO employee (ssn, name, phone, dept_fk) VALUES ('123-45-6789','Matt','1-800-555-1212', 1);
INSERT INTO employee (ssn, name, phone, dept_fk) VALUES ('123-45-7890','Mark','1-801-555-1212', 2);
INSERT INTO employee (ssn, name, phone, dept_fk) VALUES ('123-45-8901','Luke','1-802-555-1212', 1);
INSERT INTO employee (ssn, name, phone, dept_fk) VALUES ('123-45-9012','John','1-803-555-1212', 2);
Verify:
=# SELECT * FROM employee;
idpk | ssn | name | phone | dept_fk
------+-------------+-------------------------+----------------------------------+---------
1 | 123-45-6789 | Matt | 1-800-555-1212 | 1
2 | 123-45-7890 | Mark | 1-801-555-1212 | 2
3 | 123-45-8901 | Luke | 1-802-555-1212 | 1
4 | 123-45-9012 | John | 1-803-555-1212 | 2
(4 rows)
=# SELECT * FROM department;
idpk | dept_name
------+------------------------------------
1 | accounting
2 | engineering
(2 rows)
Now we demonstrate the cascading delete:
=# DELETE FROM department WHERE dept_name='engineering';
DELETE 1
=# SELECT * FROM employee;
idpk | ssn | name | phone | dept_fk
------+-------------+-------------------------+----------------------------------+---------
1 | 123-45-6789 | Matt | 1-800-555-1212 | 1
3 | 123-45-8901 | Luke | 1-802-555-1212 | 1
(2 rows)
What does this mean? If the "engineering" department record is removed, then the employees in the group are fired and removed from the employee table. The other employees are retained.
Books:
 |
PostgreSQL
by Korry Douglas, Susan Douglas ISBN #0735712573, New Riders
|
|
 |
PostgreSQL Essential Reference
by Barry Stinson ISBN #0735711216, New Riders
|
|
 |
PostgreSQL: Developer's Handbook
by Ewald Geschwinde, Hans-Juergen Schoenig, Hans-Jurgen Schonig ISBN #0672322609, SAMS
|
|
 |
Practical PostgreSQL
John C. Worsley, Joshua D. Drake ISBN #1565928466, O'Reilly
|
|
 |
Beginning Databases with PostgreSQL
by Richard Stones, Neil Matthew ISBN #1861005156, Wrox Press Inc
|
|
