HW#4 --- last modified May 03 2023 15:47:05.

Solution set.

Due date: Apr 26

Files to be submitted:
  Hw4.zip

Purpose: To gain experience with various kinds of recovery logs, and to understand notions of serializability.

Related Course Outcomes:

The main course outcomes covered by this assignment are:

CLO3 -- Create a simple query transaction in a modern DBMS system.

CLO5 -- Know database recovery techniques

CLO6 -- Be able to use isolation levels for concurrency control in a popular DBMS

Specification:

This homework consists of two experiments, the second of which requiring some coding. For the first experiment I want you to investigate the journals produced during a sqlite transaction. Some description of these formats is given on Database File Format page, in my own testing the information there did not seem to be completely accurate. As you do these experiments I want you to keep a transcript of what you typed and the sqlite3 shell's response. Put these as part of an Experiments.pdf file that you submit. First, run the sqlite3 shell and create a table foo with a single integer column. Insert rows for the numbers 1 through 50 into this tables. Do a PRAGMA journal_mode=delete; just to make sure you are in the default undo logging setting. Make a copy of your database DB1.sqlite. Then do a BEGIN TRANSACTION. Do a delete from foo; to delete all the rows you just inserted. Make a second copy of your database DB2.sqlite. Include the database file you have at this point and the -journal file that was created after the BEGIN TRANSACTION into your Hw4.zip. Look at the journal file in the hex editor of your choice. What page numbers are stored in the journal file? Can you identify a section of DB1.sqlite which is identical to the journal file that might correspond to these page numbers? Just include the first few bytes in hex in your write-up for these correspondences. If you do a couple more inserts into the database, does the journal file change? Why or why not? If you do a END TRANSACTION what happens to the log record? Redo the experiment but now do a ROLLBACK, what happens to the journal file? Repeat from the start these experiments, but doing a PRAGMA journal_mode=WAL; rather than PRAGMA journal_mode=delete; and calling the databases you save DB3.sqlite and DB4.sqlite rather than DB1.sqlite and DB2.sqlite. When you repeat the experiment this time, the logging will be at the row rather than the page level so adjust the page type questions you answered accordingly.

Next make a text file MakeDB.txt with the SQL operations for Mysql/MariaDB needed to make a database ISOLATION_TEST with a single table UNREPEATABLE having a single column data of integer type. Write a Java program IsolationTest.java which opens two separate connections to a Mysql/MariaDB to this database. Make sure to have connection info pulled out into a some changeable variables at the top of your program and have a README.txt file in your ZIP folding explaining what needs to be modified to connect to a different server. Set the isolation level for one connection to be read committed. Do this directly with an executeUpdate. Then do a sequence of interleaving operations between the two connections, printing to the screen what you are doing at each step. Your interleaving operations should be such that the connection which is in the read committed isolation level only performs reads, reads the same row thrice, but sees a different value each time. This is called an unrepeatable read. Next have your program close and reestablish the two connections. For the connection which only performed reads the first time, now set the isolation level to serializable. Have the program do the same interleaving now as the first time, printing the results to the screen. This time the subsequence of operations R_1(data), W_2(data), R_1(data), the W_2(data) needs to wait for a lock, so will timeout. If you ran these transactions in threads, so your transaction 1 could continue, it should see the same read value thrice (I am not asking you to do your code in threads). Roughly, the same code should work for Postgres, Oracle, DB2, each of which also has a SET TRANSACTION ISOLATION LEVEL command. Do some experiments with your program demonstrating what was just described, explaining why your program does what it does, and write these up in Experiments.pdf. Include transcripts of your program running.

Point Breakdown