多个并发事务同时竞争行锁的测试

余潇越
   某次线上故障,排查到cobar环境下,多并发事务竞争行锁导致非常严重的问题,整个cobar几乎不可用,基于以上背景,我做了一个测试,分析一下直连mysql和使用cobar两种情况下的服务情况,排查问题所在,这里发个博文记录一下。

一、测试环境

OS:Ubuntu 16.04 LTS 64位
CPU:I5-5200U
Mem:16G
SSD硬盘
所有服务器/中间件/java应用均部署在localhost

cobar:192.168.1.171:8070/dwd_shardb

直连:192.168.1.171:3306/dbtest1

二、测试结论


cobar 直连
autoCommit corba、db和应用执行速度快,没有发现行锁竞争的情况,其他读取行锁记录的select不受影响
db和应用执行速度非常快,没有发现行锁竞争的情况,其他读取行锁记录的select不受影响
手动Commit cobar已经卡死无法响应,客户端tcp连接长时间维持ESTABLISHED状态不释放,应用执行速度很慢(短时间就提示Lock wait timeout exceeded; try restarting transaction,事务随即全部被kill,500个sql线程在事务被kill后全部死亡,只剩下系统线程),发现非常严重的锁竞争情况,其他读取行锁记录的select受到影响,一条都无法执行
db和应用执行速度快,发现不严重的行锁竞争的情况,其他读取行锁记录的select不受影响
手动Commit(显式hold锁不释放) 没测试,预想情况会最糟糕
db执行速度一瞬间慢(等待Lock wait timeout 后会kill掉Lock wait的事务之后恢复正常),应用执行速度很慢(短时间就提示Lock wait timeout exceeded; try restarting transaction,事务随即全部被kill,500个sql线程在事务被kill后全部死亡,只剩下系统线程),发现非常严重的锁竞争情况,其他读取行锁记录的select不受影响

三、测试数据

查看的指标:

1、mysql> show processlist;查看数据库连接,如果控制台已经没有响应了即卡死就说明严重的性能问题(如corba就卡死)
2、netstat an|grep 3306(8070为cobar)查看tcp连接,如果为TIME_WAIT表示正常(客户端连接已经释放,经过2个MSL(Maximum Segment Lifetime)时间连接会断掉,linux下1个MSL大概30s);如果事务长时间ESTABLISHED表示可能发生锁等待
3、jvm应用的不同时间段的stack分析,可以看出sql事务线程的消亡和状态情况

DB直连-autocommit

db的processlist速度很快,500个事务很快执行以至于看不到事务连接
500个tcp连接状态为TIME_WAIT,表示客户端连接已经释放,2个MSL之后,3306端口连接恢复正常

DB直连-手动Commit

这个和autocommit相比,processlist能够看到一些正在执行的sql事务(说明锁竞争,但很快就全部执行完成),然后短时间之后连接数恢复正常, tcp连接情况和autocommit差不多,先是一堆的TIME_WAIT的连接,马上就消失

DB直连-手动Commit(显式hold锁不释放)

首先db processlist中query下state为updating的连接暴增,这在autocommit方式下根本看不到 同时看db端口连接情况,和前面不通的是状态全部是ESTABLISHED,说明这些连接还在运行,并没有释放 jvm应用马上抛出异常提示Lock timeout exceeded
这个Lock timeout exceeded提示之后,db的processlist和netstat恢复正常,说明db层面有一个机制或者参数,至少对于长时间holding lock的事务是kill了 然后看一下jvm的线程栈分析,超时之后,所有的sql事务线程死亡,只剩下系统线程在运行

cobar-autocommit

首先我们要知道cobar实现了db server针对db client的一些协议,对应用和command而言,它表现为一个mysql,但实际执行都是传递到后端真正的db去执行,所以在查看cobar指标的同时,也要看cobar和后端db的连接情况,在cobar-autocommit情况下,执行速度很快,cobar-autocommit和后台db维护了相同的连接,所以在cobar-autocommit和后台db能看到相同的连接,它们的processlist都是显示相同的具体连接和数目,并在一段时间之内保持 不同的是jvm应用连接的是cobar,cobar的jvm tcp连接首先很快执行完,先是大量的TIME_WAIT,然后恢复正常(注意端口是8070,cobar-server的网络连接) 随后jvm应用的客户端连接全部结束并断开 但是对于db而言,它的网络连接情况是这样的 它从开始就是建立了和cobar的长连接,并维护了一段时间的ESTABLISHED状态后恢复正常,恢复正常的瞬间,cobar和db的processlist也同时恢复到正常情况,表明cobar和db之间建立的长连接已经释放

cobar-手动Commit

在这种情况下,cobar的控制台已经出现卡死现象,并长时间没有反映 而后台db的控制台响应正常 网络连接情况,和autocommit方式不同的是,jvm应用的事务连接状态不是马上执行完之后的TIME_WAIT状态,而是大量的ESTABLISHED状态,并长时间保持这种状态 并且JVM应用客户端已经抛出java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction例外了,说明对于db-手动commit没问题的情况下,cobar已经处理不过来了,系统已经无法正常响应,最要命的是,其他select事务也收到影响,无法执行了

