List:General Discussion« Previous MessageNext Message »
From: Date:January 1 1970 12:00am
Subject:deadlocks on concurrent INSERTS
View as plain text  

I've got 6 Java client applications (running on 6 different PC's, 
including one of them on the database server) reading data from a flat 
file and inserting data into a table on my database:

mysqld Ver 5.1.12-beta-log for unknown-linux-gnu on x86_64 (MySQL 
Community Server (GPL))

There are total about 12 million lines of data to read from these flat 
files and INSERT into a Innodb table on the database. Each client 
application has 20 threads running which run some validation code on the 
record and then perform the insert. I have not exceeded my max_connections.

Around insert number 450,000 some of the clients started getting the 
following error: 

Exception in Processor.DataArchiveInsert, trying to insert into 
data_archve, e:
java.sql.SQLException: Lock wait timeout exceeded; try restarting 

The 4 clients which did not recieve the error have stopped (but have not 
thrown errors) and when I view the connections from MySQL Administrator 
I see 4 INSERT statements to the same table just which have been running 
for 40926 seconds.

The code for this is a simple INSERT statement in Java :

mysqlStatement.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
              ResultSet rsRecordsInsert = mysqlStatement.getGeneratedKeys();
              while (
                  recordID = rsRecordsInsert.getInt(1);

There are no errors in the databases error log nor anything in the slow log.

I don't even know what exactly is wrong here other than "it ain't 

Can someone lead me down the path to diagnose this problem and also fix it?

Thanks in advance,


James Neff

deadlocks on concurrent INSERTSUnknown Sender6 Dec
  • Re: deadlocks on concurrent INSERTSBen de Luca6 Dec
    • Re: deadlocks on concurrent INSERTSUnknown Sender7 Dec