MacLochlainns Weblog

A couple students in one of my classes ran into a problem when competing Java threads tried to insert new rows in a table. They raised an error when they tried the DELAY keyword to avoid the race (collision) condition in an INSERT statement. It was simple to explain to them that the DELAY keyword doesn’t work with an InnoDB table. Any attempt throws the following error:

ERROR 1616 (HY000): DELAYED OPTION NOT supported FOR TABLE 'message'

Important Update: INSERT DELAYED is gone in MySQL 5.6.6 (announcement) and the whole issue comes down to synchronizing threads (some dislike the solution) or using the ON DUPLICATE KEY clause.

They retested their Java application after redefining the target table using the MyISAM engine. They found it worked but that’s a bad fix in Java (a brief Java/MySQL tutorial post). They really needed to synchronize the Java thread (line #22), leave out the DELAY keyword, and manage the table with the InnoDB engine. Here’s the modified Java code (by the way, they named their project VulcanTech if you’re wondering about the packages in the import statement):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

package vulcantech.vth.server.commands;
 
import java.io.IOException;
import java.net.Socket;
 
import vulcantech.vth.server.combeans.MessageBean;
 
public class MessageHandler implements Handler {
 
  @Override
  public void handleIt(Object... args) {
 
    MessageBean message = (MessageBean) args[0];
    Socket sock = (Socket) args[1];
 
    DatabaseConnection dbconnection = new DatabaseConnection();
 
    String update = new String("INSERT INTO message(message_timestamp, sender, recipient, message, checked) VALUES(\'" 
                  + message.getTimeStamp() + "\', \'" + message.getSender() + "\', \'" 
                  + message.getRecipient() + "\', \'" + message.getMessage() + "\', b\'0\')");
 
    synchronized (this) {
      dbconnection.executeUpdate(update);
    }
 
    try {
      sock.getOutputStream().write(1);
    } catch (IOException e) {
      e.printStackTrace();
    }
    dbconnection.close();
  }
}

Hope this helps those who encounter race conditions against MySQL when you’re writing Enterprise Java Beans (EJBs).