How to resolve Lock wait timeout exceeded exception in SQL?

Question

I am FTP'ing a file from a remote server and inserting/updating it in my db. I am using MySql db. But I am seeing java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction exception and also the row is being inserted into my db at the same time. How can I resolve this issue?.

The error from the log is below:

SEVERE: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1078)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4187)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4119)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2570)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2731)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2815)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155)
    at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2322)
    at com.sun.gjc.spi.jdbc40.PreparedStatementWrapper40.executeQuery(PreparedStatementWrapper40.java:642)
    at ft.util.KeyGenerator.generateKey(KeyGenerator.java:92)
    at ft.util.KeyGenerator.generateKey(KeyGenerator.java:44)
    at processes.FTPInbound.connectAndGetListOfFiles(FTPInbound.java:242)
    at com.washpost.main.Main.main(Main.java:24)
    at lockbox.beans.LOCKBOX_MessageBean.onMessage(LOCKBOX_MessageBean.java:63)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

The error points to rset = stmt.executeQuery (); in the below code:

package com.washpost.ft.util;

import java.io.*;
import java.util.*;
import java.sql.*;

import java.util.logging.*;


public class KeyGenerator {

  private static HashMap keyTbl = new HashMap();
  private static Vector  keyLock = new Vector();
  private final static   long  MAX_KEY_COUNT = 999999999;
  private final static   int   KEY_INC_COUNTER = 1;



  public KeyGenerator() {
  }

  public static long generateKey(String sequenceName)
                                throws Exception {
           long keyValue;

           synchronized(keyLock) {

                  String key =  sequenceName;

                  Key data = (Key) keyTbl.get(key);

                  if(data == null || (data.currentValue == data.maxValue)) {

                           // Need to put retry logic
                           keyValue =  generateKey(sequenceName,KEY_INC_COUNTER);

                           if(keyValue == -1) {
                               throw new Exception ("SequenceName not in SEQUENCE_GENERATOR table :" + sequenceName );
                           }

                           data = new Key(keyValue,keyValue,keyValue + KEY_INC_COUNTER);

                           keyTbl.put(key,data);
                  }
                  keyValue = data.currentValue;

                  data.currentValue++;

                  return keyValue;
           }
  }


  public static synchronized long generateKey(String sequenceName,int count)
                                throws Exception {

             PreparedStatement stmt=null;
             ResultSet rset=null;
             Connection db_conn = null;
             StringBuffer sql = new StringBuffer();
             long key=-1;
             long maxKeyCount;
             String sqlSeqNum="";

             try {

              //     db_conn = Utility.getConnection("PASDataSource");
                     db_conn = Util.getConnection("ftpds");

                   sql.append("SELECT SEQ_NUM ");
                   sql.append("FROM SEQUENCE_GENERATOR ");
                   //SJ 2009.02.11, commented and added line below
                   //sql.append("WHERE sequence_name = ? ");
                    sql.append("WHERE SEQUENCE_NAME = ? for update");

                   System.out.println(sql + "::" + sequenceName);
                   stmt = db_conn.prepareStatement(sql.toString());

                   stmt.clearParameters();

                   stmt.setString(1,sequenceName);

                   rset = stmt.executeQuery ();

                    // Iterate through the result set
                    while(rset.next()) {
                          key =   rset.getInt(1);

                          // Update the key
                    }
                    rset.close();
                    stmt.close();
                    stmt = null;
                    rset = null;

                    if(key != -1) {

                          if((key + count) > MAX_KEY_COUNT) {
                              // reset key count
                              // key = 1;
                              sqlSeqNum = "";
                          }
                          else
                              sqlSeqNum = " seq_num + ";

                          sql = new StringBuffer();
                          sql.append("UPDATE SEQUENCE_GENERATOR SET SEQ_NUM = " + sqlSeqNum  + count );
                          sql.append(" WHERE SEQUENCE_NAME = ? ");
                          sql.append(" AND   SEQ_NUM = ? ");
                          stmt = db_conn.prepareStatement(sql.toString());

                          stmt.clearParameters();

                          stmt.setString(1,sequenceName);
                          stmt.setLong(2,key);

                          if(stmt.executeUpdate() != 1) {
                               System.out.println(
                                           ":Not able to generate a new UNIQUE key for sequence :" + sequenceName);
                               throw new Exception("Not able to generate a new UNIQUE key for sequence :" + sequenceName );
                          }
                          return key;
                    }

                    return -1;
               }
               catch (Exception e) {
                     System.out.println(e.getMessage());
                     throw e;
               }
             finally { 
                 Util.release(db_conn, stmt, rset);
                 System.out.println("Closing Connection in KeyGenerator(generateKey).");  
             }}
  }

some other transaction is locking the table you want to insert into. you need to figure out what it is and why it's holding onto it. once you do, consider whether you can get by with row level locking or you need table level. also consider extending your timeout if it is acceptable for transactions to lock the table longer than the current timeout — coffeeaddict
– coffeeaddict, Commented Aug 27, 2014 at 23:08
try and use auto_increment columns instead of hand rolling key generation (dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html) — Laurence
– Laurence, Commented Aug 27, 2014 at 23:11

pecci · Accepted Answer · 2014-08-28 01:46:49Z

2

The best thing would be control this using the auto increment of mysql. But if you have to control the keys in your application, you can use this classes...

SequenceBlockCache

SequenceBlock

answered Aug 28, 2014 at 1:46

pecci

5405 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to resolve Lock wait timeout exceeded exception in SQL?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related