Competition with thread and database inserting duplicates

Question

I have the following problem with competition 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 cannot repeat and because of the thread it is repeating. The bank is this .
------ ---- Items -------------------
id | numeroCupom | qtdItem
=====================
I can’t use auto_increment in qtyItem since if the coupon number changes the quatity of items it has to be reset. The teacher who passed this exercise said he has two ways to fix using select for Isert and another way I don’t remember, I tried with select for Insert but continued inserting duplicate qtdItem. The current code inserts something into the type bank

---

---- Items ---------------------------
id | numeroCupom | qtdItem

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

The right thing would be

---- Items ---------------------------
id | numeroCupom | 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();
    }
}   
}

Answer 1

One option is to search for the incremented value within the Insert command itself, more or less 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 any) or will block the entire table in the worst case. One way or another the competition problem is solved.

There are some "weird" things in your project, but I’m not getting into the merits because apparently it’s just an exercise with a specific focus that I don’t know what it is.