Mybatis的timeout和Db2的lock timeout

环境

• Ubuntu 22.04
• IntelliJ IDEA 2022.1.3
• JDK 17.0.3
• MyBatis 3.5.10
• Db2 v11.5.0.0

准备

创建Maven项目 test0701 。

修改 pom.xml ，添加依赖以及设置静态资源：

    ......
    <dependencies>
        <!-- https://mvnrepository.com/artifact/junit/junit -->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.13.2</version>
            <scope>test</scope>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.10</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/com.ibm.db2/jcc -->
        <dependency>
            <groupId>com.ibm.db2</groupId>
            <artifactId>jcc</artifactId>
            <version>11.5.7.0</version>
        </dependency>
    </dependencies>

    <build>
        <resources>
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*.xml</include>
                </includes>
                <filtering>false</filtering>
            </resource>
            <resource>
                <directory>src/main/resources</directory>
                <includes>
                    <include>**/*.xml</include>
                </includes>
                <filtering>false</filtering>
            </resource>
        </resources>
    </build>
    ......

添加POJO MyObject.java ：

package pojo;

public class MyObject {
    private int c1;
    private int c2;

    public int getC1() {
        return c1;
    }

    public void setC1(int c1) {
        this.c1 = c1;
    }

    public int getC2() {
        return c2;
    }

    public void setC2(int c2) {
        this.c2 = c2;
    }

    @Override
    public String toString() {
        return "MyObject{" +
                "c1=" + c1 +
                ", c2=" + c2 +
                '}';
    }
}

添加Mapper Test0701Mapper.java 和 Test0701Mapper.xml ：

package dao;

import pojo.MyObject;

import java.util.List;

public interface Test0701Mapper {
    public List<MyObject> read();
}

<?xml version="1.0" encoding="UTF-8" ?>

<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="dao.Test0701Mapper">
    <select id="read" resultType="pojo.MyObject">
        select * from t1 with rr
    </select>
</mapper>

注：这里使用了 with rr ，是为了方便产生锁竞争。

在 resources 目录下，创建 mybatis-config.xml 文件如下：

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">

<configuration>
    <environments default="db2">
        <environment id="db2">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.ibm.db2.jcc.DB2Driver"/>
<!--                <property name="driver" value="com.mysql.jdbc.Driver"/>-->
                <property name="url" value="jdbc:db2://localhost:50000/sample"/>
                <property name="username" value="db2inst1"/>
                <property name="password" value="passw0rd"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <mapper class="dao.Test0701Mapper"/>
    </mappers>

</configuration>

接下来在 src/main/test 目录下创建测试 Test0701.java ：

    ......
    @Test
    public void test1() throws IOException {
        String resource = "mybatis-config.xml";

        InputStream inputStream = Resources.getResourceAsStream(resource);

        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        SqlSession sqlSession = sqlSessionFactory.openSession(true);

        try {
            Test0701Mapper mapper = sqlSession.getMapper(Test0701Mapper.class);

            List<MyObject> list = mapper.read();
            list.forEach(System.out::println);
        } finally {
            sqlSession.close();
        }
    }
    ......

运行测试，如下：

MyObject{c1=1, c2=111}
MyObject{c1=2, c2=222}

timeout

测试1：不设置timeout

在Db2命令行，运行 db2 +c "update t1 set c2 = c2 + 1 where c1 = 1" 。

再次运行测试，程序hang住了，并且会一直hang，除非在Db2命令行运行 db2 rollback 或 db2 commit ，或者强制关闭程序。

测试2：设置全局timeout

修改 mybatis-config.xml 文件，在 <configuration> 下添加如下设置：

    ......
    <settings>
        <setting name="defaultStatementTimeout" value="10"/>
    </settings>
    ......

本例中，设置了超时时间为10秒。

在Db2命令行，运行 db2 +c "update t1 set c2 = c2 + 1 where c1 = 1" 。

再次运行测试，程序hang住了，10秒钟后超时报错：

org.apache.ibatis.exceptions.PersistenceException: 
### Error querying database.  Cause: com.ibm.db2.jcc.am.SqlTimeoutException: DB2 SQL Error: SQLCODE=-952, SQLSTATE=57014, SQLERRMC=null, DRIVER=4.31.10
### The error may exist in dao/Test0701Mapper.xml
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: select * from t1 with rr
### Cause: com.ibm.db2.jcc.am.SqlTimeoutException: DB2 SQL Error: SQLCODE=-952, SQLSTATE=57014, SQLERRMC=null, DRIVER=4.31.10
......

测试3：设置SQL timeout

删掉 mybatis-config.xml 文件中刚才添加的全局timeout设置。

在 Test0701Mapper.xml 文件中，找到：

    ......
    <select id="read" resultType="pojo.MyObject">
    ......

添加timeout属性：

    ......
    <select id="read" resultType="pojo.MyObject" timeout="15">
    ......

在Db2命令行，运行 db2 +c "update t1 set c2 = c2 + 1 where c1 = 1" 。

再次运行测试，程序hang住了，15秒钟后超时报错（消息同上）。

测试4：同时设置全局timeout和SQL timeout

如果同时设置全局timeout和SQL timeout，则后者生效。

比如全局timeout设置为10秒，SQL timeout设置为15秒，则运行测试，15秒钟后程序超时报错。

测试5： admin_get_tab_info 的单张表

创建POJO MyObject2.java 如下：

package pojo;

public class MyObject2 {
    private String tabschema;
    private String tabname;

    public String getTabschema() {
        return tabschema;
    }

    public void setTabschema(String tabschema) {
        this.tabschema = tabschema;
    }

    public String getTabname() {
        return tabname;
    }

    public void setTabname(String tabname) {
        this.tabname = tabname;
    }

    @Override
    public String toString() {
        return "MyObject2{" +
                "tabschema='" + tabschema + '\'' +
                ", tabname='" + tabname + '\'' +
                '}';
    }
}

在Mapper中添加方法如下：

    ......
    public List<MyObject2> read2();
    ......

    ......
    <select id="read2" resultType="pojo.MyObject2">
        select * from table(admin_get_tab_info('DB2INST1', 'T1'))
    </select>
    ......

创建测试：

    ......
    @Test
    public void test2() throws IOException {
        String resource = "mybatis-config.xml";

        InputStream inputStream = Resources.getResourceAsStream(resource);

        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        SqlSession sqlSession = sqlSessionFactory.openSession(true);

        try {
            Test0701Mapper mapper = sqlSession.getMapper(Test0701Mapper.class);

            List<MyObject2> list = mapper.read2();
            list.forEach(System.out::println);
        } finally {
            sqlSession.close();
        }
    }
    ......

在Db2命令行运行 db2 +c "alter table t1 add column c3 int" ，然后运行测试，如果有设置全局timeout或SQL timeout，仍然会超时报错。

测试6： admin_get_tab_info 的所有表

修改 read2 的SQL：

    ......
    <select id="read2" resultType="pojo.MyObject2" timeout="15">
        select * from table(admin_get_tab_info('DB2INST1', 'T1'))
    </select>
    ......

修改为 ：

    ......
    <select id="read2" resultType="pojo.MyObject2" timeout="15">
        select * from table(admin_get_tab_info(null, null))
    </select>
    ......

原先只是访问 DB2INST1.T1 表，现在改为遍历数据库的所有表。此时，无论是否设置了全局timeout或者SQL timeout，都不起作用。

不了解这是为什么，想来是因为timeout是从外部中断的，但是在这种情况下，Db2不响应中断。

lock timeout

LOCKTIMEOUT 是数据库cfg的配置项：

➜  ~ db2 get db cfg for sample | grep -i lock
 Max storage for lock list (4KB)              (LOCKLIST) = 4096
 Percent. of lock lists per application       (MAXLOCKS) = 10
 Interval for checking deadlock (ms)         (DLCHKTIME) = 10000
 Lock timeout (sec)                        (LOCKTIMEOUT) = -1
 Block log on disk full                (BLK_LOG_DSK_FUL) = NO
 Block non logged operations            (BLOCKNONLOGGED) = NO
 Lock timeout events                   (MON_LOCKTIMEOUT) = NONE
 Deadlock events                          (MON_DEADLOCK) = WITHOUT_HIST
 Lock wait events                         (MON_LOCKWAIT) = NONE
 Lock wait event threshold               (MON_LW_THRESH) = 5000000
 Lock event notification level         (MON_LCK_MSG_LVL) = 1

本例中， LOCKTIMEOUT 为 -1 （默认值），表示永不超时。参考 https://www.ibm.com/docs/en/db2/11.5?topic=parameters-locktimeout-lock-timeout 。

此外，在当前连接中可以查看以及设置特殊寄存器（special register） current lock timeout ：

➜  ~ db2 values current lock timeout

1          
-----------
         -1

  1 record(s) selected.

注：需要连接到DB才行。

➜  ~ db2 set current lock timeout 0
DB20000I  The SQL command completed successfully.

注：可以设置为 NULL 值，表示从数据库cfg取值。

注：current lock timeout只影响当前连接。

测试1：设置lock timeout

在Mapper中添加方法如下：

    ......
    public int getLockTimeout();

    public void setLockTimeout(int lockTimeout);
    ......

    ......
    <select id="getLockTimeout" resultType="int">
        values current lock timeout
    </select>

    <select id="setLockTimeout" parameterType="int">
        set current lock timeout ${param1}
    </select>
    ......

创建测试：

    @Test
    public void test3() throws IOException {
        String resource = "mybatis-config.xml";

        InputStream inputStream = Resources.getResourceAsStream(resource);

        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        SqlSession sqlSession = sqlSessionFactory.openSession(true);

        try {
            Test0701Mapper mapper = sqlSession.getMapper(Test0701Mapper.class);

            int lockTimeout = mapper.getLockTimeout();
            System.out.println("Before: lock timeout = " + lockTimeout + ", will set it to 5");

            mapper.setLockTimeout(5);

            try {
                List<MyObject> list = mapper.read();
                list.forEach(System.out::println);
            } finally {
                System.out.println("After: will set lock timeout back to " + lockTimeout);
                mapper.setLockTimeout(lockTimeout);
            }
        } finally {
            sqlSession.close();
        }
    }

在Db2命令行，运行 db2 +c "update t1 set c2 = c2 + 1 where c1 = 1" 。

运行测试，如下：

Before: lock timeout = -1, will set it to 5
After: will set lock timeout back to -1

org.apache.ibatis.exceptions.PersistenceException: 
### Error querying database.  Cause: com.ibm.db2.jcc.am.SqlTransactionRollbackException: DB2 SQL Error: SQLCODE=-911, SQLSTATE=40001, SQLERRMC=68, DRIVER=4.31.10
### The error may exist in dao/Test0701Mapper.xml
### The error may involve dao.Test0701Mapper.read
### The error occurred while handling results
### SQL: select * from t1 with rr
### Cause: com.ibm.db2.jcc.am.SqlTransactionRollbackException: DB2 SQL Error: SQLCODE=-911, SQLSTATE=40001, SQLERRMC=68, DRIVER=4.31.10
......

测试2： admin_get_tab_info 的所有表

该测试与上一个测试基本相同，唯一区别是改用 read2() 方法：

                ......
                List<MyObject> list = mapper.read();
                ......

修改为：

                ......
                List<MyObject2> list = mapper.read2();
                ......

在Db2命令行，运行 db2 +c "alter table t1 add column c3 int" 。

运行测试，报错消息同上。

总结

• timeout ( -952 ) ：从外部通过中断来取消，在有些情况下无效。

➜  ~ db2 "? sql952"

SQL0952N  Processing was cancelled due to an interrupt.
......

• lock timeout ( -911 )：由于锁超时而回滚事务。

➜  ~ db2 "? sql911"

SQL0911N  The current transaction has been rolled back because of a
      deadlock or timeout. Reason code "<reason-code>".
......
The reason codes are as follows:

2

         The transaction was rolled back due to a deadlock.

68

         The transaction was rolled back due to a lock timeout.
......

本例中，reason code是 68 ，表示因为锁超时而回滚事务。