-------------------------------------------------------------------------------- LAB 2 | Transaction isolation levels, phenomena. -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- PRACTICE EXERCISES -------------------------------------------------------------------------------- (1) Get familiar with the terms: - isolation levels, - dirty read, nonrepeatable read, phantom read, by attending the lecture, reading chapters 14.8 and 14.10 from the textbook, reading PosgreSQL documentation [1]. (2) Investigate isolation levels. The ISO standard specifies certain isolation levels which should be implemented in a DBMS. Answer the following questions (use [1] to find the answers): (a) Are all isolation levels specified in the ISO standard implemented in PostgreSQL? What are the differences? (b) May all phenomena (listed in the ISO standard) happen in PostgreSQL? (c) What is the default isolation level in PostgreSQL? (d) How to manually specify a certain isolation level in PostgreSQL? -------------------------------------------------------------------------------- In the following exercises we will have a closer look at the three undesired phenomena defined by the ISO standard. We will try to trigger them with minimal examples. Consider 'accounts' relation from lab 1. -------------------------------------------------------------------------------- (3) Propose a scenario with two transactions where one tries to make a 'dirty read'. Test the scenario in PostgreSQL. Does the dirty read take place? (4) Execute the following transactions and give your answers to points (a)-(c). T1: T2: 1 BEGIN; 2 SELECT * FROM accounts WHERE balance > 1000; 3 BEGIN; 4 UPDATE accounts SET balance = balance - 500 WHERE aid = 2; UPDATE accounts SET balance = balance + 500 WHERE aid = 1; 5 SELECT * FROM accounts; 6 COMMIT; 7 SELECT * FROM accounts WHERE balance > 1000; 8 COMMIT; (a) Explain the behaviour of T1 and T2. (b) Which of the phenomena can be observed? (c) What happens if we set the isolation level of T1 to REPEATABLE READ? Does it fix the scenario? (5) Execute the following transactions and give your answers to points (a)-(d). T1: T2: T3: 1 BEGIN; 2 BEGIN; 3 INSERT INTO accounts VALUES (3, 3000); 4 COMMIT; 5 SELECT * FROM accounts WHERE balance > 1000; 6 BEGIN; 7 INSERT INTO accounts VALUES (4, 4000); 8 COMMIT; 9 SELECT * FROM accounts WHERE balance > 1000; 10 COMMIT; (a) Explain the behaviour of T1, T2 and T3. (b) Which of the phenomena can be observed? (c) According to ISO standard, is this phenomena possible when we change the isolation level of T1 to REPEATABLE READ? (d) Verify (c) in PostgreSQL by setting the isolation level of T1 to REPEATABLE READ. (6) FURTHER READING: - 'Transaction management', 'SQL Isolation Levels' from Encyclopedia of Database Systems [2]. - ISO 9075-2:2011 'SQL Transactions - Isolation levels of SQL transactions'. - Chapter 13.2 'Transaction Isolation' from PostgreSQL documentation [1]. (7) CHECK-LIST: - I can explain the purpose of isolation levels and list them all. - I can explain different phenomena on simple examples and associate them with isolation levels in which they may happen. - I can analyse concurrent transactions and identify operations that may cause phenomena or transaction abort. -------------------------------------------------------------------------------- HOMEWORK -------------------------------------------------------------------------------- DEADLINE: 17.11.2017, 21:59 Create a table 'accounts' with two columns: 'aid' and 'balance', both of type integer. Insert three tuples: (1, 1000), (2, 2000) and (3, 3000). Execute the following transactions and give your answers to points (a)-(g). Submit your answers through the submission system as a single TXT file. (a) Are the changes made by T2 in steps 5 to 7 visible in T1? Why? (b) What happens when T1 performs steps 11 and 12? What's the difference? (c) What happens after step 14? Can T1 continue? If T1 cannot continue, explain why. (d) What happens after step 14 if the isolation level of T1 is READ COMMITTED? Why is that so? (e) Are the result sets in step 13 and step 20 equal? Explain why. (f) Remove step 12 and execute the transactions again. What happens when T1 performs step 17? (g) Remove steps 16 and 17 (additionally to step 12), and execute the transactions again. Describe the changes visible in T1 in step 18 and these visible in T3 in step 20. Changes made by which transactions are visible? T1: T2: 1 BEGIN; 2 BEGIN; 3 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; 4 SELECT * FROM accounts; 5 UPDATE accounts SET balance = balance + 100 WHERE aid = 1; 6 INSERT INTO accounts VALUES (4, 4000); 7 DELETE FROM accounts WHERE aid = 2; 8 SELECT * FROM accounts; 9 SELECT * FROM accounts; 10 INSERT INTO accounts VALUES (5, 5000); 11 UPDATE accounts SET balance = balance + 300 WHERE aid = 3; 12 UPDATE accounts SET balance = balance + 100 WHERE aid = 1; +-----------------------------------------------------------+ 13 |T3: BEGIN; SELECT * FROM accounts; | +-----------------------------------------------------------+ 14 COMMIT; +-----------------------------------------------------------+ 15 |T4: SELECT * FROM accounts; | +-----------------------------------------------------------+ 16 SELECT * FROM accounts; 17 UPDATE accounts SET balance = balance + 200 WHERE aid = 2; 18 SELECT * FROM accounts; 19 COMMIT; +-----------------------------------------------------------+ 20 |T3: SELECT * FROM accounts; COMMIT; | +-----------------------------------------------------------+ -------------------------------------------------------------------------------- REFERENCES -------------------------------------------------------------------------------- [1] https://www.postgresql.org/docs/current/static/transaction-iso.html [2] (Access from university network.) https://link.springer.com/referencework/10.1007%2F978-0-387-39940-9