============ Assignment 2 ============ Results from exercises in points 1-4 should be submitted not later than 09.11.2015 08:59 using the provided template. Exercise 5 is optional. Submission: Send an email to 'dbabgabe@cosy.sbg.ac.at' with a title '{postgresql|mysql|db2|sqlite|oracle}-a2' and a single attachment '{postgresql|mysql|db2|sqlite|oracle}-a2.pdf'. Some starting points to documentation: Oracle http://docs.oracle.com/database/121/CNCPT/consist.htm#CNCPT1364 MySQL http://dev.mysql.com/doc/refman/5.7/en/innodb-concepts.html DB2 https://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.admin.perf.doc/doc/c0054923.html PostgreSQL http://www.postgresql.org/docs/9.4/static/mvcc.html SQLite https://www.sqlite.org/lockingv3.html MS SQL Server https://msdn.microsoft.com/en-us/library/ms187101(v=sql.100).aspx https://technet.microsoft.com/en-us/library/ms189130(v=sql.105).aspx https://technet.microsoft.com/en-us/library/ms175519(v=sql.105).aspx https://technet.microsoft.com/en-us/library/jj856598(v=sql.110).aspx ------------------------------------------- 1. Concurrency control mechanisms (1 Point) ------------------------------------------- Describe briefly the concurrency control mechanisms supported by your DBMS. -------------------------- 2. Locking modes (1 Point) -------------------------- What lock modes are supported by your DBMS (shared, exclusive, others)? ----------------------------- 3. Resource locking (1 Point) ----------------------------- Which resource types can be locked in your DBMS (schema, tables, rows, others)? Show on simple examples how (if possible) to explicitly lock resources (for example, tables and tuples)? ------------------------ 4. Experiment (2 Points) ------------------------ We imitate flight seat reservation procedure. The seats availability is stored in the following table: flight_seats[id, availability] - There are 200 seats. - Id is a primary key with integer values from 1 to 200. - Availability is a boolean value indicating if the seat is available or not (initially true). The booking is done with the following steps: 1. Retrieve list of available seats (selection of available seat ids). 2. Give the customer some time (decision time is 1 second) to decide on a seat (a random seat id from the list returned in point 1). 3. Secure a seat (update the availability of the chosen seat to false). Perform the following experiment: - There are 200 customers trying to book a seat. Each customer books a seat in a separate transaction. - Imitate multiple travel agents that book the seats for the customers. The number of travel agents is a parameter k, where k in {1,2,4,6,8,10}. Perform the experiment for each k. k is the number of threads performing the booking transactions. One thread makes one reservation at a time. - Evaluate two versions of the booking transaction : a) as a single transaction including all three steps, and b) split into two smaller transactions: b1) retrieving available seats, b2) securing a seat. In case b), the decision time is not part of transactions but has to be considered. - Perform the experiment for two isolation levels: 'read committed' and 'serializable'. - Set explicitly row locking. If this is not possible, describe the locking mechanism. - Restart the transactions until they commit (all customers book a seat). Measure the following properties: - Total time required for all customers to book a seat (for each k, isolation level, and transaction version). Show the results on a line plot where x axis shows parameter k and y axis shows time. - Min, max, and avg number of times a customer had to try to book a seat until he got it (number of times a single transaction had to be restarted). Show the results in a table. Briefly describe the outcome and explain the differences between the transaction versions and isolation levels. Implement the experiment in any programming language or use JAVA: - experiment template (Database Tuning course): http://dbresearch.uni-salzburg.at/teaching/2014ws/dbt/account.zip - JDBC connection example (Datenbanken 1 course, slides 104-105 of lecture 5): http://dbresearch.uni-salzburg.at/teaching/2015ss/db1/db1_05-handout-1x1.pdf ------------------------------------- 5. [Optional] Viewing locks (1 Point) ------------------------------------- How to display currently granted and requested locks? Write an SQL query and explain the resulting table (attributes and values). The resulting table may have many attributes. In such a case choose several most interesting, for example relation name, lock type, process id, row id, etc. Show on a simple example (exercise 4 from Assignment 1 is a good starting candidate).