I'm a bit perplexed at a situation where I lock wait timeout when trying to insert columns into a database using Java's PreparedStatements, but don't have a similar issue if I do it with MySQL.
The two tables I'm dealing with are named items and inventory_items. Inventory_items has a foreign key reference to items for the column inventoryitemid.
What I currently have in Java is that a batch insert is made to items and I get the returned keys, then insert the relevant information into inventory_items. There are other tables that have the same foreign key references to items, but for this case if I can fix the issue between items and inventory_items, then I'm sure I can fix it for the rest.
The table structure for inventory_items
CREATE TABLE `inventory_items` (
`inventoryitemid` int(11) unsigned NOT NULL,
`characterid` int(11) DEFAULT NULL,
`inventorytype` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`inventoryitemid`),
KEY `charid` (`characterid`),
CONSTRAINT `INVENTORYITEM_FK` FOREIGN KEY (`inventoryitemid`) REFERENCES `items` (`inventoryitemid`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1
The table structure for items
CREATE TABLE `items` (
`inventoryitemid` int(11) unsigned NOT NULL AUTO_INCREMENT,
`itemid` int(11) NOT NULL DEFAULT '0',
`quantity` int(11) NOT NULL DEFAULT '0',
`owner` tinytext NOT NULL,
`petid` int(11) NOT NULL DEFAULT '-1',
`flag` int(11) NOT NULL,
`expiration` bigint(20) NOT NULL DEFAULT '-1',
`giftFrom` varchar(26) NOT NULL,
`position` tinyint(4) DEFAULT NULL,
PRIMARY KEY (`inventoryitemid`)
) ENGINE=InnoDB AUTO_INCREMENT=733 DEFAULT CHARSET=latin1
Image for items
Image for inventory_items
Java Prepared Statement (times out)
con.prepareStatement("INSERT INTO inventory_items (inventoryitemid, characterid, inventorytype) VALUES (?, ?, ?)");
MySQL equivalent
INSERT INTO inventory_items (inventoryitemid, characterid, inventorytype) VALUES (730, 38843, 3)
Note that I had inputted what would have been the actual values in the SQL statement to see if it could insert.
Full Java SQL Code
@Override
public void batch(Pair<Item, MapleInventoryType> entry, int key, int id) {
try {
if (ps.isClosed())
ps = con.prepareStatement("INSERT INTO inventory_items (inventoryitemid, characterid, inventorytype) VALUES (?, ?, ?)");
//System.out.println("Batching! " + ps.isClosed());
System.out.println("KEY IS " + key);
ps.setInt(1, key);
ps.setInt(2, id);
ps.setInt(3, entry.getRight().getType());
ps.addBatch();
} catch (Exception e) {
LogHelper.GENERAL_EXCEPTION.get().info("InventoryWorker batch: " + e);
}
}
@Override
public void executeBatch(int id) throws SQLException {
// TODO Auto-generated method stub
try (PreparedStatement delete = con.prepareStatement("DELETE FROM items WHERE EXISTS (SELECT * FROM inventory_items WHERE inventory_items.inventoryitemid = items.inventoryitemid AND characterid = ?)")) {
System.out.println("DELETE BY " + id);
delete.setInt(1, id);
delete.execute();
if (!ps.isClosed()) {
ps.executeBatch();
}
} catch (SQLException e) {
LogHelper.GENERAL_EXCEPTION.get().info("InventoryWorker executeBatch: " + e);
} finally {
ps.close();
}
}
The reason why I have it split into two methods is because there are different types of savings I am doing, so I split the saving up into classes to handle types of saving. I don't think we should be concerned about the possibility of the prepared statement never closing as well since I have a finally clause outside of this scope that closes everything just in case of an exception.
Entire save clause that calls the above 2 methods. Note that those two methods are under a class called worker. In the code it will be called as worker.batch() and worker.executeBatch()
public void saveItems(List<Pair<Item, MapleInventoryType>> items, int id, Connection con) throws SQLException {
lock.lock();
long start = System.currentTimeMillis();
// StringBuilder query = new StringBuilder();
// query.append(
// "UPDATE `inventoryitems` SET characterid = -1 WHERE `");
// query.append(account ? "accountid" : "characterid").append("` = ? AND `type` = ? LIMIT 128");
try {
// try (PreparedStatement ps = con.prepareStatement(query.toString())) {
// ps.setInt(1, id);
// ps.setInt(2, value);
// ps.executeUpdate();
// } catch (Exception e) {
// LogHelper.GENERAL_EXCEPTION.get().info("ItemFactory updateItems: " + e);
// }
System.out.println("Time to delete items " + (System.currentTimeMillis() - start));
start = System.currentTimeMillis();
try (PreparedStatement ps = con.prepareStatement("INSERT INTO `items` (`itemid`"+
", `quantity`, `owner`, `petid`, `flag`, `expiration`, `giftFrom`, `position`) VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
Statement.RETURN_GENERATED_KEYS);
PreparedStatement pse = con.prepareStatement(
"INSERT INTO `inventoryequipment` (`inventoryequipmentid`, `inventoryitemid`, `upgradeslots`, `level`, `str`, `dex`, `int`"+
", `luk`, `hp`, `mp`, `watk`, `matk`, `wdef`, `mdef`, `acc`, `avoid`, `hands`, `speed`, `jump`, `vicious`, `itemLevel`, `itemexp`, `ringid`, `skill`) VALUES (DEFAULT, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
) {
LinkedList<Pair<Item, MapleInventoryType>> saved_items = new LinkedList<>();
if (!items.isEmpty()) {
for (Pair<Item, MapleInventoryType> pair : items) {
Item item = pair.getLeft();
if (item.disappearsAtLogout())
continue;
MapleInventoryType mit = pair.getRight();
ps.setInt(1, item.getItemId());
ps.setInt(2, item.getQuantity());
ps.setString(3, item.getOwner());
ps.setInt(4, item.getPetId());
ps.setInt(5, item.getFlag());
ps.setLong(6, item.getExpiration());
ps.setString(7, item.getGiftFrom());
ps.setInt(8, item.getPosition());
// ps.setInt(1, value);
// ps.setString(2, account ? null : String.valueOf(id));
// ps.setString(3, account ? String.valueOf(id) : null);
// ps.setInt(4, item.getItemId());
// ps.setInt(5, mit.getType());
// ps.setInt(6, item.getPosition());
// ps.setInt(7, item.getQuantity());
// ps.setString(8, item.getOwner());
// ps.setInt(9, item.getPetId());
// ps.setInt(10, item.getFlag());
// ps.setLong(11, item.getExpiration());
// ps.setString(12, item.getGiftFrom());
saved_items.add(pair);
ps.addBatch();
}
ps.executeBatch();
System.out.println("Time to save items " + (System.currentTimeMillis() - start));
start = System.currentTimeMillis();
System.out.println("RIGHT BEFORE EQUIPMENT");
try (ResultSet rs = ps.getGeneratedKeys()) {
for (Pair<Item, MapleInventoryType> item : saved_items) {
int key = -1;
if (rs.next()) {
key = rs.getInt(1);
}
if (key == 0) {
key = item.getLeft().getInventoryItemId();
}
worker.batch(item, key, id);
MapleInventoryType mit = item.right;
if (mit.equals(MapleInventoryType.EQUIP) || mit.equals(MapleInventoryType.EQUIPPED)) {
pse.setInt(1, key);
Equip equip = (Equip) item.left;
pse.setInt(2, equip.getUpgradeSlots());
pse.setInt(3, equip.getLevel());
pse.setInt(4, equip.getStr());
pse.setInt(5, equip.getDex());
pse.setInt(6, equip.getInt());
pse.setInt(7, equip.getLuk());
pse.setInt(8, equip.getHp());
pse.setInt(9, equip.getMp());
pse.setInt(10, equip.getWatk());
pse.setInt(11, equip.getMatk());
pse.setInt(12, equip.getWdef());
pse.setInt(13, equip.getMdef());
pse.setInt(14, equip.getAcc());
pse.setInt(15, equip.getAvoid());
pse.setInt(16, equip.getHands());
pse.setInt(17, equip.getSpeed());
pse.setInt(18, equip.getJump());
pse.setInt(19, equip.getVicious());
pse.setInt(20, equip.getItemLevel());
pse.setInt(21, equip.getItemExp());
pse.setInt(22, equip.getRingId());
pse.setBoolean(23, equip.hasSkill());
pse.addBatch();
}
}
pse.executeBatch();
}
}
worker.executeBatch(id);
}
System.out.println("Time to save equipment " + (System.currentTimeMillis() - start));
} catch (Exception e) {
LogHelper.GENERAL_EXCEPTION.get().info("ItemFactory saveItems: " + e);
} finally {
worker.close();
lock.unlock();
}
}
