Transactions#

There are no annotations. What you wrapped is the transaction.

try (Tx tx = db.begin()) {

	long id;
	try {
		id = db.insertKey(
			SQL.insert(Post.instance())
				.value(Post.title, request.getString("title"))
				.value(Post.body, request.getString("body"))
				.value(Post.image_name, request.getStringOptional("image_name"))
				.value(Post.published, request.getBoolean("published"))
				.value(Post.created_at, new Date())
		);
	} catch (SqlExecuteException ex) {
		return -1;                   // tx.commit() まで来ないので、抜けたら巻き戻る
	}

	new NoticeExecutor().put(db, new Data()
		.putData("post_id", id)
		.putData("title", request.getString("title")));

	tx.commit();                     // 記事とキューを一緒に確定して終わる

	/*
	 * コミットしてから流す。
	 * 先に流すと、ロールバックしたときに
	 * 「入っていない記事のお知らせ」だけが届く。
	 */
	PostFeedHandler.notifyNewPost(request.getStringOptional("title"));

	return id;

}

Wrap the Tx that db.begin() returns in try-with-resources, and call tx.commit() at the end. Leave without calling commit() and it rolls back — whether by return or by an exception. In the example above, when the article does not go in it leaves with return -1, so nothing is left behind.

Three ways to write it#

db.transaction(tx -> {
	db.insert(...);
	db.update(...);
});                                   // commits if the body returns normally; rolls back and rethrows otherwise

long id = db.transactionResult(tx -> db.insertKey(...));   // returns a value

try (Tx tx = db.begin()) {            // when you want to commit yourself
	db.update(...);
	tx.commit();                      // commits and ends; leaving without it rolls back
}

db.transaction(...) is usually the shortest. Use db.begin() when you want to leave partway (like the return -1 above) or decide the commit yourself.

transaction and transactionResult have different names because an expression lambda such as tx -> db.update(...) reads both as "returns a value" and "returns nothing", so with a single name the call could not be told apart.

Tx methods#

Failures are TransactionException (unchecked; a subclass of SqlExecuteException). getCode() gives DB_004 and so on.

commit() and rollback() are one-shot. Calling one again after the transaction has ended throws IllegalStateException. To commit and carry on, use checkpoint(). Anything written after checkpoint() rolls back unless you commit().

You may call tx.commit() / tx.rollback() yourself inside db.transaction(...). It then does nothing more on the way out.

When SQL fails inside#

A SQL failure is an exception (Using the DB). Leave it uncaught and it leaves the Tx, and everything rolls back. Nothing goes in halfway.

Catch it and carry on, and that Tx can no longer commit.

db.transaction(tx -> {
	db.insert(...);                  // fine
	try {
		db.update(...);              // hit a unique constraint
	} catch (DuplicateKeyException e) {
		// caught; carry on
	}
	db.insert(...);                  // fine
});                                  // <- TransactionException (DB_004) here. Everything rolls back

If any SQL has failed since the transaction started, commit() rolls everything back and throws TransactionException (DB_004). One successful statement after a failure does not hide it. checkpoint() refuses the same way.

Branching on a failure and writing the other path#

Roll that Tx back and write again in a new Tx.

try {
	db.transaction(tx -> {
		insert(...);
		update(...);                 // may hit a unique constraint
	});                              // left by an exception, so it has rolled back here
} catch (DuplicateKeyException e) {
	db.transaction(tx -> insertFallback(...));   // the other path, in a new Tx
}

If there is an outer transaction too, the inner Tx has joined it (see "Nesting" below). In that case, redo the outer one as a whole.

When the body throws a checked exception#

The body of db.transaction(...) / transactionResult(...) may throw checked exceptions (no throws needed). Unchecked exceptions are rethrown as they are; checked ones are wrapped in TransactionException (DB_006), after rolling back. The original exception is at getCause().getCause().

Nesting (joining)#

Calling db.begin() (or db.transaction(...)) on a DB that already has a transaction open does not start a new one; it joins the outer one. tx.isJoined() tells you whether it joined.

A rollback-only outer commit() rolls everything back and throws TransactionException (DB_005). Catching the inner exception in the outer one and carrying on does not let the outer one commit. Nor can you commit the outer one partway (the inner checkpoint() does nothing).

Locking with a DB row (DBLock)#

Where there is no Redis, DBLock lets you run "one at a time per key".

DBLock.create(db, "daily");            // create the key's row (does nothing if it exists). Once, beforehand

db.transaction(tx -> {
	DBLock.lock(db, "daily");          // SELECT ... FOR UPDATE. Only one until the transaction ends
	...
});

Call DBLock.lock inside a transaction. Outside one it throws IllegalStateException (a FOR UPDATE lock is released at the end of the statement, so taking it outside protects nothing).

• A missing key (no create) also throws IllegalStateException
• create / lock return nothing (void). A SQL failure throws SqlExecuteException
• The lock is released at the end of the transaction. There is no way to release it explicitly

Locks backed by Redis are in Cache and locks.

What leaves the process, and what goes into the DB#

The two go on opposite sides of the commit. This one is easy to get backwards.

db.transaction(tx -> {

	long id = db.insertKey(...);

	new NoticeExecutor().put(db, data);      // inside — it is a DB queue

});

PostFeedHandler.notifyNewPost(title);        // outside — only after the commit

Publish the outgoing one first and a rollback leaves you delivering "a notice about an article that is not there".

Push the DB queue after the commit instead and whatever was in flight when the process died is silently gone — the article is there, and nobody is told about it.

Forgetting to close#

Write Tx tx = db.begin() without try-with-resources and return partway through without commit() or rollback(), and nothing is rolled back and the connection never goes back to the pool.

jimble catches it at the end of the execution (Context).

ERROR コミットもロールバックもされていないトランザクションが残っていました。ロールバックして閉じます: blog_example

Rolling back silently would leave you with "I thought I saved it and it is not there", so it logs an ERROR first and then rolls back. If you see this line, you forgot to wrap something. (It is watched on the DB side, because the DB is what holds the connection.)

An unclosed DB#

An ordinary statement does not leak when you forget to close the DB. The connection goes back to the pool as soon as the statement finishes, which is what makes the throwaway DBUtil.getMainDB() style fine.

There are only two ways to leave with the connection held, and both are caught at the end of the execution.

ERROR 閉じられていない DB が残っていました。閉じます: blog_example（selectListWithFetcher はカーソルなので、close() までコネクションを返しません）