cobar-手动Commit(显式hold锁不释放)

不用测试了,就是对于db-手动Commit(显式hold锁不释放)这种情况,单纯的db都已经爆出严重的性能和响应问题

四、测试总结

1、“显式hold锁不释放”这种情况是刻意为之,真正很难发生,但是在高并发、长事务的场景下,很有可能会发生,这种情况就是数据库直接也会发生严重的积压和性能问题
2、cobar对于非autocommit方式下多个并发事务同时竞争行锁表现糟糕,比直连数据库要糟糕很多
3、autocommit方式(也就是一个sql一个事务)多事务竞争行锁,对于db和cobar而言,貌似都不存在锁竞争的问题,可能是db执行单sql事务速度非常快,就算是多事务并发竞争,也很难出现锁等待的情况
4、如果一定要用事务,尽量将事务缩小,并设置多个savepoint
5、数据库层面做好最后一道防线,对于持锁的事务一段时间直接kill

五、测试代码

最后贴一下测试代码

package com.dianwoba.test;

import java.sql.Connection;  
import java.sql.DriverManager;  
import java.sql.ResultSet;  
import java.sql.Statement;

public class Concurrency {  
    private static final String DB_URL = "jdbc:mysql://192.168.1.171:3306/dbtest1?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull";
    private static final String DB_USER = "xxx";
    private static final String DB_PASS = "xxx";
    private static final String COBAR_URL = "jdbc:mysql://192.168.1.171:8070/dwd_shardb?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull";
    private static final String COBAR_USER = "xxx";
    private static final String COBAR_PASS = "xxx";

    private static final String TYPE_COBAR = "cobar";
    private static final String TYPE_DB = "db";

    private static final String SQL_UPDATE = "update rider SET balance = balance + -5.00 where id = 344";
    private static final String SQL_SELECT = "select balance from rider where id = 344";

    /**执行更新语句,注意manualHoldingLock参数手动holding lock不释放*/
    public static int executeUpdate(String sql, String objectType, boolean autoCommit, boolean manualHoldingLock)
    {
        Connection conn = null; Statement stmt = null; int result = 0;
        try
        {
            if (objectType.equals(TYPE_COBAR)) {
                conn = DriverManager.getConnection(COBAR_URL, COBAR_USER, COBAR_PASS);
            }
            else {
                conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASS);
            }
            if (!autoCommit)
                conn.setAutoCommit(false);

            stmt = conn.createStatement();
            result = stmt.executeUpdate(sql);

            if(manualHoldingLock)
            {
                System.out.println("holding line lock until chars inputed...");
                System.in.read();
            }
            if (!autoCommit)
                conn.commit();
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            try{if(null != stmt)stmt.close();}catch(Exception e){}
            try{if(null != conn)conn.close();}catch(Exception e){}
        }

        return result;
    }


    public static void executeQuery(String sql, String objectType)
    {
        Connection conn = null; Statement stmt = null; int result = 0;
        try
        {
            if (objectType.equals(TYPE_COBAR)) {
                conn = DriverManager.getConnection(COBAR_URL, COBAR_USER, COBAR_PASS);
            }
            else {
                conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASS);
            }

            stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql);

            if(rs.next())
            {
                System.out.println("locking line's balance is:" + rs.getFloat("balance"));
            }
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            try{if(null != stmt)stmt.close();}catch(Exception e){}
            try{if(null != conn)conn.close();}catch(Exception e){}
        }
    }

    public static void main(String[] args) throws Exception{
        /**
         * args[0]:objectType(cobar or db)
         * args[1]:autoCommit(true or false)
         * args[2]:manual holding lock(true or false)
         */
        if (args == null || args.length != 3) {
            System.out.println("incorrect params input.");
            return;
        }
        Class.forName("com.mysql.jdbc.Driver");

        final String objectType = args[0]; 
        final boolean autoCommit = Boolean.parseBoolean(args[1]);
        final boolean manualHoldingLock = Boolean.parseBoolean(args[2]);

        //手动显式lock
        if (manualHoldingLock) {
            Thread t1 = new Thread(new Runnable(){
                @Override
                public void run() {
                    executeUpdate(SQL_UPDATE, objectType, autoCommit, true);
                    System.out.println("this thread must holding line lock");
                }
            });
            t1.start();
            t1.join(1000);//main线程等待t1开始执行后继续执行
        }

        //启动其他竞争行锁线程
        for (int i = 0; i < 500; i++) {
            new Thread(new Runnable(){
                @Override
                public void run() {
                    executeUpdate(SQL_UPDATE, objectType, autoCommit, false);
                }
            }).start();
        }   

//启动其他读取行记录的select事务
        for (int i = 0; i < 500; i++) {
            System.out.println(i);
            executeQuery(SQL_SELECT, args[0]);
            Thread.sleep(100);
        }
    }
}