Adventures in JDBC and the PostgreSQL JDBC driver: Part 5 - The Effect Of Connection.setAutoCommit(...) method in JDBC
Adventures in JDBC and the PostgreSQL JDBC driver: Part 5 - The Effect Of Connection.setAutoCommit(…) method in JDBC
In database design, there are usually three levels of transaction support: No transaction, Local transaction, Distributed transaction.
No-transaction means each SQL command will be executed and their effects will be persisted into the database immediately. In PostgreSQL database, this is supported by setting autocommit option to true, and user will not issue the BEGIN command to start a transaction explicitly. In this situation, we can see each SQL command being is encapsulated in their own in-place transaction.
Local-transaction means the user will explicitly start a transaction by using the BEGIN command, and then the following SQL commands are under the scope of this transaction, until a COMMIT command is met, and all the effects of the commands will be persisted into database at once, or fail as a whole.
Distributed-transaction means the database will participate in a bigger transaction out of the database system scope itself. For example, we may have a transaction that includes several participants, which are: two databases, one EJB session bean, one webservice, etc. In this situation, our database is just one of the participants of the whole transaction.
There are several specifications in distribution transaction area, and the most popular one is X/Open XA (See Wikipedia) spec, and it’s adopted by the JDBC spec. You can see relative interfaces in JDBC library, such as theXADataSource interface. In PostgreSQL JDBC driver, you can see the classes that implements these interfaces. We will check the details on distributed transactions in another article.
In this article, I will focus on the No-transaction and the Local-transaction situation to see how does the PostgreSQL driver supporting them. I want to check the setAutoCommit(...) method defined in java.sql.Connection interface to see how it affects the transaction processing in underlying database system.
Now let’s use the PostgreSQL JDBC driver to write an example to execute some SQL commands, and check how does the driver implements the JDBC interface. Firstly, I need to open the SQL logging capability of the PostgreSQL. I have installed my postgresql database by Homebrew(See https://brew.sh/) on my MacOS machine, and the configuration file is located at:
/usr/local/pgsql/data/postgresql.conf
Here is what I have changed in the configuration file:
--- postgresql.conf.orig 2017-05-03 23:23:53.000000000 +0800
+++ postgresql.conf 2017-05-03 23:11:48.000000000 +0800
@@ -333,18 +333,22 @@
# depending on platform. csvlog
# requires logging_collector to be on.
+log_destination = stderr
+
# This is used when logging to stderr:
#logging_collector = off # Enable capturing of stderr and csvlog
# into log files. Required to be on for
# csvlogs.
# (change requires restart)
+logging_collector = on
+
# These are only used if logging_collector is on:
-#log_directory = 'pg_log' # directory where log files are written,
+log_directory = 'pg_log' # directory where log files are written,
# can be absolute or relative to PGDATA
-#log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # log file name pattern,
+log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # log file name pattern,
# can include strftime() escapes
-#log_file_mode = 0600 # creation mode for log files,
+log_file_mode = 0600 # creation mode for log files,
# begin with 0 to use octal notation
#log_truncate_on_rotation = off # If on, an existing log file with the
# same name as the new log file will be
@@ -354,9 +358,9 @@
# or size-driven rotation. Default is
# off, meaning append to existing files
# in all cases.
-#log_rotation_age = 1d # Automatic rotation of logfiles will
+log_rotation_age = 1d # Automatic rotation of logfiles will
# happen after that time. 0 disables.
-#log_rotation_size = 10MB # Automatic rotation of logfiles will
+log_rotation_size = 10MB # Automatic rotation of logfiles will
# happen after that much log output.
# 0 disables.
@@ -451,6 +455,7 @@
# e.g. '<%u%%%d> '
#log_lock_waits = off # log lock waits >= deadlock_timeout
#log_statement = 'none' # none, ddl, mod, all
+log_statement = 'all'
#log_replication_commands = off
#log_temp_files = -1 # log temporary files equal or larger
# than the specified size in kilobytes;
With the above changes, the postgresql server will log the SQL statements executed by clients. If your database server has been started, then you need to restart the server after the configuration file is changed.
After the database server is restarted, we can check the JDBC driver side now. Let’s write a sample class in Java to demonstrate the usage of the JDBC driver. Here is the code:
import org.postgresql.util.PSQLException;
import org.postgresql.util.PSQLState;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
/**
* Created by weli on 30/04/2017.
*/
public class DirectConnection {
public static void main(String[] args) throws Exception {
Class.forName("org.postgresql.Driver");
String url = "jdbc:postgresql://localhost/weli";
Properties props = new Properties();
props.setProperty("user", "weli");
props.setProperty("password", "");
props.setProperty("ssl", "false");
Connection conn = DriverManager.getConnection(url, props);
try {
conn.setAutoCommit(true);
Statement st = conn.createStatement();
st.executeQuery("insert into user_info (id, name, age) values (10, 'weli', 16);");
} catch (PSQLException e) {
if (e.getSQLState().equals(PSQLState.NO_DATA.getState())) {
// expected
}
} finally {
// conn.commit();
conn.close();
}
}
}
The above code will connect to my local database server, and run an insert SQL command to add an entry of data into the database. The important line is here:
conn.setAutoCommit(true);
The above line of code will put the database into the autocommit mode. Please note we have commented out the commit() method of the connection in finally block. This is because in autocommit mode, the executeQuery(...) method of the statement will be implicitly included into its own in-place transaction.
Now we can compile the above class with PostgreSQL JDBC driver in class path:
$ javac -cp ~/.m2/repository/org/postgresql/postgresql/42.0.1-SNAPSHOT/postgresql-42.0.1-SNAPSHOT.jar DirectConnection.java
After the compilation is done, we can run the class like this:
$ java -cp ~/.m2/repository/org/postgresql/postgresql/42.0.1-SNAPSHOT/postgresql-42.0.1-SNAPSHOT.jar:. DirectConnection
After the above command finished running, we can check the SQL log provided by database server:
$ pwd
/usr/local/pgsql/data/pg_log
$ tail -f postgresql-2017-05-22_000000.log
...
STATEMENT: insert into user_info (id, name, age) values (10, 'weli', 16)
From the above log, we can see the JDBC statement maps to a single SQL command, and there are no BEGIN or COMMIT commands surrounded the statement. In this way, we can see how does database deal with the implicit transaction: every single SQL command is treated as an atomic operation and will be persisted into database immediately.
Now let’s go back to the class file and this time we will set the autoCommit property to false:
conn.setAutoCommit(false);
After setting the auto commit property to false, we need to explicitly set commit the transaction in final block. Here is the code:
conn.commit();
Now we recompile the class and rerun the class, and here is the output of the SQL log:
LOG: execute <unnamed>: BEGIN
LOG: execute <unnamed>: insert into user_info (id, name, age) values (10, 'weli', 16)
LOG: execute S_1: COMMIT
From the above log, we can see this time there are BEGIN and COMMIT commands surrounded the statement. We can see the effect of the autoCommit property in JDBC: when it’s setting to false, then the JDBC driver will add a BEGIN command before the statement, and then users need to explicitly commit the transaction with commit() method.
The relative implementation is in the org.postgresql.jdbc.PgStatement class. The autoCommit property will be used to set flags in executeInternal(..., int flags) method of org.postgresql.jdbc.PgStatement class. Here is the relative code in the method:
if (connection.getAutoCommit()) {
flags |= QueryExecutor.QUERY_SUPPRESS_BEGIN;
}
From above code, we can see the QueryExecutor.QUERY_SUPPRESS_BEGIN value is added into flags. Here is the definition of QUERY_SUPPRESS_BEGIN in org.postgresql.core.QueryExecutor.QUERY_SUPPRESS_BEGIN class:
/**
* Flag for query execution that indicates the automatic BEGIN on the first statement when outside
* a transaction should not be done.
*/
int QUERY_SUPPRESS_BEGIN = 16;
After the flags is set, it will finally affect the sendQueryPreamble(...) method in org.postgresql.core.v3.QueryExecutorImpl class. Here is the code of the method:
private ResultHandler sendQueryPreamble(final ResultHandler delegateHandler, int flags)
throws IOException {
// First, send CloseStatements for finalized SimpleQueries that had statement names assigned.
processDeadParsedQueries();
processDeadPortals();
// Send BEGIN on first statement in transaction.
if ((flags & QueryExecutor.QUERY_SUPPRESS_BEGIN) != 0
|| getTransactionState() != TransactionState.IDLE) {
return delegateHandler;
}
int beginFlags = QueryExecutor.QUERY_NO_METADATA;
if ((flags & QueryExecutor.QUERY_ONESHOT) != 0) {
beginFlags |= QueryExecutor.QUERY_ONESHOT;
}
beginFlags |= QueryExecutor.QUERY_EXECUTE_AS_SIMPLE;
beginFlags = updateQueryMode(beginFlags);
sendOneQuery(beginTransactionQuery, SimpleQuery.NO_PARAMETERS, 0, 0, beginFlags);
// Insert a handler that intercepts the BEGIN.
return new ResultHandlerDelegate(delegateHandler) {
private boolean sawBegin = false;
public void handleResultRows(Query fromQuery, Field[] fields, List<byte[][]> tuples,
ResultCursor cursor) {
if (sawBegin) {
super.handleResultRows(fromQuery, fields, tuples, cursor);
}
}
public void handleCommandStatus(String status, int updateCount, long insertOID) {
if (!sawBegin) {
sawBegin = true;
if (!status.equals("BEGIN")) {
handleError(new PSQLException(GT.tr("Expected command status BEGIN, got {0}.", status),
PSQLState.PROTOCOL_VIOLATION));
}
} else {
super.handleCommandStatus(status, updateCount, insertOID);
}
}
};
}
In above code, if QueryExecutor.QUERY_SUPPRESS_BEGIN is set in flag, then it will return immediately. The relative code is here:
// Send BEGIN on first statement in transaction.
if ((flags & QueryExecutor.QUERY_SUPPRESS_BEGIN) != 0
|| getTransactionState() != TransactionState.IDLE) {
return delegateHandler;
}
The QueryExecutor.QUERY_SUPPRESS_BEGIN is set by default, unless autoCommit is set to false in java.sql.Connection. If QueryExecutor.QUERY_SUPPRESS_BEGIN is unset, which means autoCommit is set to false in java.sql.Connection by the user, then the rest part of the code in sendQueryPreamble(...) method will be executed, and a BEGIN command will be sent to database server. Here is the relative code:
sendOneQuery(beginTransactionQuery, SimpleQuery.NO_PARAMETERS, 0, 0, beginFlags);
The above code will send a beginTransactionQuery to the database server. We can see the definition of beginTransactionQuery in org.postgresql.core.v3.QueryExecutorImpl itself. Here is the definition:
private final SimpleQuery beginTransactionQuery =
new SimpleQuery(
new NativeQuery("BEGIN", new int[0], false, SqlCommand.BLANK),
null, false);
From the above definition, we can see the beginTransactionQuery represents the BEGIN command in database server.
What if we commit the transaction when autoCommit is true? Let’s change our sample class file like this:
try {
conn.setAutoCommit(true);
Statement st = conn.createStatement();
st.executeQuery("insert into user_info (id, name, age) values (10, 'weli', 16);");
} catch (PSQLException e) {
if (e.getSQLState().equals(PSQLState.NO_DATA.getState())) {
// expected
}
} finally {
conn.commit();
conn.close();
}
The above code set the auto commit mode to true, and then the connection will call the commit() method finally. Let’s recompile the class and run it, and here is the output:
$ java -cp ~/.m2/repository/org/postgresql/postgresql/42.0.1-SNAPSHOT/postgresql-42.0.1-SNAPSHOT.jar:. DirectConnection
Exception in thread "main" org.postgresql.util.PSQLException: Cannot commit when autoCommit is enabled.
at org.postgresql.jdbc.PgConnection.commit(PgConnection.java:755)
at DirectConnection.main(DirectConnection.java:36)
From the above code, we can see an error is thrown, and the message says it cannot commit when autoCommit is enabled. This is reasonable, because when autoCommit is set to true, then the JDBC driver won’t send BEGIN command to the server, and it’s meaningless to send a COMMIT command by commit() method.
This behavior is defined in org.postgresql.jdbc.PgConnection class:
public void commit() throws SQLException {
checkClosed();
if (autoCommit) {
throw new PSQLException(GT.tr("Cannot commit when autoCommit is enabled."),
PSQLState.NO_ACTIVE_SQL_TRANSACTION);
}
if (queryExecutor.getTransactionState() != TransactionState.IDLE) {
executeTransactionCommand(commitQuery);
}
}
From the above code, we can see the autoCommit flag will be checked in commit() method.