Programming Assignment 4: Transactions Solved

Description

Overview

In this assignment you will write a program that allows a database user to enclose SQL statements into a transaction block. That is, you will implement the “all-or-nothing” property commonly seen in real-world database systems. This assignment assumes the basic metadata and data management have been implemented in the first three programming assignments.

System Design

• You will decide how to guarantee the atomicity of transactions o In demo lecture: locking

o As always, you are free (in fact, encouraged) to come up with your own design

• In the design document, you should clearly explain how your program implements the atomicity property of transactions. The easiest way to do this is to declare lock variables shared between processes. For example, you can create an empty file called “<table_name>_lock” to indicate an existing process is accessing <table_name>. In this case, we also assume the procedure of creating the empty file is atomic: At any time, only one process is able to create/delete the lock file (as an analogy, think of the P/V operations in an operating system). As a result, after a transaction starts, the system will first check the <table_name>_lock file before granting accesses to that specific table <table_name>. If there does exist such a lock file, then the system will reject/suspend the access request. Otherwise, everything works as before (e.g., updating tuples), except that the change will not be persisted to the disk until a “commit” is encountered.

Implementation

• The program should not use external database libraries, frameworks, or applications.

• Any programming language is acceptable, e.g., Python, Java, C/C++, Go

15. Just pick one(s) that you are most comfortable/proficient with

• 15. But keep in mind we will test your code in Linux with OS-level utilities (e.g., files)

      • So, probably not: C#, Object-C, JavaScript, Prolog…

• Functionalities:

• 15. SQL: Begin Transaction, Commit

Interface

• A similar but simpler interface than Sqlite3

• Same as homework #1: standard input, standard output (or files if you prefer)

15. This assignment the program will be tested using two (2) interactive terminals (to emulate two concurrent processes/clients).