67-272 Examples of Database Transactions

transaction_examples.sql

-- Using Prof. H's superhero_transact database
-- the pg_dump file for this database at: https://gist.github.com/profh/a243685482ac0d8f3244

SELECT * FROM heroes;

BEGIN;
UPDATE heroes SET name = 'The Tickster' WHERE hero_id = 14;
SELECT * FROM heroes;
UPDATE heroes SET age = 42;
-- foo! I did't mean to change age of everyone to 42... 
-- could just ROLLBACK right now and undo it, but will wait for now...
SELECT * FROM heroes;
-- verify that it really happened
INSERT INTO victories (hero_id, major, for_month) VALUES (18, 1, current_date);
-- error; there is no hero with id=18

COMMIT;  -- rolls back because couldn't execute the complete block of SQL

SELECT * FROM heroes;
-- verify that the transaction rolled back

-- ==================
-- COMPETING TRANSACTIONS & LOCKING DEMO 

	-- in connection #1
	BEGIN;
	UPDATE heroes SET age = 48 WHERE hero_id = 1;
	SELECT * FROM heroes;
	
	-- in connection #2
	BEGIN;
	UPDATE heroes SET age = 42 WHERE hero_id = 14;
	SELECT * FROM heroes;
	
	-- introduce the deadlock
	-- in connection #1
	UPDATE heroes SET age = 43 WHERE hero_id = 14;
	SELECT * FROM heroes;
	
	-- in connection #2
	UPDATE heroes SET age = 52 WHERE hero_id = 1;
	-- deadlock is detected and broken by postgres
	
	-- in connection #1
	SELECT * FROM heroes;
	
	-- in connection #2
	SELECT * FROM heroes; -- won't do anything until transaction finished
	COMMIT; -- will rollback automatically
	
	-- in connection #1
	SELECT * FROM heroes;
	LOCK TABLE heroes IN ACCESS EXCLUSIVE MODE;
	UPDATE heroes SET age = 44 WHERE hero_id = 14;
	
	-- in connection #2
	SELECT * FROM heroes;
	
	-- in connection #1
	COMMIT;