陷入僵局，
我有這個程式碼：
public void saveItems(List<Pair<Item, 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<Item, MapleInventoryType> pair : items) { Item 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)) { try (ResultSet rs = ps.getGeneratedKeys()) { if (!rs.next()) { throw new RuntimeException("Inserting item failed."); } pse.setInt(1, rs.getInt(1)); rs.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(); ps.close(); } catch (Exception e) { e.printStackTrace(); } finally { if (ps != null) { ps.close(); } if (pse != null) { pse.close(); } } }死鎖錯誤：
com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException：嘗試獲取鎖時發現死鎖；嘗試在 sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source) at sun.reflect.GeneratedConstructorAccessor15.newInstance(Unknown Source) 在 java.lang.reflect.Constructor.newInstance(Unknown Source) at com.mysql.jdbc.Util 重新啟動事務.handleNewInstance(Util.java:408) 在 com.mysql.jdbc.Util.getInstance(Util.java:383) 在 com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1074) 在 com.mysql.jdbc。 MysqlIO.checkErrorPacket(MysqlIO.java:4226) 在 com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4158) 在 com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2615) 在 com.mysql.jdbc .MysqlIO.sqlQueryDirect(MysqlIO. $ TailFilter.mess ageReceived(DefaultIoFilterChain.java:690) at org.apache.mina.core.filterchain.DefaultIoFilterChain.callNextMessage Received(DefaultIoFilterChain.java:417) at org.apache.mina.core.filterchain.DefaultIoFilterChain.access $ org.apache.mina.core.filterchain.DefaultIoFilterChain 處的 1200（預設 IoFilterChain.java:47） $ EntryImpl $ 1.mes sageReceived(DefaultIoFilterChain.java:765) 在 org.apache.mina.filter.codec.ProtocolCodecFilter $ ProtocolDecoderOutpu tImpl.flush(ProtocolCodecFilter.java:407) at org.apache.mina.filter.codec.ProtocolCodecFilter.messageReceived(Prot ocolCodecFilter.java:236) at org.apache.mina.core.filterchain.DefaultIoFilterChain.callNextMessage Received(DefaultIoFilterChain.java:417) at org.apache.mina.core.filterchain.DefaultIoFilterChain.access $ org.apache.mina.core.filterchain.DefaultIoFilterChain 處的 1200（預設 IoFilterChain.java:47） $ EntryImpl $ 1.messageReceived(DefaultIoFilterChain.java:765) at org.apache.mina.core.filterchain.IoFilterAdapter.messageReceived(IoFi lterAdapter.java:109) at org.apache.mina.core.filterchain.DefaultIoFilterChain.callNextMessage Received(DefaultIoFilterChain .java:417) 在 org.apache.mina.core.filterchain.DefaultIoFilterChain.fireMessageReceived(DefaultIoFilterChain.java:410) 在 org.apache.mina.core.polling.AbstractPollingIoProcessor.read(Abstract PollingIoProcessor.java:710) 在org.apache.mina.core.polling.AbstractPollingIoProcessor.process(Abstr actPollingIoProcessor.java:664) 在 org.apache.mina.core.polling.AbstractPollingIoProcessor.process(Abstr actPollingIoProcessor.java:653) 在 org.apache.mina。 core.polling.AbstractPollingIoProcessor.access $ 600(Ab stractPollingIoProcessor.java:67) at org.apache.mina.core.polling.AbstractPollingIoProcessor $ Processor.run (AbstractPollingIoProcessor.java:1124) at org.apache.mina.util.NamePreservingRunnable.run(NamePreservingRunnabl e.java:64) at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) at java.util.concurrent .ThreadPoolExecutor$Worker.run(Unknown Source) at java.lang.Thread.run(Unknown Source)
saveItems(ItemFactory.java:147) 是 ps.executeUpdate(); 我試圖檢查“SHOW ENGINE INNODB STATUS;” 在 mysql 上，我得到了這個註釋：
'InnoDB', '', ' ===================================== 2014-08-24 12:38:54 bcc INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 49 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 1034 srv_active, 0 srv_shutdown, 1111 srv_idle srv_master_thread log flush and writes: 2145 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 1238 OS WAIT ARRAY INFO: signal count 1208 Mutex spin waits 91061, rounds 86815, OS waits 817 RW-shared spins 443, rounds 11553, OS waits 377 RW-excl spins 78, rounds 1712, OS waits 44 Spin rounds per wait: 0.95 mutex, 26.08 RW-shared, 21.95 RW-excl ------------------------ LATEST FOREIGN KEY ERROR ------------------------ 2014-08-24 12:38:54 1a24 Transaction: TRANSACTION 2127395, ACTIVE 0 sec inserting mysql tables in use 1, locked 1 34 lock struct(s), heap size 6544, 357 row lock(s), undo log entries 297 MySQL thread id 602, OS thread handle 0x1a24, query id 249063 localhost 127.0.0.1 root update INSERT INTO `inventoryitems` VALUES (DEFAULT, 4, '595', null, 5390001, 5, 1, 1, '', -1, 0, -1, '') Foreign key constraint fails for table `israeldev`.`inventoryitems`: , CONSTRAINT `FK_inventoryitems_1` FOREIGN KEY (`characterid`) REFERENCES `characters` (`id`) ON DELETE CASCADE Trying to add in child table, in index `FK_inventoryitems_1` tuple: DATA TUPLE: 2 fields; 0: len 4; hex 80000253; asc S;; 1: len 4; hex 01d4c23b; asc ;;; But in parent table `israeldev`.`characters`, in index `PRIMARY`, the closest match we can find is record: PHYSICAL RECORD: n_fields 70; compact format; info bits 0 0: len 4; hex 80007564; asc ud;; 1: len 6; hex 000000204b8d; asc K ;; 2: len 7; hex 5a000001ea0110; asc Z ;; 3: len 4; hex 80000003; asc ;; 4: len 4; hex 80000000; asc ;; 5: len 6; hex 42696c6c6f77; asc Billow;; 6: len 4; hex 80000087; asc ;; 7: len 4; hex 801a6202; asc b ;; 8: len 4; hex 80000000; asc ;; 9: len 4; hex 80000004; asc ;; 10: len 4; hex 800000ae; asc ;; 11: len 4; hex 800001c6; asc ;; 12: len 4; hex 80000004; asc ;; 13: len 4; hex 80007530; asc u0;; 14: len 4; hex 8000751f; asc u ;; 15: len 4; hex 80007530; asc u0;; 16: len 4; hex 80007530; asc u0;; 17: len 4; hex 8e694a2c; asc iJ,;; 18: len 4; hex 00000000; asc ;; 19: len 4; hex 8000038e; asc ;; 20: len 4; hex 80000000; asc ;; 21: len 4; hex 80000000; asc ;; 22: len 4; hex 80000001; asc ;; 23: len 4; hex 80009132; asc 2;; 24: len 4; hex 80004fbc; asc O ;; 25: len 4; hex 8000223c; asc "庫存物品 sql :
'inventoryitems', 'CREATE TABLE `inventoryitems` ( `inventoryitemid` int(10) unsigned NOT NULL AUTO_INCREMENT, `type` tinyint(3) unsigned NOT NULL, `characterid` int(11) DEFAULT NULL, `accountid` int(11) DEFAULT NULL, `itemid` int(11) NOT NULL DEFAULT '0', `inventorytype` int(11) NOT NULL DEFAULT '0', `position` 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, PRIMARY KEY (`inventoryitemid`), KEY `FK_inventoryitems_1` (`characterid`), CONSTRAINT `FK_inventoryitems_1` FOREIGN KEY (`characterid`) REFERENCES `characters` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=31099641 DEFAULT CHARSET=latin1'庫存設備 SQL：
'inventoryequipment', 'CREATE TABLE `inventoryequipment` ( `inventoryequipmentid` int(10) unsigned NOT NULL AUTO_INCREMENT, `inventoryitemid` int(10) unsigned NOT NULL DEFAULT '0', `upgradeslots` int(11) NOT NULL DEFAULT '0', `level` int(11) NOT NULL DEFAULT '0', `str` int(11) NOT NULL DEFAULT '0', `dex` int(11) NOT NULL DEFAULT '0', `int` int(11) NOT NULL DEFAULT '0', `luk` int(11) NOT NULL DEFAULT '0', `hp` int(11) NOT NULL DEFAULT '0', `mp` int(11) NOT NULL DEFAULT '0', `watk` int(11) NOT NULL DEFAULT '0', `matk` int(11) NOT NULL DEFAULT '0', `wdef` int(11) NOT NULL DEFAULT '0', `mdef` int(11) NOT NULL DEFAULT '0', `acc` int(11) NOT NULL DEFAULT '0', `avoid` int(11) NOT NULL DEFAULT '0', `hands` int(11) NOT NULL DEFAULT '0', `speed` int(11) NOT NULL DEFAULT '0', `jump` int(11) NOT NULL DEFAULT '0', `locked` int(11) NOT NULL DEFAULT '0', `vicious` int(11) unsigned NOT NULL DEFAULT '0', `itemlevel` int(11) NOT NULL DEFAULT '1', `itemexp` int(11) unsigned NOT NULL DEFAULT '0', `ringid` int(11) NOT NULL DEFAULT '-1', PRIMARY KEY (`inventoryequipmentid`), KEY `inventoryitemid` (`inventoryitemid`), CONSTRAINT `inventoryequipment_ibfk_1` FOREIGN KEY (`inventoryitemid`) REFERENCES `inventoryitems` (`inventoryitemid`) ON DELETE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=8811024 DEFAULT CHARSET=latin1'人物 ：
'characters', 'CREATE TABLE `characters` ( `id` int(11) NOT NULL AUTO_INCREMENT, `accountid` int(11) NOT NULL DEFAULT '0', `world` int(11) NOT NULL DEFAULT '0', `name` varchar(13) NOT NULL DEFAULT '', `level` int(11) NOT NULL DEFAULT '1', `exp` int(11) NOT NULL DEFAULT '0', `gachaexp` int(11) NOT NULL DEFAULT '0', `str` int(11) NOT NULL DEFAULT '12', `dex` int(11) NOT NULL DEFAULT '5', `luk` int(11) NOT NULL DEFAULT '4', `int` int(11) NOT NULL DEFAULT '4', `hp` int(11) NOT NULL DEFAULT '50', `mp` int(11) NOT NULL DEFAULT '5', `maxhp` int(11) NOT NULL DEFAULT '50', `maxmp` int(11) NOT NULL DEFAULT '5', `meso` int(11) NOT NULL DEFAULT '0', `hpMpUsed` int(11) unsigned NOT NULL DEFAULT '0', `job` int(11) NOT NULL DEFAULT '0', `skincolor` int(11) NOT NULL DEFAULT '0', `gender` int(11) NOT NULL DEFAULT '0', `fame` int(11) NOT NULL DEFAULT '0', `hair` int(11) NOT NULL DEFAULT '0', `face` int(11) NOT NULL DEFAULT '0', `ap` int(11) NOT NULL DEFAULT '0', `sp` int(11) NOT NULL DEFAULT '0', `map` int(11) NOT NULL DEFAULT '0', `spawnpoint` int(11) NOT NULL DEFAULT '0', `gm` tinyint(1) NOT NULL DEFAULT '0', `party` int(11) NOT NULL DEFAULT '0', `buddyCapacity` int(11) NOT NULL DEFAULT '25', `createdate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `rank` int(10) unsigned NOT NULL DEFAULT '1', `rankMove` int(11) NOT NULL DEFAULT '0', `jobRank` int(10) unsigned NOT NULL DEFAULT '1', `jobRankMove` int(11) NOT NULL DEFAULT '0', `guildid` int(10) unsigned NOT NULL DEFAULT '0', `guildrank` int(10) unsigned NOT NULL DEFAULT '5', `messengerid` int(10) unsigned NOT NULL DEFAULT '0', `messengerposition` int(10) unsigned NOT NULL DEFAULT '4', `mountlevel` int(9) NOT NULL DEFAULT '1', `mountexp` int(9) NOT NULL DEFAULT '0', `mounttiredness` int(9) NOT NULL DEFAULT '0', `omokwins` int(11) NOT NULL DEFAULT '0', `omoklosses` int(11) NOT NULL DEFAULT '0', `omokties` int(11) NOT NULL DEFAULT '0', `matchcardwins` int(11) NOT NULL DEFAULT '0', `matchcardlosses` int(11) NOT NULL DEFAULT '0', `matchcardties` int(11) NOT NULL DEFAULT '0', `MerchantMesos` int(11) DEFAULT '0', `HasMerchant` tinyint(1) DEFAULT '0', `equipslots` int(11) NOT NULL DEFAULT '96', `useslots` int(11) NOT NULL DEFAULT '96', `setupslots` int(11) NOT NULL DEFAULT '96', `etcslots` int(11) NOT NULL DEFAULT '96', `familyId` int(11) NOT NULL DEFAULT '-1', `monsterbookcover` int(11) NOT NULL DEFAULT '0', `allianceRank` int(10) NOT NULL DEFAULT '5', `vanquisherStage` int(11) unsigned NOT NULL DEFAULT '0', `dojoPoints` int(11) unsigned NOT NULL DEFAULT '0', `lastDojoStage` int(10) unsigned NOT NULL DEFAULT '0', `finishedDojoTutorial` tinyint(1) unsigned NOT NULL DEFAULT '0', `vanquisherKills` int(11) unsigned NOT NULL DEFAULT '0', `summonValue` int(11) unsigned NOT NULL DEFAULT '0', `reborns` int(11) NOT NULL DEFAULT '0', `uniqName` int(11) NOT NULL DEFAULT '0', `pvpkills` int(11) NOT NULL DEFAULT '0', `pvpdeaths` int(11) NOT NULL DEFAULT '0', `familiar` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`id`), KEY `accountid` (`accountid`), KEY `party` (`party`), KEY `ranking1` (`level`,`exp`), KEY `ranking2` (`gm`,`job`) ) ENGINE=InnoDB AUTO_INCREMENT=32944 DEFAULT CHARSET=latin1 PACK_KEYS=0'SQL 程式碼 .-. 錯誤發生時
----------------------------- 從 `inventoryitems` 中刪除 WHERE `type` = ? AND `accountid` = ? 值：2 編號：490 com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException：死鎖發現 d 試圖獲得鎖時；嘗試重啟事務請幫我解決這個死鎖:(。
系統開始抱怨評論的數量。
我有興趣查看發送到伺服器的語句 - 即完全生成的 SQL 語句，即具有實際值，不是嗎？參數佔位符。
似乎導致問題的聲明就是這個。
INSERT INTO `inventoryitems` VALUES (DEFAULT, 4, '595', null, 5390001, 5, 1, 1, '', -1, 0, -1, '')現在，庫存項目有一個 FOREIGN KEY 約束
刪除級聯時的約束
FK_inventoryitems_1外鍵 (characterid) 引用characters( )id以下查詢的結果是什麼
SELECT COUNT(*) FROM characters WHERE characters.id = 595;這裡的值也很有趣。
ps = con.prepareStatement("INSERT INTO `inventoryitems` VALUES (DEFAULT, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", Statement.RETURN_GENERATED_KEYS); pse = con.prepareStatement("INSERT INTO `inventoryequipment` VALUES (DEFAULT, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");$$ EDIT $$ 我開始對可能發生的事情有所了解。您似乎正在使用某種框架進行數據庫訪問（這就是為什麼您的 SQL 使用 ‘595’ 表示整數插入而不是 595。
框架可能將事務隔離級別設置為讀取送出以外的其他內容（請參閱此處）。嘗試並確保您的事務使用已送出的讀取 - 設置事務級別。嘗試在該行之後將此程式碼添加到您的程式碼中
連接 con = DatabaseConnection.getConnection(); con.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED); // 添加的行。
另外，請查看此內容和執行緒的其餘部分。
$$ EDIT $$回應“我如何檢查它？，你能指導我嗎？” 我的意思是讓您將上面的行添加到您的程式碼中，然後重新執行您的系統，直到您遇到原始錯誤。你仍然收到錯誤嗎？如果你這樣做，那麼它與原始錯誤不同嗎？如果不同，請在此處發布錯誤文本。HTH。
你能把你的數據庫轉儲到網上嗎？您可以匿名任何敏感內容。此外，壓縮您的 Java 程式碼，我將在此處針對您的架構執行它。我的 Java 有點生疏了，但在不同的系統上執行它可能會給我們一些線索！:-)
$$ EDIT $$回應 OP 的評論“你能給我一個索引的例子嗎？$$ code example $$？”。 請參見此處（您也可以使用 KEY 關鍵字作為 INDEX 的同義詞 - 請參見此處的 docco 。您應該在您的清單項中添加 CREATE TABLE 語句行，例如
INDEX `ii_type_ix` (type), INDEX `ii_account_id_ix` (accountid),至於更改您的 Java 程式碼 - 我不是 100% 確定，但問題出在數據庫上，因此您的工作應該集中在那裡。您可以更改程式碼的地方是改變查詢的順序（但保留原始功能）。
$$ Solved $$ 怎麼修：
'inventoryitems', 'CREATE TABLE `inventoryitems` ( `inventoryitemid` int(10) unsigned NOT NULL AUTO_INCREMENT, `type` tinyint(3) unsigned NOT NULL, `characterid` int(11) DEFAULT NULL, `accountid` int(11) DEFAULT NULL, `itemid` int(11) NOT NULL DEFAULT '0', `inventorytype` int(11) NOT NULL DEFAULT '0', `position` 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, PRIMARY KEY (`inventoryitemid`), KEY `FK_inventoryitems_1` (`characterid`), KEY `FK_inventoryitems_2` (`accountid`) USING BTREE, CONSTRAINT `FK_inventoryitems_1` FOREIGN KEY (`characterid`) REFERENCES `characters` (`id`) ON DELETE CASCADE, CONSTRAINT `FK_inventoryitems_2` FOREIGN KEY (`accountid`) REFERENCES `accounts` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=40321116 DEFAULT CHARSET=latin1'代替 ：
'inventoryitems', 'CREATE TABLE `inventoryitems` ( `inventoryitemid` int(10) unsigned NOT NULL AUTO_INCREMENT, `type` tinyint(3) unsigned NOT NULL, `characterid` int(11) DEFAULT NULL, `accountid` int(11) DEFAULT NULL, `itemid` int(11) NOT NULL DEFAULT '0', `inventorytype` int(11) NOT NULL DEFAULT '0', `position` 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, PRIMARY KEY (`inventoryitemid`), KEY `FK_inventoryitems_1` (`characterid`), CONSTRAINT `FK_inventoryitems_1` FOREIGN KEY (`characterid`) REFERENCES `characters` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=31099641 DEFAULT CHARSET=latin1'