Queries Colliding in JDBC. Table Locking.
Posted by: T Schumann
Date: January 31, 2005 08:54PM

Hello,

Having a problem with colliding queries.

Googled and searched and found nothing on this.

Config:
ConnectorJ 3.0.16-ga
MySQL 4.1.7
Tomcat 4.1.31
Windows XP Pro SP2 all updates applied.
J2EE 1.4

My application receives input from a form and then executes the required query, returning the results to a web page.
Problem occurs when two users execute a query simultaneously. They get "mixed together" in their sequence and clobber each other.

Query looks like this...

LOCK TABLES event AS e READ, event_categorizations AS ec READ, event_dates AS ed READ;
CREATE TEMPORARY TABLE tmp
SELECT DISTINCT e.eventid,ec.event_type_code FROM event e,event_categorizations ec ,event_dates ed
WHERE e.eventid = ec.eventid AND e.eventid = ed.eventid
AND e.approved ='Y'
AND ed.event_date >= '2005-01-31'
AND ec.event_type_code in ('CERAM','FOLK','VISUALARTS','SCULP')
order by e.eventid;
unlock tables;
SELECT DISTINCT tmp.eventid, et.event_type_code
FROM tmp, event_type et, event_type_group eg
WHERE tmp.event_type_code = et.event_type_code
AND et.event_type_group_code = eg.event_type_group_code
AND et.event_type_group_code <> 'PRIC'
ORDER BY eg.description;
drop table tmp;


(Note: I create the temp table to make the query run faster. Otherwise I get an intermediate table of millions of rows.)

The statements in this query are executed through successive calls to executeUpdate() and executeQuery(). (I believe this is necessary, because you cannot combine multiple statements together on a JDBC call, right?)

This is the chunk of code that creates and runs the above query... (Note that I'm using Log4J to dump out the SQL stmts.)

/**
* Populate the eventCollection with all events on or after
* aStartDate whose prices fall in the prices
* listed in aPriceEventTypeCollection(currently disabled) and whose event type
* is one of those listed in anEventTypeCollection
*/

public void populate(
java.util.GregorianCalendar aStartDate
,EventTypeCollection aPriceEventTypeCollection
,EventTypeCollection anEventTypeCollection
)
throws SQLException,ClassNotFoundException{

int day,month,year;
String sDay,sMonth,sYear;
String formatedStartDate;
eventCollection = new Vector();
Connection conn = ConnectionManager.getConnection(); <--SEE BELOW FOR THIS CODE***
Statement stmt;
String SQL = new String();
eventCollection = new Vector();
eventCollectionDescriptions = new Vector();

formatedStartDate = AnsiDate.convert(aStartDate);
cat.debug("Start Date "+formatedStartDate);
// We want all the events between the start date and the end date
// whose categories are in the category list and whose
// price is in the price category list.

try{
SQL = "LOCK TABLES event AS e READ, event_categorizations AS ec READ, event_dates AS ed READ ";
cat.debug(SQL);
stmt = conn.createStatement();
stmt.executeUpdate(SQL);
stmt.close();

SQL = "CREATE TEMPORARY TABLE tmp SELECT DISTINCT e.eventid,ec.event_type_code ";
SQL += "FROM event e";
SQL += ",event_categorizations ec ";
SQL += ",event_dates ed ";

// SQL += ",event_categorizations ep ";

SQL += "WHERE ";
SQL += " e.eventid = ec.eventid";
SQL += " AND e.eventid = ed.eventid";
SQL += " AND e.approved ='Y'";
SQL += " AND ed.event_date >= '" + formatedStartDate +"'";
SQL += " AND ec.event_type_code in (" + anEventTypeCollection.getCollectionAsCommaSeparatedList() +")";
SQL += " order by e.eventid;";
cat.debug(SQL);
stmt = conn.createStatement();
stmt.executeUpdate(SQL);
stmt.close();


SQL = " UNLOCK TABLES;";
cat.debug(SQL);
stmt = conn.createStatement();
stmt.executeUpdate(SQL);
stmt.close();

SQL = "SELECT DISTINCT tmp.eventid, et.event_type_code";
SQL += " FROM tmp, event_type et, event_type_group eg";
SQL += " WHERE tmp.event_type_code = et.event_type_code";
SQL += " AND et.event_type_group_code = eg.event_type_group_code";
SQL += " AND et.event_type_group_code <> 'PRIC'";
SQL += " ORDER BY eg.description;";

cat.debug(SQL);
stmt = conn.createStatement();
ResultSet rset = stmt.executeQuery(SQL);

while (rset.next()){
try{
Event event = new Event(rset.getLong(1));
cat.debug("Event Name: " + event.getEventName());
event.setMainCategory(rset.getString(2));
eventCollection.addElement(event);
}
catch (Exception ex)
{
cat.error("EventCollection",ex.fillInStackTrace());
}



}
stmt.close();
SQL = " DROP TABLE tmp;";
cat.debug(SQL);
stmt = conn.createStatement();
stmt.executeUpdate(SQL);
stmt.close();

} catch (SQLException ex){
cat.error("EventCollection",ex.fillInStackTrace());
} finally {
ConnectionManager.releaseConnection(conn); <-- currently does nothing.
SQL = " UNLOCK TABLES;";
cat.debug(SQL);
stmt = conn.createStatement();
stmt.executeUpdate(SQL);
stmt.close();
SQL = "DROP TABLE tmp;";
cat.debug(SQL);
stmt = conn.createStatement();
try
{
stmt.executeUpdate(SQL);
}
catch (SQLException ex)
{
//do nothing.
}
stmt.close();
ConnectionManager.releaseConnection(conn); <-- currently does nothing.
}
}



***This is the code that opens the connection***

static public Connection getConnection() {
try{
Class.forName("com.mysql.jdbc.Driver").newInstance();
connection = DriverManager.getConnection("jdbc:mysql://server/test");
dbSysdate = "now()";
}
}
catch (Exception e){
e.printStackTrace();
}
return connection;
}



