title: Avoiding database locks on SQL Server 2012 when updating using Java
body: <p>I have a web application which is based on SQL Server 2012, and I use Java to update data in the database. (Windows Server 2008, JSP, Tomcat7, Java7)</p>
  
  <p>The relevant code is as follows:</p>
  
  <pre><code>public static synchronized int execute(String dsName, String packageAndFunction, List fields) {
  
          // prepare insertStr
          String executeStr = buildStatement(dsName, packageAndFunction, null, fields);
          dbConn  = DBConnection.getInstance();
          Connection conn = dbConn.getConnection();
          CallableStatement stmt = null;
          int result = RESULT_FAILED;
          try {
              stmt = conn.prepareCall(executeStr);
  
              // fill statement parameters (each ?)
              fillStatement(stmt, fields);
  
              stmt.execute();
              result = stmt.getInt(fields.size());
  
          } catch(SQLException e) {
              Log.getInstance().write("Exception on executeGeneral (" + packageAndFunction + ") " + e.toString());
          } finally {
              try {
                  stmt.close();
                  dbConn.returnConnection(conn);
              } catch(SQLException e) {
                  Log.getInstance().write("Exception on executeGeneral (" + packageAndFunction + ") " + e.toString());
              }
          }
          return result;      
      }
  </code></pre>
  
  <p>About 90% of the time, the code works great. The rest of the time there is some kind of lock on the table which will disappear by itself in perhaps half an hour or so. The lock prevents even simple SELECT queries on the table from executing (in SQL Server Management Studio). In severe cases it has prevented the entire application from working. </p>
  
  <p>I had an idea to use <code>stmt.executeUpdate()</code> instead of <code>stmt.execute()</code>, but I have tried to research this and I do not see any evidence that using <code>stmt.execute()</code> for updating causes locks.</p>
  
  <p>Can anyone help?
  Thanks!</p>