I have this code :
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();
}
}
}
DeadLock Error :
com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock foun
d when trying to get lock; try restarting transaction
at sun.reflect.GeneratedConstructorAccessor15.newInstance(Unknown Source
)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Sou
rce)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:408)
at com.mysql.jdbc.Util.getInstance(Util.java:383)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1074)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4226)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4158)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2615)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2776)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2840)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.ja
va:2082)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java
:2334)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java
:2262)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java
:2246)
at client.inventory.ItemFactory.saveItems(ItemFactory.java:147)
at server.MapleStorage.saveToDB(MapleStorage.java:137)
at client.MapleCharacter.saveToDB(MapleCharacter.java:5013)
at net.server.channel.handlers.ChangeMapHandler.handlePacket(ChangeMapHa
ndler.java:59)
at net.MapleServerHandler.messageReceived(MapleServerHandler.java:128)
at org.apache.mina.core.filterchain.DefaultIoFilterChain$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$1200(Def
aultIoFilterChain.java:47)
at org.apache.mina.core.filterchain.DefaultIoFilterChain$EntryImpl$1.mes
sageReceived(DefaultIoFilterChain.java:765)
at 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$1200(Def
aultIoFilterChain.java:47)
at org.apache.mina.core.filterchain.DefaultIoFilterChain$EntryImpl$1.mes
sageReceived(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)
at org.apache.mina.core.filterchain.DefaultIoFilterChain.fireMessageRece
ived(DefaultIoFilterChain.java:410)
at org.apache.mina.core.polling.AbstractPollingIoProcessor.read(Abstract
PollingIoProcessor.java:710)
at org.apache.mina.core.polling.AbstractPollingIoProcessor.process(Abstr
actPollingIoProcessor.java:664)
at org.apache.mina.core.polling.AbstractPollingIoProcessor.process(Abstr
actPollingIoProcessor.java:653)
at 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) is ps.executeUpdate();
i have tried to check "SHOW ENGINE INNODB STATUS;" on mysql and i got this note:
'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 "Inventoryitems 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'
InventoryEquipments 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 :
'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 code .-. when the error happend
-----------------------------
DELETE FROM `inventoryitems` WHERE `type` = ? AND `accountid` = ?
Value : 2
Id : 490
com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock foun
d when trying to get lock; try restarting transaction
Please help me to solve this DeadLock :(.
Best Answer
The system is starting to complain about the number of comments.
I'd be interested in seeing the statements as they are sent to the server - i.e. the fully generated SQL statements, i.e. with real values, not ? parameter place holders.
The statement that appears to be causing the issue is this one.
Now, inventory items has a FOREIGN KEY constraint
CONSTRAINT
FK_inventoryitems_1FOREIGN KEY (characterid) REFERENCEScharacters(id) ON DELETE CASCADEWhat is the result of the following query
The values here are also of interest.
[EDIT]
I'm starting to get some insight into what may be going on. You appear to be using some sort of framework for your database access (that's the reason why your SQL has '595' for an integer insert instead of 595.
It's possible that the transaction isolation level is being set by the framework to something other than read committed (see here). Try and ensure that your transactions use read committed - set the transaction level. Try adding this code to yours after the line
Connection con = DatabaseConnection.getConnection(); con.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED); // Added line.
Also, check out this and the rest of the thread.
[EDIT] in response to "How do i check it? , can you guide me?"
What I meant was for you to add the line above to your code and rerun your system to the point where you got your original error. Do you still get an error? If you do, then is it different to the original error? If it is different, then post the error text here. HTH.
Could you take a dump of your database and put it online? You can anonymise anything sensitive. Also, zip up your Java code and I'll run that here against your schema. My Java's a bit rusty, but running it on a different system might give us some clues! :-)
[EDIT] in response to OP's comment "can you give me an example of indexes [code example] ?".
See here (you can also use the KEY keyword as a synonym for INDEX - see the docco here. You should add to your inventoryitems CREATE TABLE statement lines such as
As for changing your Java code - I'm not 100% sure, but the problem lies with the database so your efforts should be concentrated there. Where you can change the code is in varying the order of your queries (but keeping the original functionality).