CSCI 305: Introduction to Database Systems

Homework 3: Transaction Management

Activities on Transaction Management

You are to complete a collection of exercises to review some of the concepts we have learned on the topic of transaction management.

  1. Define the following terms.
    1. Transaction
    2. Atomicity as in ACID
    3. Consistency as in ACID
    4. Isolation as in ACID
    5. Durability as in ACID
    6. Serial schedule
    7. Equivalent schedules
    8. Serializable schedule
  2. Explain how WAL (Write-Ahead-Logging) works.
  3. Explain how S2PL (Strict Two-Phase Locking protocol) works.
  4. Explain how 2PL (Two-Phase Locking protocol) works.
  5. Consider the following (incomplete) schedule T:

    T1:R(x), T1:R(y), T1:W(x), T2:R(y), T3:W(y), T1:W(x), T2:R(y).

    1. Construct its serial schedule S.
    2. Identify any of the conflicts with a proper name (WW, WR, RW) in schedule T.
    3. Draw the serializability graph for schedule T, assuming all three transactions eventually commit.
  6. This and next a few exercises involve the database we saw in our first review. Down load the database if you'd like. You can certainly use the one you created last time. We will concentrate on following two relations.

    Product(maker, model, type)
    PC(model, speed, ram, hd, price)

    Sketch the following programs and try them out on the database if you can. Do not forget to issue BEGIN TRANSACTION, COMMIT, and ROLLBACK statements at the proper times and to tell the system your transactions are read-only if they are.
    1. Given a speed and amount of RAM (as arguments of the function), look up the PC's with that speed and RAM, printing the model number and price of each.
    2. Given a model number, delete the tuple for that model from both PC and Product.
    3. Given a model number, decrease the price of that model PC by $100.
    4. Given a maker, model number, processor speed, RAM size, hard-disk size, and price, check that there is no product with that model. If there is such a model, print an error message for the user. If no such model existed in the database, enter the information about that model into the PC and Product tables. You might want to try to program this in Python using the fetchall() function for the cursor class to check the return result. See the reference at the end for some examples.
  7. For each of the programs in Exercise 6., discuss the atomicity problems, if any, that could occur should the system crash in the middle of an execution of the program.
  8. In Homework 2, we used a Books file to create a database. Here is a revised copy of that Books CSV file. This copy has a few duplicate entries (the entire row is duplicated). Write a Python program to import this CSV file into a database. When a duplicate is detected, skip it and move onto the next entry. This example from Python Sqlite3 library gives a good example of how this can be done.

References

  1. Example of using cursor to examine the return result of a query. https://stackoverflow.com/questions/2440147/how-to-check-the-existence-of-a-row-in-sqlite-with-python. Accessed 2018-03-08.
  2. Transaction control in sqlite3 http://www.sqlitetutorial.net/sqlite-transaction/. Accessed 2018-03-08.
  3. Database rollback in Python https://docs.python.org/3/library/sqlite3.html. Accessed 2018-03-08.
  4. Example of transaction commitment http://tutlane.com/tutorial/sqlite/sqlite-transactions-begin-commit-rollback. Accessed 2018-03-08.
  5. Sqlite rollback tutorial https://www.tutorialspoint.com/sqlite/sqlite_transactions.htm. Accessed 2018-03-08