[Amazon](500363) Error: Transaction is read-only

记一次老项目异常的分析与解决:

### Error querying database.  Cause: java.sql.SQLException: [Amazon](500363) Error: Transaction is read-only.
### The error may exist in com/hp/printer/redshift/maintenanceMask-sqlmap.xml
### The error may involve MaintenanceMask.GetTableAllData
### The error occurred while executing a query
### SQL: SELECT                   ID, PRINTER_FAMILY,MAINTENANCE_NAME,START_VERSION,END_VERSION, COMMENTS, DELETE_FLAG, INSERT_TS, UPDATE_TS               FROM app_bm_graphics_lf_telemetry.MAINTENANCE_MASK WHERE DELETE_FLAG='N' ORDER BY
PRINTER_FAMILY ASC
### Cause: java.sql.SQLException: [Amazon](500363) Error: Transaction is read-only.
; uncategorized SQLException for SQL []; SQL state [HY000]; error code [500363]; [Amazon](500363) Error: Transaction is read-only.; nested exception is java.sql.SQLException: [Amazon](500363) Error: Transaction is read-only.] with root c
ause
        com.amazon.support.exceptions.GeneralException: [Amazon](500363) Error: Transaction is read-only.
                at com.amazon.redshift.dataengine.PGEscaper.checkReadOnly(Unknown Source)
                at com.amazon.redshift.dataengine.PGDataEngine.prepare(Unknown Source)
                at com.amazon.jdbc.common.SPreparedStatement.<init>(Unknown Source)
                at com.amazon.jdbc.jdbc41.S41PreparedStatement.<init>(Unknown Source)
                at com.amazon.jdbc.jdbc42.S42PreparedStatement.<init>(Unknown Source)
                at com.amazon.redshift.core.jdbc42.PGJDBC42PreparedStatement.<init>(Unknown Source)
                at com.amazon.redshift.core.jdbc42.PGJDBC42ObjectFactory.createPreparedStatement(Unknown Source)
                at com.amazon.jdbc.common.SConnection.prepareStatement(Unknown Source)
                at com.amazon.jdbc.common.SConnection.prepareStatement(Unknown Source)
                at org.apache.commons.dbcp2.DelegatingConnection.prepareStatement(DelegatingConnection.java:293)
                at org.apache.commons.dbcp2.DelegatingConnection.prepareStatement(DelegatingConnection.java:293)
                at org.apache.ibatis.executor.statement.PreparedStatementHandler.instantiateStatement(PreparedStatementHandler.java:79)
                at org.apache.ibatis.executor.statement.BaseStatementHandler.prepare(BaseStatementHandler.java:88)
                at org.apache.ibatis.executor.statement.RoutingStatementHandler.prepare(RoutingStatementHandler.java:58)
                at org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:76)
                at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:61)
                at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:303)
                at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:154)
                at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:134)
                at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:120)
                at sun.reflect.GeneratedMethodAccessor933.invoke(Unknown Source)
                at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
                at java.lang.reflect.Method.invoke(Method.java:498)
                at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:408)
                at com.sun.proxy.$Proxy120.selectList(Unknown Source)
                at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:214)
                at com.hp.printer.dao.MaintenanceMaskDao.getTableAllData(MaintenanceMaskDao.java:18)
                at com.hp.printer.service.MaintenanceMaskService.getTableAllData(MaintenanceMaskService.java:29)
                at com.hp.printer.service.MaintenanceMaskService$$FastClassBySpringCGLIB$$7c6d585c.invoke(<generated>)
                at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
                at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:720)
                at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
                at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:98)
                at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:262)
                at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:95)
                at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
                at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
                at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
                at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:655)
                at com.hp.printer.service.MaintenanceMaskService$$EnhancerBySpringCGLIB$$5407cd75.getTableAllData(<generated>)
                at com.hp.printer.ws.MaintenanceMaskWebService.getPrinterTableAllData(MaintenanceMaskWebService.java:33)
                at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
                at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
                at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
                at java.lang.reflect.Method.invoke(Method.java:498)
                at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:221)
                at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:136)
                at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:110)
                at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:817)
                at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:731)

spring & redshift jdbc driver 的版本

<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
		<org.springframework.version>4.2.5.RELEASE</org.springframework.version>
</properties>

...

 <!-- https://mvnrepository.com/artifact/com.amazon.redshift/redshift-jdbc42-no-awssdk -->
<dependency>
      <groupId>com.amazon.redshift</groupId>
      <artifactId>redshift-jdbc42-no-awssdk</artifactId>
      <version>1.2.41.1065</version>
</dependency>

spring事物控制

  <aop:config>
        <aop:pointcut id="serviceOperation" expression="execution(* com.hp.printer.service.*.*(..))"/>
        <aop:advisor advice-ref="transactionInterceptor" pointcut-ref="serviceOperation"/>
    </aop:config>

    <tx:advice id="transactionInterceptor" transaction-manager="transactionManager">
        <tx:attributes>
            <tx:method name="get*" read-only="true"/>
            <tx:method name="*"/>
        </tx:attributes>
    </tx:advice>

    <tx:annotation-driven transaction-manager="transactionManager"/>

查询方法

 public DataTableResponse<MaintenanceMask> getTableAllData(DataTableRequest dataTableRequest) {
        List<MaintenanceMask> list = maintenanceMaskDao.getTableAllData(dataTableRequest);
        Long count = maintenanceMaskDao.countAllData(dataTableRequest);
        DataTableResponse<MaintenanceMask> dataTableResponse = new DataTableResponse();
        dataTableResponse.setData(list);
        dataTableResponse.setDraw(dataTableRequest.getDraw());
        dataTableResponse.setRecordsTotal(count);
        dataTableResponse.setRecordsFiltered(count);
        return dataTableResponse;
    }

分析与解决

1. 项目配置了Spring管理事物，get开始的方法都会使用read-only；

2. 但由于redshift内存资源紧张，在查询表时，会创建临时表，创建临时表使用ready-only事物会报错;

3. 此段代码本身只有查询，修改方法名使其脱离Spring的事物管理；

    public DataTableResponse<MaintenanceMask> tableAllData(DataTableRequest dataTableRequest) {
        List<MaintenanceMask> list = maintenanceMaskDao.getTableAllData(dataTableRequest);
        Long count = maintenanceMaskDao.countAllData(dataTableRequest);
        DataTableResponse<MaintenanceMask> dataTableResponse = new DataTableResponse();
        dataTableResponse.setData(list);
        dataTableResponse.setDraw(dataTableRequest.getDraw());
        dataTableResponse.setRecordsTotal(count);
        dataTableResponse.setRecordsFiltered(count);
        return dataTableResponse;
    }

4. 重启Server，刷新页面， 数据正常加载，问题解决。