I am trying to use cockroach transaction as explained in this article.
I am seeing the below error quite frequently in my log
ERROR [2017-11-02 11:49:00,855] [U:480,F:167,T:647,M:1,878] storage.config.CockroachConfigStore:[CockroachConfigStore:retryTransaction:189] - [config-store-exec-3] - Got Exception while trying to restart
org.postgresql.util.PSQLException: ERROR: restart transaction: HandledRetryableTxnError: TransactionRetryError: retry txn (RETRY_SERIALIZABLE): "sql txn" id=b0b42214 key=/Table/55/1/10000/115/319817494/"_canonical"/1509623337641/0 rw=true pri=0.05295663 iso=SERIALIZABLE stat=PENDING epo=0 ts=1509623340.835374472,1 orig=1509623340.832201525,0 max=1509623340.832201525,0 wto=false rop=false seq=2
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2284)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2003)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:200)
at org.postgresql.jdbc.PgConnection.executeTransactionCommand(PgConnection.java:768)
at org.postgresql.jdbc.PgConnection.commit(PgConnection.java:780)
at org.apache.commons.dbcp.DelegatingConnection.commit(DelegatingConnection.java:334)
at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.commit(PoolingDataSource.java:211)
at com.vnera.storage.config.CockroachConfigStore.retryTransaction(CockroachConfigStore.java:181)
at com.vnera.storage.config.CockroachConfigStore.addProperty(CockroachConfigStore.java:36)
This is what I am trying to do -
I have two tables:
CREATE TABLE config_blob (
customerid integer NOT NULL,
objecttype integer NOT NULL,
objectid bigint NOT NULL,
propertyname text NOT NULL,
timeinstant bigint NOT NULL,
updatetimestamp timestamp with time zone DEFAULT now(),
value text,
PRIMARY KEY (customerid, objecttype, objectid, propertyname, timeinstant)
);
CREATE TABLE config_versions (
customerid integer NOT NULL,
objecttype integer NOT NULL,
objectid bigint NOT NULL,
propertyname text NOT NULL,
timeinstant bigint NOT NULL,
updatetimestamp timestamp with time zone DEFAULT now() NOT NULL,
PRIMARY KEY (customerid, objecttype, objectid, propertyname, timeinstant, updatetimestamp),
INDEX config_versions_by_version (updatetimestamp)
);
At a high level in a single transaction I am doing the following:-
- Insert a value in
config_blob - If the insert in
#1is successful make an entry inconfig_versionand return
If the above transaction fail then
- Update a value in
config_blob - If update in
#1is successful update the value inconfig_version
The relevant code is like below-
@Override
protected void addProperty(Connection conn,
ConfigKey key,
String propertyName,
long timeStamp,
String escapedValue,
String tableName) throws SQLException {
try {
conn.setAutoCommit(false);
boolean insertSuccess = retryTransaction(
conn,
addPropertyInDBAsTransaction(key, propertyName, timeStamp, escapedValue, tableName)
);
if (insertSuccess) {
logger.info("Insertion of property transaction is successful");
return;
}
logger.info("Insertion of property failed. Going for update");
conn.setAutoCommit(false);
boolean updateSuccess = retryTransaction(
conn,
updatePropertyinDBAsTransaction(key, propertyName, timeStamp, escapedValue, tableName)
);
if (!updateSuccess) {
throw new SQLException("insert and update both failed.");
}
logger.info("Updated existing property - transaction successful");
} finally {
conn.close();
}
}
private RetryableTransaction addPropertyInDBAsTransaction(ConfigKey key,
String propertyName,
long timeStamp,
String escapedValue,
String tableName) throws SQLException {
return conn1 - > {
try (Statement stmt = conn1.createStatement()) {
try {
String sql = getSqlQueryForAddProperty(tableName, key, propertyName, timeStamp, escapedValue, false);
logger.info("Property Insert sql {} {}", tableName, sql);
int nrows = stmt.executeUpdate(sql);
if (nrows > 0) {
sql = String.format("insert into %s " +
" (customerId, objecttype, objectid, propertyname, timeinstant) " +
" values (%d, %d, '%d'::int8, '%s', '%d'::int8)",
config.getVersionsTableName(), key.customerId, key.objectType, key.objectId,
propertyName, timeStamp);
logger.info("Property Version Insert sql in {} {}", config.getVersionsTableName(), sql);
stmt.executeUpdate(sql);
return true;
}
} catch (SQLException e) {
logger.warn("overwriting property value!");
return false;
}
}
return false;
};
}
private int updateConfig(Statement stmt, String tableName, ConfigKey key, String propertyName, long timeStamp,
String escapedValue) throws SQLException {
String sql = getSqlQueryForAddProperty(tableName, key, propertyName, timeStamp, escapedValue, true);
logger.info("Property Update in {} {}", tableName, sql);
int nrows = stmt.executeUpdate(sql);
if (nrows > 0) {
sql = String.format("update %s set updatetimestamp = now() where " +
"customerid = %d and " +
"objectType = %d and " +
"objectid = '%d'::int8 and " +
"propertyName = '%s' and " +
"timeinstant = '%d'::int8 ;",
config.getVersionsTableName(), key.customerId, key.objectType, key.objectId, propertyName, timeStamp
);
logger.info("Property Update in {} {}", config.getVersionsTableName(), sql);
stmt.executeUpdate(sql);
}
return nrows;
}
private boolean retryTransaction(Connection conn, RetryableTransaction transaction) throws SQLException {
Savepoint sp = conn.setSavepoint("cockroach_restart");
while (true) {
try {
boolean isSucess = transaction.run(conn);
conn.commit();
logger.info("Transaction successful");
return isSucess;
} catch (SQLException e) {
if (e.getErrorCode() == 40001) {
// Signal the database that we will attempt a retry.
conn.rollback(sp);
} else {
logger.error(String.format("Got exception with error code %d", e.getErrorCode()), e);
Throwables.propagate(e);
}
}
}
}
protected String getSqlQueryForAddProperty(
String tableName,
ConfigKey key,
String propertyName,
long timeStamp,
String escapedValue,
boolean isUpdate) {
if (!isUpdate) {
return String.format("insert into %s (customerId, objecttype, objectid, propertyname, timeinstant, value) " +
" values (%d, %d, '%d'::int8, '%s', '%d'::int8, %s)",
tableName, key.customerId, key.objectType, key.objectId, propertyName, timeStamp, escapedValue
);
}
return String.format("update %s set value = %s where " +
"customerid = %d and " +
"objectType = %d and " +
"objectid = '%d'::int8 and " +
"propertyName = '%s' and " +
"timeinstant = '%d'::int8 ;",
tableName, escapedValue, key.customerId, key.objectType, key.objectId, propertyName, timeStamp
);
}
// A simple interface that provides a retryable lambda expression.
interface RetryableTransaction {
boolean run(Connection conn) throws SQLException;
}
The detailed relevant logs -
INFO [2017-11-02 11:49:00,832] [U:480,F:167,T:647,M:1,878] storage.config.CockroachConfigStore:[CockroachConfigStore:lambda$addPropertyInDBAsTransaction$2:134] - [config-store-exec-3] - Property Insert sql config_blob insert into config_blob (customerId, objecttype, objectid, propertyname, timeinstant, value) values (10000, 115, '319817494'::int8, '_canonical', '1509623337641'::int8, '{
"@class" : "com.van.model.core.InventoryData",
"objectGuid" : "[VM]-[inventory]-[10.197.17.204]",
"vendorId" : null,
"assetIdentifier" : null,
"name" : null,
"otherNames" : null,
"manager" : {
"@class" : "com.van.model.core.Reference",
"modelKey" : {
"key" : "10000:8:935316259"
},
"objectGuid" : "X.X.X.X",
"propertyName" : "_canonical",
"name" : null
},
"modelKey" : {
"key" : "10000:115:319817494"
},
"groups" : [ "java.util.HashSet", [ ] ],
"subTypes" : [ "java.util.HashSet", [ ] ],
"description" : null,
"propertyBag" : {
"@class" : "com.van.model.core.PropertyBag",
"propertyValues" : [ "java.util.HashSet", [ ] ]
},
"associationEntities" : {
"@class" : "java.util.HashMap"
},
"payload" : "ChMxMDAwMDoxMTU6MzE5ODE3NDk0EhIxMDAwMDoxOjE5ODMwMTY5NTASEjEwMDAwOjE6MTk4MzAxNjk",
"basePayload" : ""
}')
INFO [2017-11-02 11:49:00,837] [U:480,F:167,T:647,M:1,878] storage.config.CockroachConfigStore:[CockroachConfigStore:lambda$addPropertyInDBAsTransaction$2:142] - [config-store-exec-3] - Property Version Insert sql in config_versions insert into config_versions (customerId, objecttype, objectid, propertyname, timeinstant) values (10000, 115, '319817494'::int8, '_canonical', '1509623337641'::int8)
ERROR [2017-11-02 11:49:00,855] [U:480,F:167,T:647,M:1,878] storage.config.CockroachConfigStore:[CockroachConfigStore:retryTransaction:189] - [config-store-exec-3] - Got Exception while trying to restart
org.postgresql.util.PSQLException: ERROR: restart transaction: HandledRetryableTxnError: TransactionRetryError: retry txn (RETRY_SERIALIZABLE): "sql txn" id=b0b42214 key=/Table/55/1/10000/115/319817494/"_canonical"/1509623337641/0 rw=true pri=0.05295663 iso=SERIALIZABLE stat=PENDING epo=0 ts=1509623340.835374472,1 orig=1509623340.832201525,0 max=1509623340.832201525,0 wto=false rop=false seq=2
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2284)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2003)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:200)
at org.postgresql.jdbc.PgConnection.executeTransactionCommand(PgConnection.java:768)
at org.postgresql.jdbc.PgConnection.commit(PgConnection.java:780)
at org.apache.commons.dbcp.DelegatingConnection.commit(DelegatingConnection.java:334)
at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.commit(PoolingDataSource.java:211)
at com.van.storage.config.CockroachConfigStore.retryTransaction(CockroachConfigStore.java:181)
at com.van.storage.config.CockroachConfigStore.addProperty(CockroachConfigStore.java:36)
at com.van.storage.config.PSqlConfigStore$5.call(PSqlConfigStore.java:381)
at com.van.storage.config.PSqlConfigStore$5.call(PSqlConfigStore.java:376)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at com.van.common.VneraExecutors$Queue$TimedRunnable.run(VneraExecutors.java:212)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
Cockroach Version-
ubuntu@platform:~/logs$ cockroach version
Build Tag: v1.1.0
Build Time: 2017/10/12 14:50:18
Distribution: CCL
Platform: linux amd64
Go Version: go1.8.3
C Compiler: gcc 6.3.0
Build SHA-1: 8b865035e21aa4fa526ee017ba9dc685d7af649c
Build Type: release-gnu
Can some one let me know what I am doing wrong or there is some limitation on the cockroach side?
Let me know if you need any more information from my side.