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

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 
transaction

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 (rsRecordsInsert.next())
              {
               
                  recordID = rsRecordsInsert.getInt(1);
                               
              }
                           
               
              rsRecordsInsert.close();


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 
working." 

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

Thanks in advance,

-- 

James Neff

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