CSE 344 Section 7

HW6 & Locking

HW6

What is Flights App?

• A client-server application

SQL Injection

• Vulnerable web applications can expose capabilities to the user that are undesired
• Dropping tables, inserting values, deleting values, gaining access when it should be rejected, etc.
• Using PreparedStatement objects will help prevent SQL Injection
• A PreparedStatement pre-compiles the SQL query such that inputs that would result in SQL Injection when parsed can no longer do so
• SQL Injection Demo

PreparedStatements

How they work:

• Your SQL Connection Object parses the String passed to prepareStatement() and pre-compiles it to a parameterized SQL query
• The “?” in the query are interpreted as parameters that can be inserted via calls on the PreparedStatement Object

​

String rawQuery = “SELECT * FROM Flights WHERE origin_city = ? AND day_of_month = ?”;

PreparedStatement ps = conn.prepareStatement(rawQuery); // Pre-compiles the query into a PreparedStatement Object

​

ps.clearParameters(); // Clears parameters from previous use

ps.setString(1, originCity); // Sets the first parameter (the first “?”) to the value of the variable “originCity”

ps.setInt(2, dayOfMonth); // Sets the second parameter (the second “?”) to the value of the variable “dayOfMonth”

ResultSet rs = ps.executeQuery(); // Executes the query and stores the ResultSet in the variable “rs”

ResultSet

• A ResultSet represents a table of data returned from executing a query
• It maintains an internal cursor that points to the current row of data, initially that cursor is positioned before the first row so the first call to `next` moves the cursor to the first row
• Makes it ideal for a while loop:

​

// ... continued from previous slide

ResultSet rs = ps.executeQuery();

​

while (rs.next()) { // check if there is another row and move to the next row

String destCity = rs.getString(“dest_city”); // Gets the value of the attribute “dest_city” for the current row

...

} // When `next` finally returns false, we know we’ve seen every row

rs.close(); // Remember to close the ResultSet

HW6 Notes

• We will be using Azure again: you’ll need your server name, database name, username, and password to put in the dbconn.properties file
• This file should not be submitted
• Add test cases of your own, the ones that we provided are not exhaustive and do not test for the full set of capabilities we expect
• There are private test cases - you can run them on Gradescope and see whether they pass, but won’t see tested commands or expected output
• Private test case file names are a hint!

HW6 Notes part 2

• Remember the tables you create is for global data stored for all users
• Do not store local data for one user which might get deleted after the user quits the session
• The Flights, Weekdays, Months, and Carriers tables should be read-only - you shouldn’t have to modify these.
• Reminder to be careful about the order in which you create/delete tables!
• Make sure to append your uw netid to your custom tables

​

HW6 Notes part 3

• The `Flight` class is there for you to use if you like, feel free to modify it, add methods, etc.
• You can also add internal classes into Query.java as you see fit
• All your code must be in Query.java or PasswordUtils.java
• It might be helpful to create a class that represents an Itinerary
• If it implements the `Comparable` interface then you can use `Collections.sort` to sort it (this would be helpful for sorting one-hop and direct flights with one another)

HW6 Demo

Locking

Locking Overview

Remember - locking is for database internal implementation

• A way to provide the ACID guarantees
• Pessimistic concurrency control
• Assumes transaction executions will conflict
• Users of a database need only specify �BEGIN TRANSACTION … COMMIT / ROLLBACK
• ...and the isolation level...

Database Lock

Table Lock

Predicate Lock

Tuple Lock

Lock Granularity

SQLite!

Less Concurrency

More Concurrency

Binary Locks

2PL with Binary Locks

Lock growing phase

Lock shrinking phase

2PL vs. Strict 2PL

2PL:

• In every transaction, all lock requests must precede all unlock requests
• Ensures conflict serializability
• Might not be able to recover (Dirty Read: Read on some write that gets rolled back)
• Can result in deadlocks

Strict 2PL:

• Every lock for each transaction is held until commit or abort
• Ensures conflict serializability
• Recoverable as each transaction does not affect others until commit/abort
• Can result in deadlocks

2PL vs. Strict 2PL

Invalid reads

Trickled unlock and transaction end

3-Tiered Locking (Shared/Exclusive locks)

• X(A) - Exclusive lock on A
• No other transaction can read or write to A
• S(A) - Shared lock on A
• Other transactions can acquire a shared lock to A, only allows for reads
• If only one transaction holds a shared lock for A, �then it can upgrade to an exclusive lock
• U(A) - No lock on A (or think of it as Unlock)
• This transaction does not hold a lock on A
• If it had a shared lock, some other transactions might still have a lock for A

All the locks!

Worksheet