Introduction
This article describes the recently released open-source JDBC driver for CockroachDB. It wraps the PostgreSQL JDBC driver (pgjdbc) which in turn communicates in the PostgreSQL native network wire (v3.0) protocol with CockroachDB.
Article series on the JDBC driver:
Features
This JDBC driver adds certain features on top of pgJDBC that are relevant to CockroachDB.
Internal retries on serialization conflicts.
Internal retries on connection errors.
Rewriting qualified SQL queries to use SELECT FOR UPDATE to reduce serialization conflicts.
CockroachDB-specific database metadata and version info.
All these features are disabled by default, which means the driver is operating in a pass-through mode delegating all JDBC API invocations to the pgJDBC driver.
Enabling internal retries may reduce the need for application-level retry logic and thereby enhance compatibility with 3rd-party products that don't implement any transaction retries.
Enabling SELECT FOR UPDATE rewrites may reduce serialization conflicts from appearing in the first place and thereby reduce retries to a bare minimum or none at all, at the expense of imposing locks on every read operation.
SELECT FOR UPDATE rewrites can be scope to connection level where all qualified SELECT queries are rewritten, or to transaction level where all qualified SELECT within a given transaction are rewritten.
For more information about client-side retry logic, see also:
Getting Started
Below is an example of creating a JDBC connection and executing a simple SELECT query in an implicit transaction (auto-commit):
try (Connection connection
= DriverManager.getConnection("jdbc:cockroachdb://localhost:26257/jdbc_test?sslmode=disable") {
try (Statement statement = connection.createStatement()) {
try (ResultSet rs = statement.executeQuery("select version()")) {
if (rs.next()) {
System.out.println(rs.getString(1));
}
}
}
}
Next is an example of executing a SELECT and an UPDATE in an explicit transaction with FOR UPDATE rewrites:
try (Connection connection
= DriverManager.getConnection("jdbc:cockroachdb://localhost:26257/jdbc_test?sslmode=disable")) {
connection.setAutoCommit(false);
try (Statement statement = connection.createStatement()) {
statement.execute("SET implicitSelectForUpdate = true");
}
try (PreparedStatement ps = connection.prepareStatement("select balance from account where id=?")) {
ps.setLong(1, 100L);
try (ResultSet rs = ps.executeQuery()) {
if (rs.next()) {
BigDecimal balance = rs.getBigDecimal(1);
try (PreparedStatement ps2 = connection.prepareStatement("update account set balance = balance + ? where id=?")) {
ps2.setBigDecimal(1, new BigDecimal("10.50"));
ps2.setLong(2, 100L);
ps2.executeUpdate();
}
}
}
}
connection.commit();
}
Same as above where all qualified SELECTs are suffixed with FOR UPDATE:
try (Connection connection
= DriverManager.getConnection("jdbc:cockroachdb://localhost:26257/jdbc_test?sslmode=disable&implicitSelectForUpdate=true")) {
connection.setAutoCommit(false);
...
connection.commit();
}
Maven configuration
Add this dependency to your pom.xml file:
<dependency>
<groupId>io.cockroachdb.jdbc</groupId>
<artifactId>cockroachdb-jdbc-driver</artifactId>
<version>{version}</version>
</dependency>
Then add the Maven repository to your pom.xml file (alternatively in Maven's settings.xml):
<repository>
<id>github</id>
<name>Maven Packages</name>
<url>https://maven.pkg.github.com/cloudneutral/cockroachdb-jdbc</url>
<snapshots>
<enabled>true</enabled>
</snapshots>
</repository>
You need to authenticate to GitHub Packages by creating a personal access token (classic) that includes the read:packages scope. For more information, see Authenticating to GitHub Packages.
Add your personal access token to the servers section in your settings.xml:
<server>
<id>github</id>
<username>your-github-name</username>
<password>your-access-token</password>
</server>
Take note that the server and repository id:s must match (it can be different than github). Now you should be able to build your project with the JDBC driver as a dependency:
mvn clean install
Alternatively, you can just clone the repository and build it locally using mvn install.
Modules
The JDBC driver project is a multi-module Maven project with the following components:
cockroachdb-jdbc-driver
The main library for the CockroachDB JDBC driver. This is all you need and it transitively pulls in pgJDBC and log4j as only dependencies.
cockroachdb-jdbc-it
Integration tests and functional tests that are activated via Maven profiles.
cockroachdb-jdbc-demo
A standalone demo app to showcase the retry mechanism and other features.
Supported CockroachDB and JDK Versions
The driver is CockroachDB version agnostic and supports any version supported by the PostgreSQL JDBC driver v 42.5+ (pgwire protocol v3.0). It's built for Java 8 at the language source and target level but requires Java 17 LTS for building.
URL Properties
The driver uses the jdbc:cockroachdb: JDBC URL prefix and supports all PostgreSQL URL properties on top of that. To configure a data source to use this driver, you typically configure it for PostgreSQL and only change the URL prefix and the driver class name.
The general format for a JDBC URL for connecting to a CockroachDB server:
jdbc:cockroachdb:[//host[:port]/][database][?property1=value1[&property2=value2]...]
See pgjdbc for all supported driver properties and the semantics.
In addition, this driver has the following CockroachDB-specific properties:
retryTransientErrors
(default: false)
The JDBC driver will automatically retry serialization failures (40001 state code) at read, write or commit time. This is done by keeping track of all statements and the results during a transaction, and if the transaction is aborted due to a transient 40001 error, it will roll back and retry the recorded operations on a new connection and compare the results with the initial commit attempt. If the results are different, the driver will be forced to give up the retry attempt to preserve a serializable outcome.
Enable this option if you want to handle aborted transactions internally in the driver, preferably combined with select-for-update locking. Leave this option disabled if you want to handle aborted transactions in your application.
retryConnectionErrors
(default: false)
The CockroachDB JDBC driver will automatically retry transient connection errors with SQL state 08001, 08003, 08004, 08006, 08007, 08S01 or 57P01 at read, write or commit time.
Applicable only when retryTransientErrors is also true.
Disable this option if you want to handle connection errors in your own application or connection pool.
CAUTION! Retrying on non-serializable conflict errors (i.e anything but 40001) may produce duplicate outcomes if the SQL statements are non-idempotent. See the design notes for more details.
retryListenerClassName
(default: io.cockroachdb.jdbc.retry.LoggingRetryListener)
Name of the class that implements io.cockroachdb.jdbc.retry.RetryListener to be used to receive callback events when retries occur. One instance is created for each JDBC connection.
retryStrategyClassName
(default: io.cockroachdb.jdbc.retry.ExponentialBackoffRetryStrategy)
Name of the class that implements io.cockroachdb.jdbc.retry.RetryStrategy to be used when retryTransientErrors property is set to true. If this class also implements io.cockroachdb.jdbc.proxy.RetryListener it will receive callback events when retries happen. One instance of this class is created for each JDBC connection.
The default ExponentialBackoffRetryStrategy will use an exponentially increasing delay with jitter and a multiplier of 2 up to the limit set by retryMaxBackoffTime.
retryMaxAttempts
(default: 15)
A maximum number of retry attempts on transient failures (connection errors/serialization conflicts). If this limit is exceeded, the driver will throw a SQL exception with the same state code signalling yielding further retry attempts.
retryMaxBackoffTime
(default: 30s)
Maximum exponential backoff time in the format of a duration expression (like 12s). The duration applies to the total time for all retry attempts at transaction level.
Applicable only when retryTransientErrors is true.
implicitSelectForUpdate
(default: false)
The driver will automatically append a FOR UPDATE clause to all qualified SELECT statements within connection scope. This parameter can also be set in an explicit transaction as a session variable in which case its scope to the transaction.
The qualifying requirements include:
Not used in a read-only connection
No time travel clause (as of system time)
No aggregate functions
No group by or distinct operators
Not referencing internal table schema
A SELECT .. FOR UPDATE will lock the rows returned by a selection query such that other transactions trying to access those rows are forced to wait for the transaction that locked the rows to finish. These other transactions are effectively put into a queue based on when they tried to read the value of the locked rows. It does not eliminate the chance of serialization conflicts but greatly reduces it.
(default: false)
By default, the driver will use PostgreSQL JDBC driver metadata provided in java.sql.DatabaseMetaData rather than CockroachDB-specific metadata. While the latter is more correct, it causes incompatibilities with libraries that bind to PostgreSQL version details, such as Flyway and other tools.
Logging
This driver uses SLF4J for logging which means it's agnostic to the logging framework used by the application. The JDBC driver module does not include any logging framework dependency transitively.
Additional Examples
Plain Java Example
Class.forName(CockroachDriver.class.getName());
try (Connection connection
= DriverManager.getConnection("jdbc:cockroachdb://localhost:26257/jdbc_test?sslmode=disable&implicitSelectForUpdate=true&retryTransientErrors=true") {
try (Statement statement = connection.createStatement()) {
try (ResultSet rs = statement.executeQuery("select version()")) {
if (rs.next()) {
System.out.println(rs.getString(1));
}
}
}
}
Spring Boot Example
Configure the datasource in src/main/resources/application.yml:
spring:
datasource:
driver-class-name: io.cockroachdb.jdbc.CockroachDriver
url: "jdbc:cockroachdb://localhost:26257/jdbc_test?sslmode=disable&application_name=MyTestAppe&implicitSelectForUpdate=true&retryTransientErrors=true"
username: root
password:
Optionally, configure the data source programmatically and use the TTDDYY logging proxy:
@Bean
@Primary
public DataSource dataSource() {
return ProxyDataSourceBuilder
.create(hikariDataSource())
.traceMethods()
.logQueryBySlf4j(SLF4JLogLevel.DEBUG, "io.cockroachdb.jdbc")
.asJson()
.multiline()
.build();
}
@Bean
@ConfigurationProperties("spring.datasource.hikari")
public HikariDataSource hikariDataSource() {
HikariDataSource ds = dataSourceProperties()
.initializeDataSourceBuilder()
.type(HikariDataSource.class)
.build();
ds.setAutoCommit(false);
ds.addDataSourceProperty(PGProperty.REWRITE_BATCHED_INSERTS.getName(), "true");
ds.addDataSourceProperty(CockroachProperty.IMPLICIT_SELECT_FOR_UPDATE.getName(), "true");
ds.addDataSourceProperty(CockroachProperty.RETRY_TRANSIENT_ERRORS.getName(), "true");
ds.addDataSourceProperty(CockroachProperty.RETRY_MAX_ATTEMPTS.getName(), "5");
ds.addDataSourceProperty(CockroachProperty.RETRY_MAX_BACKOFF_TIME.getName(), "10000");
return ds;
}
To configure src/main/resources/logback-spring.xml to capture all SQL statements and JDBC API calls:
<?xml version="1.0" encoding="UTF-8"?>
<configuration>
<include resource="org/springframework/boot/logging/logback/defaults.xml"/>
<include resource="org/springframework/boot/logging/logback/console-appender.xml" />
<logger name="org.springframework" level="INFO"/>
<logger name="io.cockroachdb.jdbc" level="DEBUG"/>
<root level="INFO">
<appender-ref ref="CONSOLE"/>
</root>
</configuration>
Building
The CockroachDB JDBC driver requires Java 17 (or later) LTS but is cross-compiled to run on any platform for which there is a Java 8 runtime.
Prerequisites
If you want to build with the regular mvn command, you will need Maven v3.x or above.
Install the JDK (Linux):
sudo apt-get -qq install -y openjdk-17-jdk
Install the JDK (macOS):
brew install openjdk@17
Clone the project
git clone git@github.com:cloudneutral/cockroachdb-jdbc.git
cd cockroachdb-jdbc
Build the project
chmod +x mvnw
./mvnw clean install
The JDBC driver jar is now found in cockroachdb-jdbc-driver/target.
Run Integration Tests
The integration tests will run through a series of contended workloads to exercise the retry mechanism and other driver features.
First, start a local CockroachDB node or cluster.
Create the database:
cockroach sql --insecure --host=localhost -e "CREATE database jdbc_test"
Then activate the integration test Maven profile:
./mvnw -P it -Dgroups=anomaly-test clean install
Test groups include:
anomaly-test - Runs through a series of RW/WR/WW anomaly tests.
connection-retry-test - Run a test with connection retries enabled.
batch-insert-test - Batch inserts load test.
batch-update-test - Batch updates load test.
See the pom.xml file for changing the database URL and other settings (under ìt profile).
Summary
This article provides instructions on how to configure and build a new open-source JDBC driver for CockroachDB. It covers parameters such as retryTransientErrors, implicitSelectForUpdate to reduce transient SQL exceptions on contended workloads. It also explains the configuration of the driver, including retry strategies, URL properties, and logging settings, as well as examples of how to configure the driver in plain Java and Spring Boot.
