Competition with thread and database inserting duplicates

1

I have the following problem with concurrency using Thread. The code below should take the last number of a column of a table and add +1 to save again in the database, the problem is that this number can not be repeated and because of the thread it is repeating itself. The bank is that.
------ ---- Items -------------------
id | numberCupom | qtdItem
=====================
I can not use auto_incremente in qtdItem since if the coupon number changes the quantity of items has to be zeroed. The teacher who passed this exercise said that there are two ways to fix using the select for isert and another way I can not remember, I tried with the select for insert but continued inserting duplicate qtdItem. The current code inserts something into the type bank

---- Items ---------------------------
id | numberCupom | qtdItem

1 | 5 | 1
2 | 5 | 2
3 | 5 | 2

The correct one would be

---- Items ---------------------------
id | numberCupom | qtdItem

1 | 5 | 1
2 | 5 | 2
3 | 5 | 3

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package concorrencia;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;



/**
 *
 * @author Francisco
 */
public class Concorrencia {


private static Connection conectar(){
    try {
        Class.forName("com.mysql.jdbc.Driver");
        return DriverManager.getConnection("jdbc:mysql://localhost/fiscal", "jp", "");
    } catch (ClassNotFoundException ex) {
        Logger.getLogger(Concorrencia.class.getName()).log(Level.SEVERE, null, ex);
    } catch (SQLException ex) {
        Logger.getLogger(Concorrencia.class.getName()).log(Level.SEVERE, null, ex);
    }
    return null;
}

private static int getProximoId(Connection con, int cupomId) throws SQLException{
    PreparedStatement stmt = con.prepareStatement("select max(nr_item) from item_cupom where cupom_id = ?");
    stmt.setInt(1, cupomId);
    ResultSet rs = stmt.executeQuery();
    try{
        if(rs.next()){
            return rs.getInt(1) + 1;
        }else{
            return 1;
        }
    }finally{
        rs.close();
        stmt.close();
    }
}

public static void inserirItem(Connection con, int cupomId, boolean demorar) throws SQLException, InterruptedException{
    int nrItem = getProximoId(con, cupomId);
    if(demorar){
        Thread.sleep(200);
    }
    PreparedStatement pstmt = con.prepareStatement("insert into item_cupom(cupom_id, nr_item) values(?, ?);");
    pstmt.setInt(1, cupomId);
    pstmt.setInt(2, nrItem);
    pstmt.execute();
    pstmt.close();
}

private static boolean seraQueVaiDemorar(){
    double valor = Math.random();
    return valor > 0.8;
}

/**
 * @param args the command line arguments
 */
public static void main(String[] args) {
    Connection con = conectar();

    for (int i = 0; i < 2; i++) {
        new Thread(){
            @Override
            public void run() {
                for(int i = 0; i < 300; i++){
                    try {
                        inserirItem(con, 5, seraQueVaiDemorar());
                    } catch (Exception ex) {
                        Logger.getLogger(Concorrencia.class.getName()).log(Level.SEVERE, null, ex);
                    }
                }
            }

        }.start();
    }
}   
}
    
asked by anonymous 28.04.2016 / 14:50

1 answer

1

One option is to search for the value incremented within the insert command itself, something like this:

insert into item_cupom(cupom_id, nr_item) 
values(?, (select max(nr_item) + 1 from item_cupom where cupom_id = ?))
MySQL (InnoDB) will block inserts that affect the same indexes (if there are indexes) or will lock the entire table in the worst case scenario. One way or another, the competition problem is solved.

There are some "weird" things in your project, but I will not go into merit because it's apparently just an exercise with a specific focus that I do not know what it is.

    
28.04.2016 / 15:45