Archive for the ‘insert delayed’ tag
Delay or synchronize it?
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).