And now this is what I get in the Log4J output...



1. 2005-01-31 15:16:37,546 [http-8080-Processor3] DEBUG com.allmyfun.EventCollection - LOCK TABLES event AS e READ, event_categorizations AS ec READ, event_dates AS ed READ
2. 2005-01-31 15:16:37,546 [http-8080-Processor3] DEBUG com.allmyfun.EventCollection - CREATE TEMPORARY TABLE tmp SELECT DISTINCT e.eventid,ec.event_type_code FROM event e,event_categorizations ec ,event_dates ed WHERE e.eventid = ec.eventid AND e.eventid = ed.eventid AND e.approved ='Y' AND ed.event_date >= '2005-01-31' order by e.eventid;
3. 2005-01-31 15:16:37,906 [http-8080-Processor4] DEBUG jsp.eventlist - in EVENTLIST
4. 2005-01-31 15:16:37,906 [http-8080-Processor4] DEBUG com.allmyfun.EventTypeCollection - SELECT event_type_code,description FROM event_type
5. 2005-01-31 15:16:38,953 [http-8080-Processor3] DEBUG com.allmyfun.EventCollection - UNLOCK TABLES;
6. 2005-01-31 15:16:38,984 [http-8080-Processor4] ERROR com.allmyfun.EventTypeCollection - EventTypeCollection:
java.sql.SQLException: General error message from server: "Table 'event_type' was not locked with LOCK TABLES"
at com.allmyfun.EventTypeCollection.populateEventTypeCollection(EventTypeCollection.java:157)
at com.allmyfun.EventTypeCollection.<init>(EventTypeCollection.java:13)
at org.apache.jsp.eventlist_jsp._jspService(eventlist_jsp.java:342)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:92)

Notice that the processing from user "A" runs as normal on lines 1 and 2 (This is a query similar to the query I listed above)
Then on line 3 and 4, log output shows another user's request jumping and doing a SELECT before user A's process has a chance to unlock the tables.
This then causes the SQL exception.


Is there a way to run the query so this doesn't happen?
Or do I need to manage this in my application, preventing a 2nd query from executing while I've got tables locked? If so, can you point me to a resource that gives me a guideline on the best way to do this?

Thanks!
Trent.

Options: ReplyQuote


Subject
Written By
Posted
Queries Colliding in JDBC. Table Locking.
January 31, 2005 08:54PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.