Hands-on 7: Databases

Intro to databases

This hands-on assignment will introduce you to databases. One of the benefits of using a database is that it provides transactions, which have isolation and atomicity (see chapter 9 for details). Transactions provide guarantees that an ordinary file system does not provide. The focus in this hands-on is on isolation.

For the purposes of this exercise, we will discuss a PostgreSQL database. The default isolation level, which specifies what type of serializability semantics are desired, is SERIALIZABLE. In PostgreSQL, this means that all schedules will be conflict-serializable.

PostgreSQL databases allow users (such as application developers) to organize data in tables. Each table consists of record called rows. Each row, in turn, consists of several attributes called columns.

In this hands-on, we use some basic SQL commands. We have explained the relevant commands in the assignment, but if you have any additional questions about SQL syntax, please post on Piazza.

Transactions

To deal with concurrent operations, many databases, including PostgreSQL, support transactions, which allow several statements to execute with atomicity and isolation. To execute statements as a single transaction, clients first issue a BEGIN command, then execute some SQL commands, and finally issue either a COMMIT command, which makes the transaction's changes permanent, or a ROLLBACK command, which reverts the changes from all of the commands in the transaction.

In this assignment, you will think about concurrent database queries that issue SQL statements over two different connections to a single database.

Questions

Now you're ready for this week's questions.

Like before, the questions are in a read-only google doc. Make sure to enter quesitons in the page indicated (please do not erase the question text) and upload them as a PDF to Gradescope. See more detailed instructions at the end of the first week's hands-on. If you are having Gradescope problems, please post a question on Piazza!