/*********************************************************************** * Falls die Datenbank neu aufgebaut werden soll, muessen die Tabellen * zunaechst geloescht werden. Dies ist natuerlich nur dann moeglich, * wenn die Tabellen vorher schon mal erzeugt wurden. * http://www.postgresql.org/docs/8.1/interactive/sql-createtable.html * http://www.faqs.org/docs/ppbook/x13546.htm * http://www.postgresql.at/de/docs/sql-createtable.html ***********************************************************************/ START TRANSACTION; DROP TABLE IF EXISTS a CASCADE; DROP TABLE IF EXISTS b CASCADE; COMMIT WORK; /*********************************************************************** * Datenbankschema ***********************************************************************/ START TRANSACTION; CREATE TABLE a ( id INTEGER NOT NULL, b INTEGER NOT NULL, CONSTRAINT p_a PRIMARY KEY (id) ); CREATE TABLE b ( id INTEGER NOT NULL, a INTEGER NOT NULL, CONSTRAINT p_b PRIMARY KEY (id), CONSTRAINT f_b_a FOREIGN KEY (a) REFERENCES a(id) DEFERRABLE INITIALLY DEFERRED ); /* Kann erst hier definiert werden, aber nicht direkt in TABLE a. */ ALTER TABLE a ADD CONSTRAINT f_a_b FOREIGN KEY (b) REFERENCES b(id) DEFERRABLE INITIALLY DEFERRED; COMMIT WORK; /*********************************************************************** * Daten ***********************************************************************/ /* Funktioniert nicht, wenn Auto-Commit je Befehl aktiv! */ /* INSERT INTO a VALUES (1,2); INSERT INTO b VALUES (2,1); */ /* Funktioniert nicht! */ /* START TRANSACTION; INSERT INTO a VALUES (1,2); COMMIT WORK; START TRANSACTION; INSERT INTO b VALUES (2,1); COMMIT WORK; */ /* Funktioniert! */ START TRANSACTION; INSERT INTO a VALUES (1,2); INSERT INTO b VALUES (2,1); COMMIT WORK; /* Funktioniert! */ START TRANSACTION; DELETE FROM a; DELETE FROM b; COMMIT WORK; /* Funktioniert nicht! */ /* SET CONSTRAINTS f_a_b IMMEDIATE; START TRANSACTION; INSERT INTO a VALUES (1,2); INSERT INTO b VALUES (2,1); COMMIT WORK; */ /* Analog mit: SET CONSTRAINTS f_a_b, f_b_a IMMEDIATE; SET CONSTRAINTS ALL IMMEDIATE; */ /* Funktioniert! */ SET CONSTRAINTS ALL DEFERRED; START TRANSACTION; INSERT INTO a VALUES (1,2); INSERT INTO b VALUES (2,1); COMMIT WORK; /*********************************************************************** * Händler-Datenbank ***********************************************************************/ START TRANSACTION ISOLATION LEVEL SERIALIZABLE; -- Hier wäre ein Phantom-Read von MAX(h_id) fatal. INSERT INTO haendler(h_id, h_name, h_ortschaft) VALUES ((SELECT MAX(h_id)+1 FROM haendler), 'Maier 1', 'Königsbrunn'), ((SELECT MAX(h_id)+2 FROM haendler), 'Maier zwo', 'Bobingen') ; INSERT INTO liefert(h_id, w_id, l_preis) VALUES ((SELECT MAX(h_id)-1 FROM haendler), (SELECT w_id FROM ware WHERE w_typ = 'Sonstiges' AND w_bezeichnung = 'Eieruhr' ), 6.00 ), ((SELECT MAX(h_id) FROM haendler), (SELECT w_id FROM ware WHERE w_typ = 'Sonstiges' AND w_bezeichnung = 'Eieruhr' ), 5.50 ) ; COMMIT WORK; SELECT * FROM haendler JOIN liefert USING (h_id) JOIN ware USING (w_id) ; /*********************************************************************** * Beispiele für ISOLATION LEVEL ***********************************************************************/ SERIALIZABLE: obiges Beispiel REPEATABLE READS: Bank-Transaktionen (Kontenbewegungen) READ COMMITED (Postgres-Stadard) So gut wie REPEATABLE READS, wenn keine multiplen Reads vorkommen. Locks können vor Transaktionsende freigegeben werden. READ UNCOMMITTED Statistische Auswertungen (z.B. aktuelle Zahl der Corona-Inifizierten)