CODEWITHSUNDEEP
javamysqlmultithreadingdeadlock
Nicolas Martel
Nicolas Martel

Reputation: 1721

Java MySQL deadlock (multithreading)

I'm getting this weird deadlock and I can't quite figure out why it occurs

If two threads call this one method at about the same time, I get this exception:

java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction

This is the method

public void saveItems(List<Pair<Item, MapleInventoryType>> items, int id) throws             SQLException {
    synchronized (this) {
        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(); //DEADLOCK OCCURS HERE
            ps.close();

            for (Pair<Item, MapleInventoryType> pair : items) {
                Item item = pair.getLeft();
                MapleInventoryType mit = pair.getRight();
                ps = con.prepareStatement("INSERT INTO `inventoryitems` VALUES (DEFAULT, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", Statement.RETURN_GENERATED_KEYS);
                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)) {
                    pse = con.prepareStatement("INSERT INTO `inventoryequipment` VALUES (DEFAULT, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
                    try (ResultSet rs = ps.getGeneratedKeys()) {

                        if (!rs.next()) {
                            throw new RuntimeException("Inserting item failed.");
                        }

                        pse.setInt(1, rs.getInt(1));
                    }
                    ps.close();

                    Equip equip = (Equip) 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();
                }
            }
        } finally {
            if (ps != null) {
                ps.close();
            }
            if (pse != null) {
                pse.close();
            }
        }
}

I don't understand how the deadlock could occur since everything is wrapped inside the synchronized block.

Help would be greatly appreciated, thanks.

Upvotes: 0

Views: 683

Answers (4)

fntneves
fntneves

Reputation: 414

Check your transaction isolation level.

Did you set autoCommit() to false ? If true, then when you're running in transaction's context, you need to commit it after work, because, if your transaction isolation level is strong as Repeatable Read, then Lock is on active transaction's context until commit occurs.

Give us more details about what you're doing.

Upvotes: 1

hamster
hamster

Reputation: 1

You are accessing inventoryitems before inventoryequipment. There might be another thread doing this in the opposite order, leading to a deadlock.

You should always have a hierarchy of locks and only access locked resources in the same order.

Why don't you use ps.close() directly after ps.executeUpdate(); but after a complicated if statement? If you need to use values from ps in pse, you should store them in variables between.

Upvotes: 0

Joshua Huber
Joshua Huber

Reputation: 3533

I noticed that you're not calling close() on your Connection. Try using this in place of your finally { } block. Hope this helps.

    finally {
        if (ps != null) ps.close();
        if (pse != null) pse.close();
        if (con != null) con.close();
    }

Upvotes: 0

peter.petrov
peter.petrov

Reputation: 39437

I don't understand how the deadlock could occur since everything is wrapped inside the synchronized block.

You may have e.g. another process/thread doing e.g. an INSERT in the same table. Depending on how your DB is configured you may get this exception which you're getting. The synchronized block here just means that no other Java thread can call/enter into this same block of code at the same time. This doesn't prevent the same DB resource table/page/record (on the DB level) from being accessed by another process/thread at the same time.

Upvotes: 0

Related Questions