-------------------------------------------------------------------------------- LAB 1 | ACID properties. -------------------------------------------------------------------------------- Throughout the labs we will use PostgreSQL as a reference DBMS [1]. Please install it locally for the best experience (see Point (2)). Use 'psql' to perform the exercises. -------------------------------------------------------------------------------- PRACTICE EXERCISES -------------------------------------------------------------------------------- (1) Get familiar with the terms: - transaction, - ACID properties, - transaction states, by attending the lecture and reading chapters 14.1, 14.2 from the textbook. (2) Download PostgreSQL and install it [2], unless you already have it. (3) Create a table 'numbers' with a single column 'n' of type integer. Insert three tuples: 1, 2, and 3. Create a file 'numbers-create.sql' with the respective statements for easier reseting the database to the original state. (4) Perform the following steps and give your answers to points (a)-(b). 1 Insert a new tuple with value 4. Use INSERT INTO statement. 2 Display all tuples of table numbers with SELECT statement. 3 Delete the tuple with value 4 using DELETE. 4 Display all tuples. Is the new tuple there? 5 Insert a new tuple with value 4. 6 Start a new transaction with 'BEGIN;'. 7 Display all tuples. Is the new tuple there? 8 Delete the tuple with value 4. 9 Display all tuples. Is the new tuple there? 10 Execute ROLLBACK; command. 11 Display all tuples. Is the new tuple there? (a) What are the differences between executing points 1 to 4 compared to points 5 to 11? (b) After step 8, can a concurrent transaction still see the deleted tuple? Why is that so? (5) Perform the following steps and give your answers to points (a)-(c). 1 Start PostgreSQL and 'psql' client. 2 Start a transaction with 'BEGIN;'. 3 Delete a tuple with value 2. 4 Display all tuples from 'numbers' with SELECT. 5 Kill 'postgres' process (for example: pgrep postgres | xargs kill -SIGKILL). 6 Start PostgreSQL and 'psql' again. 7 Display all tuples from 'numbers' with SELECT. (a) Did we delete the tuple? (b) Which ACID property do we experience? (c) What happens to the transaction after step 5? (6) Bring the table 'numbers' to the state from point (3). Perform the following steps and give your answers to points (a)-(d). 1 Start PostgreSQL and 'psql' client. 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 'postgres' process. 7 Start PostgreSQL and 'psql' again. 8 Display all tuples from 'numbers' with SELECT. (a) Did we delete the tuple this time? (b) What happened to the transaction after step 5? (c) Did killing the process influence our transaction? (d) Which ACID property do we experience? (7) Bring the table 'numbers' to the state from point (3). Open two terminal windows and start 'psql' in each of them. This will imitate two concurrent transactions, T1 and T2. Add a check constraint 'max_check' to table 'numbers' which evaluates if each number is smaller equal 10: ALTER TABLE numbers ADD CONSTRAINT max_check CHECK (n <= 10); Perform the following steps and give your answers to points (a)-(d). 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; (a) What can be observed after steps 6 and 7? (b) What happens after step 8? (c) What happens after step 9? (d) Which ACID properties do we experience? (8) FURTHER READING: - 'ACID Properties', 'Transaction' - Encyclopedia of Database Systems [4]. - ISO 9075-2:2011 'SQL Transactions - General description of SQL transactions'. - Chapter 3.4. 'Transactions' - PostgreSQL documentation [3]. (9) CHECK-LIST: - I can explain the term 'transaction' and give a simple example. - I can list ACID properties and explain why are they important. - I can implement simple transactions in SQL (PostgreSQL) using the keywords BEGIN, COMMIT, ROLLBACK. -------------------------------------------------------------------------------- HOMEWORK -------------------------------------------------------------------------------- DEADLINE: 27.10.2017, 21:59 POINTS: 1 Create a table 'accounts' with two columns: 'aid' and 'balance', both of type integer. Insert two tuples: (1, 1000) and (2, 2000). Execute the following transactions (imitating two concurrent money withdrawals from the same account) and give answers to points (a)-(h). Submit your answers through the submission system as a single TXT file. Before updating the balance with the new value, the transactions verify if there is enough money on the account. After withdrawal, the new amount is also verified. These tasks are imitated by the SELECT statements in steps 3, 4, 7, and 9. T1 (withdraw 200): T2 (withdraw 1000): 1 BEGIN; 2 BEGIN; 3 SELECT * FROM accounts WHERE aid = 1; 4 SELECT * FROM accounts WHERE aid = 1; 5 UPDATE accounts SET balance = 800 WHERE aid = 1; 6 UPDATE accounts SET balance = 0 WHERE aid = 1; 7 SELECT * FROM accounts WHERE aid = 1; 8 COMMIT; 9 SELECT * FROM accounts WHERE aid = 1; 10 COMMIT; (a) What are the values seen by T1 in step 3 and T2 in step 4? (b) After step 10, is the update made by T1 in step 5 reflected on the database or is it lost? (c) What happens after step 6? (d) What are the values seen by T1 in step 7 and T2 in step 9? Is the balance of the account correct? (e) What happens if T1 rolls back in step 7? (f) After swapping steps 4 and 5, does T2 see the value updated by T1? Why is that so? (g) What happens if we partially modify the UPDATE statements with 'SET balance = balance - X' where X = 200 for T1 and X = 1000 for T2? Is the balance correct after the transactions commit? (h) How to fix the scenario to disallow a negative balance? -------------------------------------------------------------------------------- REFERENCES -------------------------------------------------------------------------------- [1] https://www.postgresql.org/ [2] https://www.postgresql.org/download/ [3] https://www.postgresql.org/docs/9.5/static/tutorial-transactions.html [4] (Access from university network.) https://link.springer.com/referencework/10.1007%2F978-0-387-39940-9 [5] (Explicit Locking) https://www.postgresql.org/docs/current/static/explicit-locking.html