[case4]聊聊jdbc的大数据量读写相关异常的防御措施

序

本文主要研究一下在对jdbc进行大数据量读写相关异常的防御措施

读操作

一次性select大量的数据到内存，最容易出现的是OOM的异常，这个时候可以从时间和数据大小两个维度进行限制

限制数据量

1.分页查询

对于普通的功能，分页操作是必须的，也是解决这个问题最简单的方法，在相关功能实现的时候，要对生产的数据量进行提前预估，确定好相应的分页数据量。

2.maxRows

jdbc可以设置statement的maxRows，用来限制该statment能够拉取的所有数据的最大值，超过则丢弃。不同的数据的jdbc driver实现可能不一样，比如pg的jdbc driver是会将maxRows和fetchSize做比较，取最小的值做为limit参数值来去查询。

这个参数如果要对不同的sql来做通用设置，可能不是太好设置，稍微有点野蛮和暴力，可能某些某些查询出来的数据的列数不多也占用不了太多内存。需要单独设置。但是现在实际功能实现上很少直接使用jdbc，而是使用jpa或mybatis，因此具体就需要看jpa或mybatis有没有暴露这个参数值给你设置。但是对于通用的sql服务来说，非常有必要设置下maxRows，比如不超过2w等，来进行兜底的防范。

3.fetchSize

jdbc提供fetchSize参数来设置每次查询按fetchSize分批获取。不同的数据库的jdbc driver实现不一样。

比如mysql需要url设置useCursorFetch=true，且设置了statement的fetchSize，这样才真正的批量fetch，否则是全量拉取数据。在fetch模式下，executeQuery方法不会去获取第一批数据，而是在resultSet的next方法中实现。

比如pg的话在executeQuery方法默认会拉取第一批fetchSize的数据并返回，之后resultSet的next()方法根据需要再去fetch

使用fetchSize来避免OOM的话有个限制条件，就是需要自己在遍历resultSet的过程中边遍历数据，边处理数据。如果不是边遍历边处理，还是把结果集循环添加到list中返回，在不是reactive模式的编程范式下，这个fetchSize也就失去效果了，因为最后你还是在内存中堆积所有的数据集再去处理，因此终究会有OOM的风险

限制查询时间

限制时间的话，有多个维度：

1.connection的socketTimeout

这个是jdbc中最底层的连接socket的timeout参数设定，可以用来防止数据库由于网络原因或自身问题重启导致连接阻塞，这个是非常有必要设置的，一般是在连接url中设置

比如mysql

jdbc:mysql://localhost:3306/ag_admin?useUnicode=true&characterEncoding=UTF8&connectTimeout=60000&socketTimeout=60000

比如pg，pg的单位与mysql不同，mysql是毫秒，而pg是秒

jdbc:postgresql://localhost/test?user=fred&password=secret&&connectTimeout=60&socketTimeout=60

但是现在一般使用的是数据库连接池，因此这个不设置，通过设置连接池相关参数也是可以。

2.statement的queryTimeout

这个主要是设置statement的executeQuery的执行超时时间，即从client端发出查询指令到接收到第一批数据的超时时间，通常是通过timer来实现的。

但是这个在不同的数据库的jdbc driver的实现上有所不同，比如在fetch模式下mysql的executeQuery不会获取第一批数据，而pg则会顺带拉取第一批数据再返回。这个参数只有在不是fetch模式下，即一次性查询所有数据，才相对符合语义。如果是fetch模式，该超时时间限制不了后续几批数据的拉取超时，他们只能取决于connection的socketTimeout参数。

mybatis可以通过defaultStatementTimeout参数来设置该值
jpa可以通过query hit来设置

@QueryHints(@QueryHint(name = org.hibernate.jpa.QueryHints.SPEC_HINT_TIMEOUT, value = "1000"/*ms**/))
    List<DemoUser> findAll();

jdbc template可以通过参数来设置

    @Bean(name = "pgJdbcTemplate")
    public JdbcTemplate pgJdbcTemplate(
            @Qualifier("pgDataSource") DataSource dataSource) {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        jdbcTemplate.setQueryTimeout(10*1000);
        jdbcTemplate.setMaxRows(10*1000);
        return jdbcTemplate;
    }

3.transaction的timeout

在现实的编程中实现某个业务功能可能在一个事务中调用了很多个statement的查询，transaction可以以事务为单位来限制这批操作的超时间。

可以设置全局的超时时间

    @Bean
    @Qualifier("pgTransactionManager")
    PlatformTransactionManager pgTransactionManager() {
        JpaTransactionManager transactionManager = new JpaTransactionManager(pgEntityManagerFactory().getObject());
        transactionManager.setDefaultTimeout(60 /*seconds*/);
        return transactionManager;
    }

也可以在transactional注解中单独设置，比如

@Transactional(timeout=5) /**5 seconds*/
public List<DemoUser> findAll();

4.connection的占用时间

在使用连接池来进行数据库操作的时候，一般的连接池都会提供连接检测的功能，比如在borrow的时候验证下连接是否是ok的

另外还提供对连接占用的超时suspect和abandon操作，来检测连接泄露，如果上面那些操作都没有设置或(默认)设置的值太大不合理，那么这个检测就是除了socketTimeout外的兜底操作了。如果连接被借出超过指定时间未归还，则判定为连接泄露，则会强制abandon，即close掉连接，非常暴力，但也非常有用，防止线程阻塞在数据库操作最后导致服务504或502

写操作

类似fetchSize，对于大量数据的插入或更新操作，jdbc提供了batch方法，用来批量操作。因此对于大规模的数据操作时要注意内存中堆积的数据量，记得分批释放调用。比较适合使用原生的jdbc来操作，jpa的save方法还是现在内存中对接了大量对象，在flush的时候才执行批量和释放。

小结

对于jdbc的大量数据读写操作，要额外注意内存中对象的堆积，防止OOM。另外对于数据库操作的超时时间也要额外注意设置，防止服务器线程阻塞导致无法提供服务。

操作	类别	参数	备注
读	数量	pageSize	分页查询
读	数量	maxRows	限制一次或分fetch查询的所有数据量上限
读	数量	fetchSize	限制statement的query及result的next每次分批查询的大小
读	时间	connection socketTimeout	底层socket连接的读超时
读	时间	statement queryTimeout	限制statement的query超时
读	时间	transaction timeout	限制事务执行的超时时间
读	时间	connection remove abandon timeout	限制连接借用超时时间
写	数量	batch execute	分批执行

doc

• Hibernate: set default query timeout?
• 聊聊pg jdbc statement的maxRows参数
• 聊聊jdbc statement的fetchSize
• 聊聊pg jdbc的queryTimeout及next方法
• 聊聊mysql jdbc的queryTimeout及next方法
• 聊聊jdbc socketTimeout的设置
• 聊聊tomcat jdbc pool的默认参数及poolSweeper
• 聊聊jdbc的batch操作
• 聊聊jpa的batch操作的实现