Transaction begin mode and commit/rollback lifecycle differ from sqlite-jdbc #40

Problem

Locking and transaction state differ from sqlite-jdbc in ways that affect concurrent workloads. A pool or application that relies on sqlite-jdbc's begin timing can see different lock acquisition and different transaction lifetimes after a swap.

Actual behavior

setAutoCommit(false) does not begin a transaction. The first statement issues BEGIN IMMEDIATE for read-write connections or BEGIN DEFERRED for read-only connections (LibsqlConnection.java:159-172,217-233). xerial 3.53.4.0 begins immediately with the configured mode, DEFERRED by default (SQLiteConnection.java:345-361). After commit or rollback, xerial begins a new transaction immediately (:433-457), while libSQL waits for the next statement. rollback also swallows failures.

Expected behavior

The transaction contract is defined. Matching sqlite-jdbc means using the configured begin mode and beginning a new transaction after commit or rollback. Keeping lazy begin is acceptable if the lock behavior is documented.

Reproduction

1. Open a connection, call setAutoCommit(false), and run BEGIN on the same connection. It succeeds in libSQL because no transaction is open, and fails in xerial.
2. Read a row under two concurrent connections and compare lock acquisition and blocking.
3. Commit and inspect whether a new transaction is already open.

Acceptance criteria

• A documented contract covers begin timing and begin mode.
• Tests cover begin timing, the configured mode, commit, rollback, and re-begin.
• The documented lock behavior matches what concurrent readers and writers observe.
• The contract does not depend on rollback swallowing errors.

References

• libsql-jdbc/src/main/java/com/github/magnusp/libsql/jdbc/LibsqlConnection.java:159-172 (ensureTransactionStarted)
• libsql-jdbc/src/main/java/com/github/magnusp/libsql/jdbc/LibsqlConnection.java:217-233 (setAutoCommit)
• libsql-jdbc/src/main/java/com/github/magnusp/libsql/jdbc/LibsqlConnection.java:234-273 (commit and rollback)
• xerial 3.53.4.0 SQLiteConnection.java:345-361 (setAutoCommit) and :433-457 (commit and rollback)

Steering

Lane D (LibsqlConnection.java), position 2 of 2. Depends on #30. Lands in wave 2.

Corrections from the #28 review. Scope is limited to begin timing, begin mode, and re-begin after commit or rollback. Rollback error handling belongs to #30; drop it from this issue's actual behavior and acceptance criteria and cross-reference #30 instead. Reproduction step 1 is wrong: after setAutoCommit(false), the first statement triggers ensureTransactionStarted, which sends BEGIN IMMEDIATE, so an explicit BEGIN fails in libSQL too. Probe begin timing differently, for example by recording the statements the driver sends or by observing lock acquisition from a second connection.

Contract. Default parity with xerial 3.53.4.0 (SQLiteConnection.java:345-361,433-457): eager begin in the configured mode, and a new transaction begins after commit or rollback. Eager begin costs a round trip per transition over Hrana, so this is the issue where the documented-deviation escape hatch is most likely. The PR must state the chosen contract explicitly. Do not silently keep lazy begin.

Evidence. New tests fail on the current code and pass after the fix. Cover begin timing, the configured mode, commit, rollback, and re-begin, plus the documented lock behavior observed by concurrent connections in libsql-itests. Run ./mvnw -pl libsql-jdbc -am test, ./mvnw -pl libsql-itests -am verify (Docker required), and ./mvnw spotless:apply before push.

PR shape. One PR, Closes #40. Part of #54.