Labs 1 - ACID properties ======================== We use PostgreSQL for the following exercises. (1) Create a table 'numbers' with a single column 'n' of type integer. Insert three tuples: 1, 2, and 3. (2) Perform the following steps. 1) Start 'psql'. 2) Start transaction with 'BEGIN;'. 3) Delete a tuple with value 2. 4) Display all tuples from 'numbers' with SELECT. 5) Kill 'psql' process. 6) Start 'psql' again. 7) Display all tuples from 'numbers' with SELECT. Did we delete the tuple? Which ACID property do we experience? (3) Bring the table 'numbers' to the state from point (1). Perform the following steps. 1) Start 'psql'. 2) Start transaction with 'BEGIN;'. 3) Delete a tuple with value 2. 4) Display all tuples from 'numbers' with SELECT. 5) Commit the transaction with 'COMMIT;'. 6) Kill 'psql' process. 7) Start 'psql' again. 8) Display all tuples from 'numbers' with SELECT. Did we delete the tuple this time? Which ACID property do we experience? (4) Bring the table 'numbers' to the state from point (1). Open two terminal windows and start 'psql' in each of them. This will imitate two concurrent transactions, T1 and T2. Perform the following steps. Add a check constraint 'max_check' to table 'numbers' which evaluates if each number is smaller equal 10. What can be observed after lines 6 and 7? What happens after line 9? T1: T2: 1 BEGIN; 2 BEGIN; 3 SELECT * FROM numbers; 4 SELECT * FROM numbers; 5 UPDATE numbers set n=n+1; 6 SELECT * FROM numbers; 7 SELECT * FROM numbers; 8 UPDATE numbers set n=n+7; 9 COMMIT; 10 COMMIT; Which ACID properties do we experience?