How to start a transaction in JDBC?
This bring up the question: how do you begin a transaction in JDBC? It’s clear how to end a transaction, but not how to begin it. If a Connection starts inside in a transaction, how are we supposed to invoke Connection.setTransactionIsolation(int) outside of a transaction to avoid implementation-specific behavior?
from JDBC 4.0 specification : «When to start a new transaction is a decision made implicitly by either the JDBC driver or the underlying data source. Although some data sources implement an explicit “begin transaction” statement, there is no JDBC API to do so. Typically, a new transaction is started when the current SQL statement requires one and there is no transaction already in place. Whether or not a given SQL statement requires a transaction is also specified by SQL:2003.»
8 Answers 8
Answering my own question:
- JDBC connections start out with auto-commit mode enabled, where each SQL statement is implicitly demarcated with a transaction.
- Users who wish to execute multiple statements per transaction must turn auto-commit off.
- Changing the auto-commit mode triggers a commit of the current transaction (if one is active).
- Connection.setTransactionIsolation() may be invoked anytime if auto-commit is enabled.
- If auto-commit is disabled, Connection.setTransactionIsolation() may only be invoked before or after a transaction. Invoking it in the middle of a transaction leads to undefined behavior.
JDBC implicitly demarcates each query/update you perform on the connection with a transaction. You can customize this behavior by calling setAutoCommit(false) to turn off the auto-commit mode and call the commit()/rollback() to indicate the end of a transaction. Pesudo code
try < con.setAutoCommit(false); //1 or more queries or updates con.commit(); >catch(Exception e) < con.rollback(); >finally
Now, there is a type in the method you have shown. It should be setTransactionIsolation(int level) and is not the api for transaction demarcation. It manages how/when the changes made by one operation become visible to other concurrent operations, the «I» in ACID (http://en.wikipedia.org/wiki/Isolation_(database_systems))
Your answer does not indicate when it is safe to invoke setTransactionIsolation(). Why does the Javadoc read «Note: If this method is called during a transaction, the result is implementation-defined.»?
one more tip: be nice and restore the previous state of AutoCommit: boolean previousAutoCommit = conn.getAutoCommit(); try <. >finally
@voho Why revert to previousAutoCommit? Even if it is pooled, on con.close(), it goes back to the pool and autoCommit will become true next time you get connection from the pool.
I suggest you read this you’ll see
Therefore, the first call of setAutoCommit(false) and each call of commit() implicitly mark the start of a transaction. Transactions can be undone before they are committed by calling
Check the official documentation on JDBC Transactions
When a connection is created, it is in auto-commit mode. This means that each individual SQL statement is treated as a transaction and is automatically committed right after it is executed. (To be more precise, the default is for a SQL statement to be committed when it is completed, not when it is executed. A statement is completed when all of its result sets and update counts have been retrieved. In almost all cases, however, a statement is completed, and therefore committed, right after it is executed.)
The way to allow two or more statements to be grouped into a transaction is to disable the auto-commit mode. This is demonstrated in the following code, where con is an active connection:
con.setAutoCommit(false);
I can’t find the aforementioned quote on the page you linked to. Furthermore, I’d much prefer finding a quote in the JDBC specification as opposed to some unsourced quote on devx (assuming it is unsourced, that is).
You can use these methods for transaction:
- you must create the connection object like con
- con.setAutoCommit(false);
- your queries
- if all is true con.commit();
- else con.rollback();
I’m assuming that the call to con.commit(); wraps/adds the SQL to execute in BEGIN TRANSACTION; and COMMIT; statements (or behaves in a way that can be considered equivalent), doing away with the need for the code to explicitly include lines for these statements. Is that correct?
Actually, I like this answer best because it gives a nice overview of the ‘normal flow’, and drives home the point (to me at least) that setAutoCommit(false) sets the stage for the driver to begin a new transaction prior to sending your next Statement .
Startingly, you can manually run a transaction, if you wish to leave your connection in «setAutoCommit(true)» mode but still want a transaction:
try (Statement statement = conn.createStatement()) < statement.execute("BEGIN"); try < // use statement . statement.execute("COMMIT"); >catch (SQLException failure) < statement.execute("ROLLBACK"); >> This solution is fine if you don’t have multiple statements in 1 transaction, otherwise use the other approaches
Derby/JavaDB for example does not have BEGIN/COMMIT/ROLLBACK SQL, but relies entirely on connection methods, which are communicated to the server via protocol elements different from SQL.
Actually, this page from the JDBC tutorial would be a better read.
You would get your connection, set your isolation level and then do your updates amd stuff and then either commit or rollback.
Even the tutorial does not address this issue explicitly. It contains some confusing sentences like: «It is advisable to disable the auto-commit mode only during the transaction mode.» What is a «transaction mode»?
Maybe this will answer your question: You can only have one transaction per connection. If autocommit is on (default), every select, update, delete will automatically start and commit (or rollback) a transaction. If you set autocommit off, you start a «new» transaction (means commit or rollback won’t happen automatically). After some statements, you can call commit or rollback, which finishes current transaction and automatically starts a new one. You cannot have two transactions actively open on one JDBC connection on pure JDBC.
Using one connection for multiple transactions (reuse, pooling or chaining) some weird problems can lurk creating problems people have to live by since they usually cant identify the causes.
The following scenarios come to mind:
- (Re-)Using a connection with an ongoing / uncommitted transaction
- Flawed connection pool implementations
- Higher isolation level implementations in some databases (especially the distributed SQL and the NoSQL one)
Point 1 is straight forward and understandable. Point 2 basically leads to either point 1 or (and) point 3.
Point 3 is all about a system where a new transaction has begun before the first statement is issued. From a database perspective such a transaction might have started long before the ‘first’ real statement was issued. If the concurrency model is based on the snapshot idea where one reads only states/values that were valid at the point the transaction begins but no change that has changed later on, it is very important that on commit the full read set of the current transaction is also validated.
Since NoSQL and certain isolation levels like MS SQL-Server Snapshot often do not validate the read-set (in the right way), all bets are usually off to what to expect. While this is a problem always being present, it is way worse when one is dealing with transactions that start on the last commit or when the connection was pooled rather than the connection being actually used, it is usually important to make sure the transaction actually starts when it is expected to start. (Also very important if one uses a rollback-only read-only transaction).
I use the following rules when dealing with JDBC in JAVA:
- Always rollback a JDBC connection before using it (scraps everyting and starts a new transaction), if the company uses plain JDBC in conjunction with any pooling mechanism
- Use Hibernate for Transaction handling even if only using a session managed JDBC connection for plain SQL. Never had a problem with transactions till now.
- Use BEGIN / COMMIT / ROLLBACK as SQL-Statements (like already mentioned). Most implementations will fail if you issue a BEGIN statement during an active transaction (test it for your database and remember the test database is not the production database and JDBC Driver and JDBC Server-side implementations can differ in behaviro than running a SQL console on the actual server).
- Use 3 inside one’s own wrapper for a JDBC connection instances. This way transaction handling is always correct (if no reflection is used and the connection pooling is not flawed).
3+4 I only use if response time is critical or if Hibernate is not available. 4 allows for using some more advanced performance (response time) improvement patterns for special cases