http://pastebin.com/RPccq2wy
Basically, I'm running a game server for an MMO - and unfortunately I can't recreate the deadlock myself, so I don't know what in-game action is causing the deadlock, but the last 3 deadlocks have all revolved around the same two tables - inventoryequipment and inventoryitems
I'm running java jdk update 26 with jre6, and have an average of 20 connections at a time every day. I'm renting the 4 core VDS, which has more than enough power to run this, and all installed applications are 64 bit (windows server 2008 r2).
When a deadlock occurs, the game server freezes up, disconnecting everybody and causing a rollback. This gets aggrivating as the game in question takes some hours of playtime to get anywhere.
I've been told this happens from the DB lagging, causing it to timeout the connection causing a deadlock, but it is setup locally. The server application has an allotted heap size of 1.5 gigs - but even setting it lower didn't fix it, and setting it higher didn't do anything.
I'll also post the function in which it saves the items to the DB. If you guys need anything else, please, tell me
Code: Select all
public synchronized void saveItems(List<Pair<IItem, MapleInventoryType>> items, int id) throws SQLException {
PreparedStatement ps = null;
PreparedStatement pse = null;
try {
StringBuilder query = new StringBuilder();
query.append("DELETE FROM `inventoryitems` WHERE `type` = ? AND `");
query.append(account ? "accountid" : "characterid").append("` = ?");
Connection con = DatabaseConnection.getConnection();
ps = con.prepareStatement(query.toString());
ps.setInt(1, value);
ps.setInt(2, id);
ps.executeUpdate();
ps.close();
ps = con.prepareStatement("INSERT INTO `inventoryitems` VALUES (DEFAULT, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", Statement.RETURN_GENERATED_KEYS);
pse = con.prepareStatement("INSERT INTO `inventoryequipment` VALUES (DEFAULT, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
for (Pair<IItem, MapleInventoryType> pair : items) {
IItem item = pair.getLeft();
MapleInventoryType mit = pair.getRight();
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());
ps.executeUpdate();
if (mit.equals(MapleInventoryType.EQUIP) || mit.equals(MapleInventoryType.EQUIPPED)) {
ResultSet rs = ps.getGeneratedKeys();
if (!rs.next())
throw new RuntimeException("Inserting item failed.");
pse.setInt(1, rs.getInt(1));
rs.close();
IEquip equip = (IEquip) item;
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, 0);
pse.setInt(20, equip.getVicious());
pse.setInt(21, equip.getItemLevel());
pse.setInt(22, equip.getItemExp());
pse.setInt(23, equip.getRingId());
pse.executeUpdate();
}
}
pse.close();
ps.close();
} finally {
if (ps != null) ps.close();
if (pse != null) pse.close();
}
}
