Lab_10__Transactions

pdf

School

University of Massachusetts, Amherst *

*We aren’t endorsed by this school

Course

345

Subject

Computer Science

Date

Feb 20, 2024

Type

pdf

Pages

Uploaded by AgentPheasantPerson1042

Lab 10: Transactions Preface: Transactions A transaction is a series of read and write actions. It is important to manage concurrent operations- when more than one user is accessing the database- so that users can read and write data without problems. Four Examples of problems with concurrent transactions: 1

Overview of the ACID properties: A system enforcing the ACID proper-es ensures that schedules are serializable. However, serializability may be too strict a requirement for some applica-ons. For example, in a search for products from an online vendor, it may not be that crucial to reflect all recently applied updates to listed products. Atomicity relates to the commit/rollback commands. Isolation relates to visibility of updates from concurrent transactions. We won’t discuss much Durability and Consistency in this lab. Isolation levels provide a way to manage concurrent transactions. 2

Step 1: Multiple transactions NOTE: Feedback for questions Q4-Q13 will be given after the due date. You will only see feedback for Questions 1, 2, and 3 on Gradescope until after the due date. You will be simulating 2 separate transactions on your machine. You will do that by starting psql in two separate terminal windows. We will be denoting the windows with the numbers 1 and 2 in these instructions, to make it clear which commands you enter in which window. 1 2 Create a new database called “projects_databse” and run the script projects_database.sql . Connect to projects in both windows, by running \c projects_database in both. Make sure you have two psql instances running in the two terminal windows, both connected to the projects_database database. Keep track of which window you designate as “1”, and which you designate as “2”. Subsequent instructions ask you to enter different commands in the two windows, in particular order. 3

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

Step 2: When are changes visible? Q1: Start a transaction in window 1 : start transaction; Now check the contents of the product table in window 2 : select * from product; Now initiate a price update in table product in window 1 : update product set price = price+10 where pname='Gizmo'; Now, check again the contents of the product table in window 2 : select * from product; Did anything change? Enter the price of the Gizmo product in window 2 as Q1.sql. Use the format select number ; where number is the price of the Gizmo product. Q2: Now, check the contents of the product table in window 1 : select * from product; What do you observe? Is the price change visible? Is it visible in both windows? Explain what you observe. Enter the price of the Gizmo product in window 1 as Q2.sql. Use the format select number ; where number is the price of the Gizmo product. 4

Q3: Now, return to window 1 and commit the transaction: commit; Check again the contents of the product table in window 2 : select * from product; Explain your observations. Enter the price of the Gizmo product in window 2 as Q3.sql. Use the format select number ; where number is the price of the Gizmo product. Explanation (think about it yourself before you read this): Window 1 and window 2 are two different clients submitting transactions concurrently. When a transaction updates the database, the updates are only visible within that transaction until they are committed. Then, they become visible to everyone. This property is called ISOLATION. 5

Step 3: Competing changes Q4: Start a transaction in window 1 : start transaction; And another in window 2 : start transaction; Transaction 1 updates John’s phone number in window 1 : update employees set phone = '1234' where name= 'John'; Transaction 2 updates Cecilia’s phone number in window 2 : update employees set phone = NULL where name= 'Cecilia'; It then proceeds to also update John’s phone number ( window 2 ): update employees set phone = '9876' where name= 'John'; When postgres completes an update, it gives you a message, like UPDATE 1 . If you see no message and no prompt, the system is waiting for something. This is normal. Reason about what is happening and follow the instructions. What happened? Were the transactions able to perform these changes? Select the correct option to describe what output you get in window 2 after executing the last query: 1) UPDATE 1 2) UPDATE 2 3) Nothing. The query completes. 4) Nothing. The query hangs. Submit your answer as Q4.sql using the format: select ans; where ans is 1, 2, 3, or 4: 6

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

Q5: Now, go ahead and commit transaction 1 in window 1 : commit; Did the commit affect transaction 2? What do you think happened? What do you see now in window 2? 1) UPDATE 1 2) UPDATE 2 3) Nothing. The query completes. 4) Nothing. The query hangs. Submit your answer as Q5.sql using the format: select ans; where ans is 1, 2, 3, or 4: Q6: Execute the following query on both windows: select * from Employees; What are the contents of the Employees table in each window? 1) Both clients see the original value of the table before the updates were executed. 2) Each client returns the value of the last update it executed. 3) Both clients see the value updated by client 1. 4) Both clients see the value updated by client 2. Submit your answer as Q6.sql using the format: select ans; where ans is 1, 2, 3, or 4: Explanation (think about it yourself before you read this): Before a transaction updates a row, it takes a lock that prevents other concurrent transactions from updating the same row. When a transaction tries to update a row that is locked, it waits for the transaction who currently holds the lock to complete. Then, when the lock is freed by the other transaction, it can go ahead and complete the update. 7

