Hitachi Vantara Pentaho Community Forums
Results 1 to 2 of 2

Thread: Lock wait timeout exceeded exception

  1. #1

    Default Lock wait timeout exceeded exception

    Hi,

    I am getting Lock wait timeout exceeded exception for relatively simple/small data set. I am using MySQL. I ran the Insert/Update query which is actually causing the time out (both per the log from job/transformation and mysql slow query log) independently from mysql query editor and it takes milliseconds to execute that query. I have tried this in both configs where the db is remote and local - pretty much same result. The table where row is getting inserted only has 35K records and is fairly simple.

    Strange thing is when I run the job from my machine hitting remote db - it consistently fails on the same entry.

    Also mysql process list shows only that as the only query running at the time, so it doesnt look like a deadlock scenario. I even upgraded the mysql driver in PDI to 5.0 latest, that didnt help either.

    Will appreciate any pointers on how to go about debugging this.

    Here is the exception trace - just in case

    2008/11/17 17:17:18 - Cleanup SKU - Get price strs.0 - =========> CategoryMemberUpsert: Processing SKU 0077083
    2008/11/17 17:17:56 - Upsert ProductCategoryMember.0 - ERROR (version 3.1.0, build 826 from 2008/10/30 17:23:39) : Unexpected error :
    2008/11/17 17:17:56 - Upsert ProductCategoryMember.0 - ERROR (version 3.1.0, build 826 from 2008/10/30 17:23:39) : org.pentaho.di.core.exception.KettleStepException:
    2008/11/17 17:17:56 - Upsert ProductCategoryMember.0 - ERROR (version 3.1.0, build 826 from 2008/10/30 17:23:39) : Error in step, asking everyone to stop because of:
    2008/11/17 17:17:56 - Upsert ProductCategoryMember.0 - ERROR (version 3.1.0, build 826 from 2008/10/30 17:23:39) :
    2008/11/17 17:17:56 - Upsert ProductCategoryMember.0 - ERROR (version 3.1.0, build 826 from 2008/10/30 17:23:39) : Error inserting/updating row
    2008/11/17 17:17:56 - Upsert ProductCategoryMember.0 - ERROR (version 3.1.0, build 826 from 2008/10/30 17:23:39) : Lock wait timeout exceeded; try restarting transaction
    2008/11/17 17:17:56 - Upsert ProductCategoryMember.0 - ERROR (version 3.1.0, build 826 from 2008/10/30 17:23:39) :
    2008/11/17 17:17:56 - Upsert ProductCategoryMember.0 - ERROR (version 3.1.0, build 826 from 2008/10/30 17:23:39) :
    2008/11/17 17:17:56 - Upsert ProductCategoryMember.0 - ERROR (version 3.1.0, build 826 from 2008/10/30 17:23:39) : at org.pentaho.di.trans.steps.insertupdate.InsertUpdate.processRow(InsertUpdate.java:291)
    2008/11/17 17:17:56 - Upsert ProductCategoryMember.0 - ERROR (version 3.1.0, build 826 from 2008/10/30 17:23:39) : at org.pentaho.di.trans.step.BaseStep.runStepThread(BaseStep.java:2664)
    2008/11/17 17:17:56 - Upsert ProductCategoryMember.0 - ERROR (version 3.1.0, build 826 from 2008/10/30 17:23:39) : at org.pentaho.di.trans.steps.insertupdate.InsertUpdate.run(InsertUpdate.java:485)
    2008/11/17 17:17:56 - Upsert ProductCategoryMember.0 - ERROR (version 3.1.0, build 826 from 2008/10/30 17:23:39) : Caused by: org.pentaho.di.core.exception.KettleDatabaseException:
    2008/11/17 17:17:56 - Upsert ProductCategoryMember.0 - ERROR (version 3.1.0, build 826 from 2008/10/30 17:23:39) : Error inserting/updating row
    2008/11/17 17:17:56 - Upsert ProductCategoryMember.0 - ERROR (version 3.1.0, build 826 from 2008/10/30 17:23:39) : Lock wait timeout exceeded; try restarting transaction
    2008/11/17 17:17:56 - Upsert ProductCategoryMember.0 - ERROR (version 3.1.0, build 826 from 2008/10/30 17:23:39) :
    2008/11/17 17:17:56 - Upsert ProductCategoryMember.0 - ERROR (version 3.1.0, build 826 from 2008/10/30 17:23:39) : at org.pentaho.di.core.database.Database.insertRow(Database.java:1314)
    2008/11/17 17:17:56 - Upsert ProductCategoryMember.0 - ERROR (version 3.1.0, build 826 from 2008/10/30 17:23:39) : at org.pentaho.di.core.database.Database.insertRow(Database.java:1227)
    2008/11/17 17:17:56 - Upsert ProductCategoryMember.0 - ERROR (version 3.1.0, build 826 from 2008/10/30 17:23:39) : at org.pentaho.di.core.database.Database.insertRow(Database.java:1210)
    2008/11/17 17:17:56 - Upsert ProductCategoryMember.0 - ERROR (version 3.1.0, build 826 from 2008/10/30 17:23:39) : at org.pentaho.di.trans.steps.insertupdate.InsertUpdate.lookupValues(InsertUpdate.java:103)
    2008/11/17 17:17:56 - Upsert ProductCategoryMember.0 - ERROR (version 3.1.0, build 826 from 2008/10/30 17:23:39) : at org.pentaho.di.trans.steps.insertupdate.InsertUpdate.processRow(InsertUpdate.java:274)
    2008/11/17 17:17:56 - Upsert ProductCategoryMember.0 - ERROR (version 3.1.0, build 826 from 2008/10/30 17:23:39) : ... 2 more
    2008/11/17 17:17:56 - Upsert ProductCategoryMember.0 - ERROR (version 3.1.0, build 826 from 2008/10/30 17:23:39) : Caused by: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
    2008/11/17 17:17:56 - Upsert ProductCategoryMember.0 - ERROR (version 3.1.0, build 826 from 2008/10/30 17:23:39) : at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:946)
    2008/11/17 17:17:56 - Upsert ProductCategoryMember.0 - ERROR (version 3.1.0, build 826 from 2008/10/30 17:23:39) : at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2985)
    2008/11/17 17:17:56 - Upsert ProductCategoryMember.0 - ERROR (version 3.1.0, build 826 from 2008/10/30 17:23:39) : at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1631)
    2008/11/17 17:17:56 - Upsert ProductCategoryMember.0 - ERROR (version 3.1.0, build 826 from 2008/10/30 17:23:39) : at com.mysql.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:1232)
    2008/11/17 17:17:56 - Upsert ProductCategoryMember.0 - ERROR (version 3.1.0, build 826 from 2008/10/30 17:23:39) : at com.mysql.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:740)
    2008/11/17 17:17:56 - Upsert ProductCategoryMember.0 - ERROR (version 3.1.0, build 826 from 2008/10/30 17:23:39) : at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1604)
    2008/11/17 17:17:56 - Upsert ProductCategoryMember.0 - ERROR (version 3.1.0, build 826 from 2008/10/30 17:23:39) : at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1519)
    2008/11/17 17:17:56 - Upsert ProductCategoryMember.0 - ERROR (version 3.1.0, build 826 from 2008/10/30 17:23:39) : at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1504)
    2008/11/17 17:17:56 - Upsert ProductCategoryMember.0 - ERROR (version 3.1.0, build 826 from 2008/10/30 17:23:39) : at org.pentaho.di.core.database.Database.insertRow(Database.java:1262)
    2008/11/17 17:17:56 - Upsert ProductCategoryMember.0 - ERROR (version 3.1.0, build 826 from 2008/10/30 17:23:39) : ... 6 more

  2. #2
    Join Date
    May 2006
    Posts
    4,882

    Default

    Uncomment the line in /etc/my.cnf:

    innodb_lock_wait_timeout = 50

    And change the setting to 500 (its seconds)

    Restart mysql and rerun your job


    Besides that it's possibly a MySQL bug:
    A bug causing something like that was present e.g. 4.0.26, it was not a problem in 4.0.20, and in 4.1.1 it was finally fixed. So maybe the above change of setting won't help, and you're just using a "bugged" version of MySql.

    As usual do an internet search for the error message... you'll find a couple of thousand entries on the problem.

    Regards,
    Sven
    Last edited by sboden; 11-18-2008 at 03:44 AM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.