分布式事务之数据库锁

问题描述

最近有个项目功能需求是需要我们获取一条mysql的记录，对其处理，处理完后再写回es和mysql，然后更新该msyql记录，因为我们是多用户并发操作，在此处理过程中需要严格保证只容许一个线程一个时候占用。即需要在mysql和elasticsearch数据库间保证分布式数据的原子性。那该如何做呢？

具体问题业务描述

具体需求是mysql数据库中存在一张表学生游戏积分表，积分表的每条记录对应该学生玩的游戏和该游戏产生的积分总和，如下：

Es中我们还有一张表是该学生玩该游戏具体每天产生的积分和描述，如下：

{student_id:1,game_name:”dota”,time:”2019-06-22”,score:27…} {student_id:1,game_name:”dota”,time:”2019-06-21”,score:2…} {student_id:1,game_name:”dota”,time:”2019-06-02”,score:230…} {student_id:1,game_name:”dota”,time:”2019-03-18”,score:13…} {student_id:2,game_name:”crossfire”,time:”2019-03-01”,score:30…} {student_id:3,game_name:”gun_game”,time:”2017-02-29”,score:94…}

 现在学生玩家希望每次登录就能看到自己的历史积分成绩（玩家等级），然后将玩家的游戏等级和其盆友的等级按等级排序出来显示出来。如下图所示：

业务分析

从后台程序员的业务角度来看，学生具体的游戏积分在es中一个叫detail的表中，如果每次玩家登陆开始统计积分就需要从es表中对ta/盆友分组，然后分组内的成绩求和。因为所有记录都保存在同一个表中，因此需要对detail表进行扫描然后分组求和，用户少的情况下，可以支撑业务需求，但学生玩家通常统一在网上8点左右登录，也就是在这个时候点达到峰值，如果一秒几百个用户同时进入，es将同时扫描全表做聚合排序，会很慢，因此解决的办法时在数据入库时将统计结果统计后更新统计结果至scores字段中，这样就对性能有很多改善，现在的问题来了，如何保证es将累计积分更新至mysql过程中，别的程序不会同时更新了该行表字段？

解决方案

这个时候自然想到的是分布式锁，分布式锁能保证一个时候多个用户只能对同一个资源访问，常见的实现分布式锁有数据库、redis、zookeeper。考虑到我们每次都是对一个学生玩家更新，因此采用数据库的行锁将满足我们的要求， 相对于myisam来说，innoDB 是 MySQL 上第一个提供外键约束的数据存储引擎，除了提供事务处理外，InnoDB 还支持行锁，行锁的使用方式为：

Select * from student_game where id=3 for update;

不过需要注意的是行锁不是指对记录，而是对索引枷锁，如上面的id设为索引，同时，设为索引的字段值不应该重复多的，否则加了行锁就可能跟加表锁效果一样了，这里我们选择innodb作为student_game表的检索引擎，学号id作为默认索引。

  这样我们的业务代码大概是这样的：

conn.setAutoCommit(false);//关闭自动提交
select scores from test.student where id= 4 for update；//上锁
//获取学生id,scores数据，查询es记录，等到总分。
//更新学生得分表scores记录
Conn.commit;//释放锁

测试流程

测试简单起见，假设玩家没玩一天得一分，则意味着向es每写一条记录，将在game_score表的特定用户id的成绩加1，下面是测试流程：

@Test
    public void testQuery4(){
        for(int i=0;i<20;i++) {
            new Thread(){
                @Override
                public void run() {
                    super.run();
                    Connection conn = MysqlUtil.getConn();
                    MysqlUtil.execute(conn,3);
                    try {
                        conn.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
            }.start();
        }
}

上述测试，我们将同时启动20个线程同时向es中写入记录，写完后跟新game_score数据表的玩家得分。

加锁前并发读写

public static void execute(Connection conn, int id) {
        try {
            Statement statement = conn.createStatement();
            ResultSet rs = statement.executeQuery(lockStirng);
            int sum = 0;
            while (rs.next()) {
                sum = rs.getInt("scores");
                System.out.println("玩家"+id+"目前总得分:"+sum
);//打印玩家的得分
            }
            rs.close();
            statement.execute("update test.student_score set scores = " + (sum + 1) + " where id=" + id);//更新玩家得分
            statement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

当我们没有设置行锁的情况下，每个用户向es插入记录后同时根据mysql表得到如下结果：

玩家3目前总得分:0
玩家3目前总得分:0
玩家3目前总得分:0
玩家3目前总得分:0
玩家3目前总得分:1
玩家3目前总得分:1
玩家3目前总得分:1
玩家3目前总得分:1
玩家3目前总得分:1
玩家3目前总得分:1
玩家3目前总得分:1
玩家3目前总得分:1
玩家3目前总得分:1
玩家3目前总得分:1
玩家3目前总得分:1
玩家3目前总得分:1
玩家3目前总得分:1
玩家3目前总得分:1
玩家3目前总得分:2
玩家3目前总得分:2

加锁后并发读写

设置行锁：

public static void executeTest3(Connection conn, int id) {
        String queryStirng = "select scores from test.game_score where id= "+id+" for update";
        try {
            conn.setAutoCommit(false);
            boolean isGetLock = false;
            Statement statement = conn.createStatement();
            while (!isGetLock) {
                if (!isGetLock) {
                    ResultSet rs = statement.executeQuery(queryStirng);
                    isGetLock=true;
                    int sum=0;
                    while (rs.next()) {
                        sum = rs.getInt("scores");
                        System.out.println("玩家"+id+"目前总得分:"+sum);
                    }
                    rs.close();
                    statement.execute("update test.game_score set scores = " + (sum + 1) + " where id="+id);
                    statement.close();
                } else try {
                    Thread.sleep(20);
                } catch (InterruptedException e) {
                    e.printStackTrace();
                }
            }
            conn.commit();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

得到结果为：

玩家3目前总得分:0
玩家3目前总得分:1
玩家3目前总得分:2
玩家3目前总得分:3
玩家3目前总得分:4
玩家3目前总得分:5
玩家3目前总得分:6
玩家3目前总得分:7
玩家3目前总得分:8
玩家3目前总得分:9
玩家3目前总得分:10
玩家3目前总得分:11
玩家3目前总得分:12
玩家3目前总得分:13
玩家3目前总得分:14
玩家3目前总得分:15
玩家3目前总得分:16
玩家3目前总得分:17
玩家3目前总得分:18

总结

结果说明通过数据库行锁可以可以达到分布式锁的目的，这里留个问题给大家思考，如果某个app在获取mysql记录，加完行锁后因为某种原因非正常退出，此时该app并未释放行锁，这样导致其他读写包含该行的所有app无限等待，此时该如何处理？