MySQL的for update在Java中的应用

前言

Java Web中经常出现多线程针对同一数据库进行操作的情况，如何避免多个线程同时操作同一行数据是至关重要的。
MySQL在这种情景下 可以考虑使用悲观锁，即当前只能有一个线程执行，结束了唤醒了其他线程进行处理。下述描述的是使用MySQL的select... for update在Java代码中的应用。
下述代码中将使用一个简单SpringBoot应用，采用定时任务模拟同时有2个进程修改同一行数据的情景。

环境准备

目标数据库准备

目标表test的SQL脚本test.sql

-- 建表语句
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test`  (
  `id` int(11) NOT NULL,
  `task_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `editer` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `msg` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
-- 数据插入
INSERT INTO `test` VALUES (2, 'sss', NULL, NULL);
INSERT INTO `test` VALUES (3, 'ddsdd', NULL, NULL);
INSERT INTO `test` VALUES (4, 'dsdsad', NULL, NULL);
INSERT INTO `test` VALUES (5, 'dsadsadasdw', NULL, NULL);
INSERT INTO `test` VALUES (6, 'wdwdwq', NULL, NULL);
INSERT INTO `test` VALUES (7, '2deqew', NULL, NULL);
INSERT INTO `test` VALUES (8, '222dads', NULL, NULL);

定时任务配置类QuartzConfig.java

// 源码
package com.example.demo;

import org.quartz.*;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

/**
 * @author painter
 * @version 1.0
 * @date 2019/3/27
 */
@Configuration
public class QuartzConfig {
    @Bean
    public JobDetail teatQuartzDetail(){
        return JobBuilder.newJob(TestQuartz.class).withIdentity("testQuartz").storeDurably().build();
    }

    @Bean
    public JobDetail taskQuartzDetail() {
        return JobBuilder.newJob(TaskQuartz.class).withIdentity("taskQuartz").storeDurably().build();
    }

    @Bean
    public Trigger testQuartzTrigger(){
        SimpleScheduleBuilder scheduleBuilder = SimpleScheduleBuilder.simpleSchedule()
                //设置时间周期单位秒
                .withIntervalInSeconds(5)
                .repeatForever();
        return TriggerBuilder.newTrigger().forJob(teatQuartzDetail())
                .withIdentity("testQuartz")
                .withSchedule(scheduleBuilder)
                .build();
    }

    @Bean
    public Trigger taskQuartzTrigger() {
        SimpleScheduleBuilder scheduleBuilder = SimpleScheduleBuilder.simpleSchedule()
                //设置时间周期单位秒
                .withIntervalInSeconds(5)
                .repeatForever();
        return TriggerBuilder.newTrigger().forJob(taskQuartzDetail())
                .withIdentity("taskQuartz")
                .withSchedule(scheduleBuilder)
                .build();
    }
}

上述代码创建了2个定时任务，一个为taskQuartz，另一个为testQuartz。当应用启动时，两个定时任务回同时触发，每五秒执行一次。

定时任务类TaskQuartz.java

// 源码
package com.example.demo;

import org.quartz.JobExecutionContext;
import org.quartz.JobExecutionException;
import org.springframework.scheduling.quartz.QuartzJobBean;

import java.sql.*;
import java.util.Date;

/**
 * @author painter
 * @version 1.0
 * @date 2019/3/27
 */
public class TaskQuartz extends QuartzJobBean {
    @Override
    protected void executeInternal(JobExecutionContext jobExecutionContext) throws JobExecutionException {
        String url = "jdbc:mysql://192.168.14.173:11011/ytest";
        String userName = "root";
        String password = "root";
        String sql = "select * from test where msg is null or msg = '' for update ";
        String data = "";
        try {
            // 注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            // 创建连接
            Connection connection = DriverManager.getConnection(url, userName, password);
            // 预编译sql
            PreparedStatement ps = connection.prepareStatement(sql);
            // 关闭自动提交
            connection.setAutoCommit(false);
            ResultSet rs = ps.executeQuery();
            // 修改指定记录
            while (rs.next()) {
                data = rs.getString("msg");
                if (data == null || "".equals(data)) {
                    sql = "update test set editer = 'task1', msg = 'task1修改成功' where id = " + rs.getInt("id");
                    ps.addBatch(sql);
                    System.out.println("task1 修改完成:" + rs.getInt("id"));
                }
            }
            ps.executeBatch();
            connection.commit();
            connection.setAutoCommit(true);
            rs.close();
            ps.close();
            connection.close();
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }

        System.out.println("task1 run " + new Date());
    }
}

定时任务类TestQuartz.java

// 源码
package com.example.demo;

import org.quartz.JobExecutionContext;
import org.quartz.JobExecutionException;
import org.springframework.scheduling.quartz.QuartzJobBean;

import java.sql.*;
import java.util.Date;

/**
 * @author painter
 * @version 1.0
 * @date 2019/3/27
 */
public class TestQuartz extends QuartzJobBean {
    @Override
    protected void executeInternal(JobExecutionContext jobExecutionContext) throws JobExecutionException {
        String url = "jdbc:mysql://192.168.14.173:11011/ytest";
        String userName = "root";
        String password = "root";
        String sql = "select * from test where msg is null or msg = '' for update ";
        String data = "";
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection connection = DriverManager.getConnection(url, userName, password);
            PreparedStatement ps = connection.prepareStatement(sql);
            connection.setAutoCommit(false);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                data = rs.getString("msg");
                if (data == null || "".equals(data)) {
                    sql = "update test set editer = 'task2', msg = 'task2修改成功' where id = " + rs.getInt("id");
                    ps.addBatch(sql);
                    System.out.println("task2 修改完成:" + rs.getInt("id"));
                }
            }
            ps.executeBatch();
            connection.commit();
            connection.setAutoCommit(true);
            System.out.println("task2执行查询");
            rs.close();
            ps.close();
            connection.close();
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
        System.out.println("task2 run " + new Date());
    }
}

上述两个类执行了该操作：获取表中msg为空的记录，修改editer和msg列的记录。
执行结果：


当task1修改了查询记录时，test中的记录被row lock，task2需要等待task1的更新完成释放后，才能进行查询。此时数据已经不符合task2的更新条件，因此保证了一行数据不会同时被多个线程所修改。