Class 23 — Monday, November 16
Quiz 10 first. Then the other half of what a database does: a change made in two statements must land as both or neither, and two such changes at once must not corrupt each other.
- A deposit that stops halfway — predict first
- One transaction
- Two transfers at once — predict first
- In name order
- What the database did for you — keyboards down
- A server whose accounts survive
Today’s work goes in your copy of cmsc-230-jdbc, beside Wednesday’s files, which made bank.mv.db with alice and bob in it. If yours is gone, run FirstTable once.
1. A deposit that stops halfway
Keyboards down. Write in the Journal first, then run it.
A bank records every deposit. So a deposit changes the database twice: an UPDATE adds the money to the balance, and an INSERT puts a row into a table of deposits, saying who deposited how much. In the program below the two are two statements, and between them the program crashes on purpose.
import java.sql.*;
// a deposit in two statements, with a crash between them
public class HalfDeposit {
static final String ADD = "UPDATE accounts"
+ " SET balance = balance + ? WHERE name = ?";
static final String RECORD = "INSERT INTO deposits"
+ " (name, cents) VALUES (?, ?)";
static boolean crash = true;
static Connection open() throws SQLException {
String url = "jdbc:h2:./bank";
return DriverManager.getConnection(url);
}
// adds cents to name's balance, then records it
static void deposit(Connection db, String name,
long cents) throws SQLException {
PreparedStatement add = db.prepareStatement(ADD);
add.setLong(1, cents);
add.setString(2, name);
add.executeUpdate();
if (crash) {
throw new IllegalStateException(
"the program stopped");
}
PreparedStatement rec = db.prepareStatement(RECORD);
rec.setString(1, name);
rec.setLong(2, cents);
rec.executeUpdate();
}
public static void main(String[] args)
throws SQLException {
try (Connection db = open()) {
deposit(db, "alice", 500);
System.out.println("deposited and recorded");
}
}
}The two statements are prepared: the SQL has a ? where each value goes, and setLong(1, cents) and setString(2, name) fill them, first ? and second, before executeUpdate runs it. A value given this way is only ever a value, whatever is in it. ADD and RECORD are the SQL, written once as constants.
ShowBoth prints alice’s balance and her deposits on record, and makes the deposits table the first time:
import java.sql.*;
// alice's balance, and her deposits on record
public class ShowBoth {
static final String DEPOSITS = "CREATE TABLE IF NOT"
+ " EXISTS deposits (id BIGINT GENERATED BY"
+ " DEFAULT AS IDENTITY PRIMARY KEY,"
+ " name VARCHAR(40), cents BIGINT)";
public static void main(String[] args)
throws SQLException {
try (Connection db = HalfDeposit.open()) {
Statement sql = db.createStatement();
sql.executeUpdate(DEPOSITS);
ResultSet row = sql.executeQuery(
"SELECT balance FROM accounts"
+ " WHERE name = 'alice'");
row.next();
System.out.println("balance " + row.getLong(1));
ResultSet rows = sql.executeQuery(
"SELECT id, cents FROM deposits"
+ " WHERE name = 'alice' ORDER BY id");
int count = 0;
while (rows.next()) {
count++;
System.out.println("deposit "
+ rows.getLong("id") + ": "
+ rows.getLong("cents"));
}
System.out.println(count
+ " deposits on record");
}
}
}Run ShowBoth, then HalfDeposit, which stops with its exception.
Now run
ShowBothagain. What does it print, and why?
balance 10500, and 0 deposits on record. The first statement ran and the second never did, and the database kept what the first one changed: alice has $5.00 more, and no row says where it came from. Half a deposit.
Each statement landed on its own, the moment it ran. That is the connection’s autocommit mode, and it is on until you say otherwise. Section 2 says otherwise.
2. One transaction
Pairs. 2.1 and 2.2, then 2.3 if you are done.
A transaction is a run of statements that land together or not at all. Three calls on the connection make one:
db.setAutoCommit(false); // from here, nothing lands yet
db.commit(); // everything since lands, at once
db.rollback(); // or nothing since does2.1 WholeDeposit
A new program whose main turns autocommit off, calls HalfDeposit.deposit(db, "alice", 500) inside a try, and commits after it; the catch (RuntimeException e) rolls back and prints that it did. Run it with the crash, then ShowBoth. Then set HalfDeposit.crash = false first and run it again.
Done when the crashing run changes nothing, and the other adds 500 to the balance and one row to deposits, both.
2.2 A transfer, in one transaction
A new file, Transfer, with
that takes cents from one account and adds them to the other, in one transaction, and returns whether it did. The rule that nobody goes below zero is in the first UPDATE itself:
executeUpdate returns the rows it changed: 1 when the money was there, 0 when it was not. On 0, roll back and return false. The check and the change are one statement, so nothing can happen in between them.
Your test, in test/TransferTest.java, with a database in memory: jdbc:h2:mem:t1;DB_CLOSE_DELAY=-1. mem: is what makes it one: no file, and gone when the program ends, so a test can make its own table and two accounts and start from nothing every time. DB_CLOSE_DELAY=-1 keeps it until then: without it, H2 drops an in-memory database the moment its last connection closes, and a test that opens a second connection finds nothing. Two tests: a transfer of 3.00 from alice’s 10.00 to bob’s 2.50 leaves 7.00 and 5.50; a transfer of 2.51 from bob is refused and changes neither balance. Use t2 for the second database.
Done when both tests are green.
2.3 A transfer to nobody (if you are done)
transfer(db, "alice", "carol", 100): the second UPDATE changes 0 rows, and the money has already left alice. Make transfer roll back and return false in that case too.
Your test: that transfer, and alice’s balance as it was.
When the section ends: commit and push.
3. Two transfers at once
Keyboards down. Predict first, then run it.
Two threads, each with a connection of its own, each sending one cent 5,000 times, in opposite directions, with your transfer from 2.2. The accounts are in a database in memory, made fresh:
import java.sql.*;
import java.util.*;
// two threads, each with a connection of its own, transfer
// one cent 5,000 times each way, in a database in memory;
// then the total, and how many transfers failed
public class TwoAtOnce {
static final String URL = "jdbc:h2:mem:race"
+ ";DB_CLOSE_DELAY=-1";
static Connection open() throws SQLException {
return DriverManager.getConnection(URL);
}
static void setUp() throws SQLException {
try (Connection db = open()) {
Statement sql = db.createStatement();
sql.executeUpdate("CREATE TABLE accounts"
+ " (name VARCHAR(40) PRIMARY KEY,"
+ " balance BIGINT)");
sql.executeUpdate("INSERT INTO accounts VALUES"
+ " ('alice', 100000),"
+ " ('bob', 100000)");
}
}
// one thread's work: 5,000 transfers of one cent
static Runnable sends(String from, String to,
List<String> failed) {
return () -> {
try (Connection db = open()) {
for (int i = 0; i < 5000; i++) {
try {
Transfer.transfer(db, from, to, 1);
} catch (SQLException e) {
String why = e.getMessage();
failed.add(why.split(";")[0]);
}
}
} catch (SQLException e) {
failed.add("could not connect: " + e);
}
};
}
public static void main(String[] args)
throws Exception {
setUp();
List<String> failed = Collections
.synchronizedList(new ArrayList<>());
Runnable ab = sends("alice", "bob", failed);
Runnable ba = sends("bob", "alice", failed);
Thread one = new Thread(ab);
Thread two = new Thread(ba);
one.start();
two.start();
one.join();
two.join();
try (Connection db = open()) {
ResultSet r = db.createStatement().executeQuery(
"SELECT SUM(balance) FROM accounts");
r.next();
System.out.println("total " + r.getLong(1)
+ ", failed " + failed.size());
if (!failed.isEmpty()) {
System.out.println("first: "
+ failed.get(0));
}
}
}
}Both accounts start with 1,000.00. What will the total be at the end, and will any transfer fail? Why?
The total is 200000 every run: no cent was lost or made. But about one transfer in six failed, three runs here failing 1,776, 1,430 and 1,644 of 10,000, every one with
A transaction that changes a row holds that row until it commits, as a synchronized block holds a lock. One thread takes alice’s row and asks for bob’s; the other has bob’s and asks for alice’s. Class 19’s two transfers, inside the database. The difference: the database notices the circle, picks one of the two, rolls it back whole and tells it so, instead of waiting for ever. The total holds because the rolled-back transfer changed nothing.
4. In name order
Pairs. 4.1, then 4.2 if you are done.
4.1 Ordered
A copy of transfer that changes the two rows in name order: whichever account’s name comes first alphabetically, its UPDATE runs first. Two opposite transfers then ask for the same row first, and one waits for the other to commit. The refusal still comes from the sender’s row count, whichever order it runs in.
Done when TwoAtOnce, calling your Ordered.transfer, prints failed 0 three runs in a row.
4.2 Nothing lost under load (if you are done)
Your test: TwoAtOnce’s two threads as a test, on a database of its own: after the joins, the two balances add up to 200000 and nothing failed. Mark it @Timeout(30).
When the section ends: commit and push.
5. What the database did for you
Keyboards down.
in A9’s MemoryBank |
in the database |
|---|---|
synchronized (account) round check and change |
one UPDATE, the check in its WHERE |
| two locks, taken in name order | two rows, changed in name order |
| a deadlock hangs for ever | a deadlock is detected, one side rolled back |
a List<Entry> copied on the way out |
a SELECT of rows nobody is changing |
| a crash mid-method leaves the object half changed | a stop before commit lands nothing |
One line is not like the others: the database does something there that A9’s Java never did. Which, and what does it still leave to you?
The deadlock. A9’s two threads would wait for each other for ever, and only the order of the locks prevented it. The database notices the circle and rolls one transfer back, so nothing hangs and the money is safe. What it leaves to you is the retry, or the order: a rolled-back transfer did not happen, and Ordered is what makes it not need to.
6. A server whose accounts survive
Pairs. 6.1, then 6.2 if you are done.
6.1 AccountServer
Wednesday’s threaded server, on the database: a thread for each client, and for each line a connection of its own to jdbc:h2:./bank;DB_CLOSE_DELAY=-1. Two commands: BALANCE alice answers BALANCE 11000, and DEPOSIT alice 500 calls HalfDeposit.deposit (with crash off) and answers OK. Anything else, and any exception, is one ERROR line. Talk to it with Wednesday’s EchoClient from Class 20, on port 6230.
Done when a client deposits and reads the balance; you stop the server with the red square and start it again; and a new client reads the same balance.
6.2 WITHDRAW (if you are done)
WITHDRAW alice 300, with the rule in the WHERE, as 2.2’s first UPDATE: OK when the money was there, ERROR when it was not, and the balance unchanged then.
When the section ends: commit and push.