Transaction Support
The Simba MongoDB JDBC Driver with SQL Connector supports JDBC transactions on MongoDB 4.0 and later. When you run a JDBC transaction, the connector maps it to an equivalent MongoDB transaction, and then executes the mapped transaction on the MongoDB database. To use transactions, set the EnableTransaction property to true (see EnableTransaction) and the IgnoreTransactions property to false (see IgnoreTransactions).
Important:
- Transactions are not supported in a non-replica set environment.
-
The CREATE TABLE statement cannot be executed in a transaction under the following circumstances:
- You are using MongoDB 4.0 or later
- And
EnableTransaction=true - And
IgnoreTransactions=false
When auto-commit is set to true, the values of ReadPreference and WriteConcern are set to the values provided in the connection URL.
When auto-commit is set to false, the values in the connection string are overridden:
- The ReadPreference value is set to primary.
- The WriteConcern value is set according to the transaction isolation used (refer to the table below).
The ReadConcern and WriteConcern values depend on the transaction isolation used:
| Transaction Isolation | ReadConcern | WriteConcern |
|---|---|---|
| TRANSACTION_READ_UNCOMMITTED | local | 1 |
| TRANSACTION_READ_COMMITTED | snapshot | majority |
| TRANSACTION_REPEATABLE_READ | snapshot | majority |
| TRANSACTION_SERIALIZABLE | snapshot | majority |
Important:
ReadConcern is not exposed in the connection string.
When a write conflict or network error is returned, the connector throws a SQLTransientException during the transaction. For example, the following transaction has a write conflict:
| process 1 (auto-commit off) | process 2 (auto-commit on) |
|---|---|
| start transaction | |
| modify row 1 | |
| modify row 1 | |
| (write conflict) |
In this case, the transaction in process 1 can be retried by implementing a retry logic. The following is a simple implementation for retrying a transaction that failed with a SQLTransientException:
public void retryTransientError()
{
Connection conn = null;
try
{
conn = DriverManager.getConnection(...);
PreparedStatement updateStmt = conn.prepareStatement("update customer set address = ? where _id = ?");
while (true)
{
try
{
conn.setAutoCommit(false);
updateStmt.setString(1, "123 main st");
updateStmt.setString(2, "58b74413c66a6160b7bfe5be");
updateStmt.addBatch();
updateStmt.setString(1, "123 west 4th ave");
updateStmt.setString(2, "58b74413c66a6160b7bfe5ba");
updateStmt.addBatch();
updateStmt.executeBatch();
System.out.println("commit");
conn.commit();
}
catch (SQLTransientException e)
{
System.out.println("Transient error. Retry transaction...");
continue;
}
catch(Exception e)
{
conn.rollback();
}
break;
}
}
catch(Exception e)
{
e.printStackTrace();
}
finally
{
if (null != conn)
{
try
{
conn.close();
}
catch(SQLException e)
{
e.printStackTrace();
}
}
}
}
When committing a transaction, and an UNKNOWN_TRANSACTION_COMMIT_RESULT_LABEL error is returned from the MongoDB Java connector third-party library, the connector automatically retries the commit. You can configure the number of retries (see MaxCommitRetries) and wait time before retrying a commit (see RetryCommitWaitTime).
Important:
Only the transaction that triggers the UNKNOWN_TRANSACTION_COMMIT_RESULT_LABEL error from the MongoDB Java connector third-party library is retried. Commits that fail for other reasons are not retried.