在多线程下不能操作数据库么？

Question

我这边有个项目，是使用客户端socekt 连接服务获取数据并保存到数据库，但是由于是一个客户端连接多个服务，目前只想到了多线程，但是在线程下一直无法保存数据会是什么原因导致的

private JdbcTemplate jdbcTemplate = null;
    private InputStream in;
    private Socket socket = null;
    private String ip = null;
    private int port = 0;
    private ProductionDataRepository repository;
    private JSONObject json = null;
    private ProductionData SelectProdata = null;
    private int connect_Info_id = 0;
    private int a = 1;
    private int b = 2;
    public SaveDataThread(String ip,int port,ProductionDataRepository repository,ProductionData SelectProdata,int connect_Info_id,JdbcTemplate jdbcTemplate) throws UnknownHostException, IOException {
        this.port = port;
        this.ip = ip;
        this.repository = repository;
        this.SelectProdata = SelectProdata;
        this.connect_Info_id = connect_Info_id;
        this.jdbcTemplate = jdbcTemplate;
        socket = new Socket(ip, port);
    }
    @Transactional(rollbackOn=Exception.class)
    public synchronized void run() {
        
        System.out.println("线程-" + Thread.currentThread().getId());
        System.out.println(ip);
        System.out.println(port);
        
        ProductionData Prodata = null;
        boolean connectStatus = true;
        while(connectStatus) {
            try {
                if(socket.getInputStream() == null) {
                    continue;
                }
                in = socket.getInputStream();
            } catch (IOException e2) {
                // TODO Auto-generated catch block
                e2.printStackTrace();
            }
            try {
                socket.sendUrgentData(0xFF);
            } catch (IOException e1) {
                // TODO Auto-generated catch block
                e1.printStackTrace();
                socket = null;
                connectStatus = false;
            }
            byte[] data=new byte[1024];
            int len = 0;
            try {
                len = in.read(data);
            } catch (IOException e) {
                System.out.println("ProductionDataServices的save方法中的in.read(data)调用异常，异常原因："+e.getMessage());
                e.printStackTrace();
            }
            System.out.println(new String(data,0,len));
            json = JSONObject.fromObject(new String(data,0,len));
            /*json = getPorductionData(as.get("ip").toString(),Integer.parseInt(as.get("port").toString()));*/
            if(json == null) {
                System.out.println("json对象为 null,未获取到数据！");
                continue;
            }
            //查询生产数据id 等于 production_data_id的数据
            //获取

            //如果当天存在数据则对数据进行修改
            String state = null;
            if(SelectProdata != null) {
                 // 0 暂停 1 运行 2 未连接
                if(json.getInt("State") == 0) {
                    state = "暂停";
                } else if(json.getInt("State") == 1) {
                    state = "运行";
                }
                //判断是否为空，防止未连接时存在对象但是开始时间为空
                if(SelectProdata.getStartTime() == null) {
                    repository.updateStart_time(new Timestamp(System.currentTimeMillis()),SelectProdata.getId());
                }
                
                
                    System.out.println();
                    int OK = SelectProdata.getOk()+json.getInt("OK");
                    System.out.println("对象ng:    "+SelectProdata.getOk());
                    System.out.println("json对象NG:    "+json.getInt("OK"));
                    System.out.println(""+SelectProdata.getOk()+"+"+json.getInt("OK")+"="+(SelectProdata.getOk()+json.getInt("OK")));
                    System.out.println("int总和ng:    "+OK);
                    SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");//设置日期格式
                    String sql = "update ss_production_data pd set "
                            + "pd.ok = "+(SelectProdata.getOk()+json.getInt("OK"))+","
                            + "pd.ng = "+(SelectProdata.getNg()+json.getInt("NG"))+","
                            + "pd.total = "+(SelectProdata.getOk()+json.getInt("OK")+SelectProdata.getNg()+json.getInt("NG"))+","
                            + "pd.state = '"+state+"',"
                            + "pd.owe_hole = "+(SelectProdata.getOweHole()+json.getInt("Hole"))+","
                            + "pd.long_hair = "+(SelectProdata.getLongHair()+json.getInt("LongHair"))+","
                            + "pd.Long_short_hair="+(SelectProdata.getLongShortHair()+json.getInt("UnevenHair"))+","
                            + "pd.loose_wool="+(SelectProdata.getLooseWool()+json.getInt("LooseHair"))+","
                            + "pd.implantation_error ="+(SelectProdata.getImplantationError()+json.getInt("WrongHair"))+","
                            + "pd.difference_hair = "+(SelectProdata.getDifferenceHair()+json.getInt("CrushHair"))+","
                            + "pd.dirty= "+(SelectProdata.getDirty()+json.getInt("Dirt"))+","
                            + "pd.end_time = '"+df.format(new Date())+"'"
                            + "where pd.id = "+SelectProdata.getId()+"";
                    
                    
                    save(sql);
                    
                    
                            
                
                continue;
            }
            
            
                Prodata = new  ProductionData();
                //否则因为不存在id修改将变成添加
                Prodata.setName(connect_Info_id+"号机器");//机器名称
                Prodata.setConnectInfoId(connect_Info_id);//绑定连接信息id
                Prodata.setOk(json.getInt("OK"));//合格
                Prodata.setNg(json.getInt("NG"));//不合格
                Prodata.setTotal((json.getInt("OK")+json.getInt("NG")));//总数
                // 0 暂停 1 运行 2 未连接
                if(json.getInt("State") == 0) {
                    Prodata.setState("暂停");
                } else if(json.getInt("State") == 1) {
                    Prodata.setState("运行");
                }
                Prodata.setOweHole(json.getInt("Hole"));//欠孔
                Prodata.setLongHair(json.getInt("LongHair"));//长毛
                Prodata.setLongShortHair(json.getInt("UnevenHair"));//长短毛
                Prodata.setLooseWool(json.getInt("LooseHair"));//散毛
                Prodata.setImplantationError(json.getInt("WrongHair"));//植错毛
                Prodata.setDifferenceHair(json.getInt("CrushHair"));//差毛
                Prodata.setDirty(json.getInt("Dirt"));//脏毛
                Prodata.setStartTime(new Timestamp(System.currentTimeMillis()));
                Prodata.setStartTime(new Timestamp(System.currentTimeMillis()));
                Prodata.setCreateTime(new Timestamp(System.currentTimeMillis()));
                repository.save(Prodata);
            
        }
    }
    public void save(String sql) {
        String driver = "com.mysql.cj.jdbc.Driver";
        String url = "jdbc:mysql://127.0.0.1:3306/hair_planting_machine?serverTimezone=GMT%2B8&characterEncoding=utf-8&useSSL=false&allowPublicKeyRetrieval=true";
        String username = "root";
        String password = "123456";
        Connection conn = null;
        try {
            Class.forName(driver); //classLoader,加载对应驱动
            conn = (Connection) DriverManager.getConnection(url, username, password);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        int i = 0;
        PreparedStatement pstmt;
        try {
            pstmt = (PreparedStatement) conn.prepareStatement(sql);
            i = pstmt.executeUpdate();
            System.out.println("resutl: " + i);
            pstmt.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

Answer 1

你这个代码有问题，首先你想多线程这个没毛病，但是你代码写的有毛病，如果你项目至少包含dao-service-other多层的话，我觉得事务应该在service层控制，而多线程操作应该放到其他层，通过其他层调用事务方法，要不然的话，事务会出现问题。具体会产生什么问题还得看你代码具体是咋整的。
一般的问题就是切面和事务范围问题，你用注解的方式@Transactional这是一个代理方法，代理方法退出并不代表着事务的提交，你一定要清楚这一点，spring在处理的时候，并不是用的你编写程序的这套代码，而是根据你写的，生成了一个代理对象。运行的是代理对象。你这个代码有严重问题，我给你提供一个思路。
以下代码是伪代码，参考用。

// 多线程处理类
@Component
class MultiThreadProcess {
    @Autowried
    private Service service;
    run() {
        // 这里负责你业务处理，然后调用Service里面的事务方法处理
        service.saveTheObject(obj)
    }
}

@Service
class Service {
    @Autowried
    private Dao dao;
    
    // 这个方法执行完毕并不代表事务会提交，只有等真的代理对象中代理这个方法的方法执行完毕才会提交事务
    @Transactional
    saveTheObject(obj) {
        dao.save(obj);
    }
}

@Repository
class Dao {
    save(obj) {
        repository.save(obj)
    }
}