Q7: Now, let’s rollback transaction 2 (window 2): rollback; What are the contents of the Employees table in window 2? What is the phone number of John? 1) The value updated by the first transaction 2) The value updated by the second transaction 3) The original value before any transaction was executed 4) None of the above. Submit your answer as Q7.sql using the format: select ans; where ans is 1, 2, 3, or 4: Explanation (think about it yourself before you read this): Transactions have an all-or-nothing property called ATOMICITY. A transaction can either commit or abort. If it commits, ALL its updates need to be applied. If it aborts, NONE of its updates are applied. The rollback command aborts the current transaction. Step 4: Deadlock Q8: Start a transaction in window 1 : start transaction; And another in window 2 : start transaction; Transaction 1 updates John’s phone number in window 1 : update employees set phone = '0000' where name= 'John'; Transaction 2 updates Cecilia’s and John’s phone numbers in window 2 : 8

update employees set phone = NULL where name= 'Cecilia'; update employees set phone = NULL where name= 'John'; We reached this same state in step 3, and transaction 2 is waiting for transaction 1. But now, transaction 1 proceeds to update Cecilia’s phone number ( window 1 ): update employees set phone = '5555555' where name= 'Cecilia'; What happened? What do you see in window 1? 1) UPDATE 1 2) Nothing. The query hangs. 3) Nothing. The query completes. 4) Error: deadlock detected. Submit your answer as Q8.sql using the format: select ans; where ans is 1, 2, 3, or 4: Q9: Now try to commit both transactions. What happened? Why? Which changes are reflected in the database? 1) Both transactions are aborted. 2) The first transaction (window 1) is aborted, the second (window 2) completes. 3) The first transaction (window 1) commits, the second (window 2) aborts. 4) Both transactions are committed. Submit your answer as Q9.sql using the format: select ans; where ans is 1, 2, 3, or 4: 9

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

Explanation (think about it yourself before you read this): A transaction that acquires a lock releases it when it commits or aborts. Transactions can acquire some locks, hold them, and then try to get more locks. We have seen before that one transaction may have to wait for another transaction to complete in order to acquire a lock. When two transactions wait for each other, we get a deadlock. The database breaks the deadlock by aborting one of the two transactions. This frees up the contended locks, allowing the other transaction to make progress. Before moving on to step 5, rollback the unfinished transaction. Otherwise, the transaction in window 2 from step 5 is never committed, causing a warning when you start a new transaction in step 6. Step 5: Reading the same value multiple times. Q10: Start a transaction in window 1 : start transaction; And another in window 2 : start transaction; Transaction 1 updates John’s phone number in window 1 : update employees set phone = '555-3245' where name= 'John'; Transaction 2 reads the contents of the Employees table in window 2 : select * from employees ; Now, transaction 1 commits, in window 1 : commit; Transaction 2 again reads the contents of the Employees table in window 2 : select * from employees ; 10

Transaction 2 reads the same table twice. What values does it read? 1) Both reads return the original values of the Employees table before transaction 1 started. 2) The first read returns the original values before transaction 1 started, the second the values updated by transaction 1. 3) The first read returns the values updated by transaction 1, the second the original values before transaction 1 started. 4) Both reads return the value updated by transaction 1. Submit your answer as Q10.sql using the format: select ans; where ans is 1, 2, 3, or 4: Commit or rollback transaction 2 before proceeding to the next step. What happened? Check the postgres reference on isolation levels here: https://www.postgresql.org/docs/current/static/transaction-iso.html#MVCC ISOLEVEL-TABLE What isolation level do you think your transactions are using? Verify that you identified the correct isolation level using the following command: SELECT current_setting('transaction_isolation'); Why do you think this isolation level is chosen as the default? Q11: What isolation level is used by default by postgres? 1) Read uncommitted 2) Read committed 3) Repeatable read 4) Serializable Submit your answer as Q11.sql using the format: select ans; where ans is 1, 2, 3, or 4: Explanation (think about it yourself before you read this): Serializability is the most intuitive isolation level. Isolation levels that relax serializability introduce anomalies that are not expected, for example when a transaction reads the same table twice and gets different results. Why use those isolation 11

levels? Because transactions acquire locks in fewer situations and run faster as a result. Step 6: Changing isolation levels Q12: Start a transaction in window 1 and set the isolation level to repeatable read: start transaction; set transaction isolation level repeatable read; And another in window 2 and set the isolation level to repeatable read: start transaction; set transaction isolation level repeatable read; Let’s now repeat updates similar to step 5. Transaction 1 updates John’s phone number in window 1 : update employees set phone = '123-4567' where name= 'John'; Transaction 2 reads the contents of the Employees table in window 2 : select * from employees; Now, transaction 1 commits, in window 1 : commit; Transaction 2 again reads the contents of the Employees table in window 2 : select * from employees ; How are things different this time? Transaction 2 reads the same table twice. What values does it read? 1) Both reads return the original values of the Employees table before transaction 1 started. 2) The first read returns the original values before transaction 1 started, the second the values updated by transaction 1. 3) The first read returns the values updated by transaction 1, the second the 12

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

original values before transaction 1 started. 4) Both reads return the value updated by transaction 1. Submit your answer as Q12.sql using the format: select ans; where ans is 1, 2, 3, or 4: Q13: Commit transaction 2 in window 2 commit; and then read the employee table again (this query is effectively a transaction in itself): select * from employees ; What data do you see now? What values does the transaction read? 1) The original values of the Employees table before transaction 1 started. 2) The values updated by transaction 1. 3) The same values read by the previous transaction executed by window 2. Submit your answer as Q4.sql using the format: select ans; where ans is 1, 2, or 3: 13

Lab_10__Transactions

Related Documents