Quiz: JDBC Transactions: setAutoCommit, commit and rollback
Study the lesson first: JDBC Transactions: setAutoCommit, commit and rollback
Total: 6 questions
1. What is auto-commit mode in JDBC, and what happens to several statements run without a transaction?
What is auto-commit mode in JDBC, and what happens to several statements run without a transaction?
A new JDBC connection works in auto-commit mode: getAutoCommit() returns true, and every executeUpdate is committed at once as a separate transaction. A Java exception does not roll back what the database has already saved: if you transfer money with two UPDATE statements without a transaction and the recipient account does not exist, the debit stays (Alice had 1000, now 900, and the 100 went nowhere).
To make several statements run as one unit, call connection.setAutoCommit(false). The call itself does not start a transaction: it starts with the first SQL statement after it and lasts until commit() or rollback(), and the next statement starts a new one. You do not need to call setAutoCommit(false) again before each transaction as long as the mode has not been switched back to true.
With auto-commit off, even a SELECT opens a transaction that has to be ended: otherwise at REPEATABLE READ it keeps seeing the old snapshot of the data. For reads, keep autoCommit = true or call connection.setReadOnly(true).
2. How do you write a JDBC transaction correctly with commit() and rollback()?
How do you write a JDBC transaction correctly with commit() and rollback()?
Four required parts, as in the MoneyTransfer class:
setAutoCommit(false)before the first statement, remembering the previous mode withgetAutoCommit();commit()at the end of the success path: without it, MySQL rolls the transaction back when the connection closes;rollback()incatchandthrow e: without the rollback, the changes stay on the connection and are saved by the nextcommit(), and a swallowed exception looks like a successful transfer;- restore the previous
autoCommitinfinally: the connection goes back to the pool and to code that expects auto-commit.
The balance check is part of the query: UPDATE ... WHERE id = ? AND balance >= ?, and if executeUpdate() does not return 1, the method throws. A separate SELECT before the debit does not protect you: two concurrent transfers both see enough money. Keep the transaction short: while it is open, the database holds locks.
commit() with autoCommit = true throws SQLException, while setAutoCommit(true) in the middle of a transaction silently commits it.
3. What is a Savepoint in JDBC, and how do you roll back only part of a transaction?
What is a Savepoint in JDBC, and how do you roll back only part of a transaction?
A Savepoint is a marker inside a transaction. connection.setSavepoint("name") sets it, connection.rollback(savepoint) undoes the changes made after it, and everything done before the marker stays in the transaction and is saved by a regular commit().
The lesson example: with balances of 700.00 and 800.00, Alice gets a bonus of 50, the savepoint after_bonus is set, 900 is wrongly debited from Bob, then rollback(afterBonus) and commit() are called. The database holds Alice = 750.00, Bob = 800.00.
rollback(savepoint) does not end the transaction: you still need a commit() or a full rollback() after it. Savepoints are handy when processing a long list of operations where a failure in one of them can be skipped without losing the rest. connection.getMetaData().supportsSavepoints() tells whether the driver supports them; MySQL (InnoDB), PostgreSQL and Oracle do.
4. How do you set the transaction isolation level in JDBC, and what are the values of the Connection constants?
How do you set the transaction isolation level in JDBC, and what are the values of the Connection constants?
The level is set with connection.setTransactionIsolation(int) using a constant of the Connection interface, and read with getTransactionIsolation():
TRANSACTION_NONE= 0: transactions are not supported;TRANSACTION_READ_UNCOMMITTED= 1;TRANSACTION_READ_COMMITTED= 2: the default in PostgreSQL, Oracle and SQL Server;TRANSACTION_REPEATABLE_READ= 4: the default in MySQL (InnoDB), so on MySQLgetTransactionIsolation()returns 4;TRANSACTION_SERIALIZABLE= 8.
Change the level before the transaction starts (in the lesson example, before setAutoCommit(false) and the queries): the JDBC specification leaves a call in the middle of an open transaction implementation-defined, and drivers behave differently. The new level applies to the connection until changed again, so in a pool it should be restored, just like autoCommit. getMetaData().supportsTransactionIsolationLevel(level) tells whether the database supports a level.
5. How do you run one transaction across several DAOs, and how does this relate to @Transactional in Spring?
How do you run one transaction across several DAOs, and how does this relate to @Transactional in Spring?
A JDBC transaction belongs to a single Connection object. If every DAO method takes its own connection from the pool, orderDao.create(order) and accountDao.debit(userId, sum) end up in different transactions: if the debit fails, the order is already saved through its own connection.
The framework-free fix: the service takes a connection, turns auto-commit off, passes that Connection as a parameter to every DAO method and calls commit(), and in catch calls rollback() and rethrows. The DAOs do not close the connection: the service owns it and the transaction.
In Spring, @Transactional on a service method does the same: one connection is bound to the thread, commit() is called after a normal return, and rollback() on an unchecked exception. Checked exceptions do not roll the transaction back by default; that is configured with the rollbackFor attribute.
Two different databases cannot be joined by a plain JDBC transaction: you need a distributed (XA) transaction through a JTA manager such as Atomikos, or the Saga pattern with compensating operations.
6. How do you handle a deadlock in JDBC, and when can a transaction be retried?
How do you handle a deadlock in JDBC, and when can a transaction be retried?
On a deadlock, the database rolls one of the transactions back as a whole, and the driver throws SQLTransactionRollbackException, a subclass of SQLException with an SQLState starting with 40. MySQL returns error 1213 with SQLState 40001, and Connector/J throws exactly this exception. All changes of such a transaction are already undone, so it is safe to retry.
The retry goes in the calling code: a loop with try, break after success, and catch (SQLTransactionRollbackException e) that rethrows after a few attempts (three in the lesson). For this, the transaction method must rethrow the original exception after rollback(), as MoneyTransfer.transfer does.
Catch exactly SQLTransactionRollbackException, not any SQLException: errors like "insufficient funds" make no sense to retry, they come back on every attempt.