Java - a lock could not be obtained within the time requested after exception

Question

here is the thing:

• I have a DB that serves as a scoreborad;

• I have a button that updates the DB with the new scores;

• I have a "developer exception" that throws a SQLException whenever the score is low and can't replace any score in the scoreboard (DB);

The problem:

• I get a low score and the exception occur, then I get a high score and instead of updating the DB I get the error.

Here is the error:

• "a lock could not be obtained within the time requested"

Here is the code in the button:

try
    {
        Connection conec=DriverManager.getConnection(hostname,username,password); //<-- Conecçao com a base de dados
        Statement stm=conec.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE); //<-- Conecção com as tabelas
      //------------Aquisição dos dados------------------//
        SQL="SELECT * FROM DEMOLION.MINER";
        res=stm.executeQuery(SQL);
      //-------------------------------------------------//
        res.first();
        nome=JOptionPane.showInputDialog("Introduza o mone que deseja registar:","Jogador1");
        Desktop.getDesktop().open(new File("Jogos RPG Maker/Miner mapa/Game.exe"));
        pontuacao=JOptionPane.showInputDialog("Introduza a pontuação obtida:","0");
        pontos=Integer.parseInt(pontuacao);
        do
        {
            pontos_insuf();
            dbpontos=res.getInt("PONTUACOES");
            System.out.println("pt="+pontos+"db="+dbpontos);
            if(pontos>dbpontos)
            {
                res.updateInt("PONTUACOES",pontos);
                res.updateString("NOME",nome);
                res.updateRow();
            }
            if(pontos<dbpontos || pontos==dbpontos)
            {
                res.next();
            }
        }
        while(pontos<dbpontos);
        stm.close();
        res.close();
    }
    catch (IOException ex)
    {
        Logger.getLogger(Jogos_Pacman.NewJFrame.class.getName()).log(Level.SEVERE, null, ex);
    }
    catch(SQLException e)
    {
        MsgBox.show(e.getMessage(),"AVISO", JOptionPane.INFORMATION_MESSAGE);
    }

here is the developer exception:

private void pontos_insuf() throws SQLException{
if(!res.next())
{
    throw new SQLException("Obteve uma pontuação insuficiente para superar pontuações anteriores!");
} }

Answer 1

You are creating your Statement with CONCUR_UPDATABLE which I'm guessing for your database requires a lock, but when an error is thrown you aren't releasing the lock, i.e. you're not explicitly closing the Statement.

Best case is you're at the mercy of GC for collecting the Statement and releasing the lock, worst case it never happens.

What you need to do is move these statements:

stm.close();
res.close();

into a finally block so that they happen no matter what