Deadlock found when trying to get lock; try restarting transaction

[pramod_ps]

Hi,
I found "Deadlock found when trying to get lock; try restarting transaction" while
running the pentaho. DB engine is InnoDB. How do I prevent the error.
What should be the setting in mySQl.
What design features that I need to take care when I design the set up?

[TomS]

Hi,

I do not think this is a setup-related issue.

It rather seems that your transformation/job causes such deadlock-situation by selecting/updating/deleting rows from different steps in the same table...
I would advise to check your pdi-design for such situations.

Cheers,

Tom

[sreenivask]

if u know reply pls

[MichaelBieri]

Try it without the "Result Streaming Cursor" (check database connection dialog). This one is realy nasty when using the insert/update step against mysql.

[MattCasters]

Only if you're running database transactionally, otherwise it shouldn't matter.

[MichaelBieri]

I think this may also depend on the mysql version - they did some incompatible changes and added some internal locking in version 5.5, based on the bugs of wrong slave sync due wrong order of the binlog entrys.

I had excat the same issue here with a daily raw import/update of 30 millions of rows from a flatfile to a single table in mysql 5.5. I run 2 copies of the insert/update step with a friendly commit size of 5k. It was impossible to import the full file without at least some "Deadlock found when trying to get lock; try restarting transaction"s and active streaming cursor.

Disabling the streaming cursor did help in my case - the import is running stable this way.

Disabling even the binlog on the server side for this database did not help.

I have the feeling that pdi with an actual mysql distribution ist not a great deal. There are some more strange issues, this one seems to be as well a locking issue:
http://jira.pentaho.com/browse/PDI-7090

And if you are using the dimension steps, you are currently forced to use the old JDBC driver:
http://jira.pentaho.com/browse/PDI-6873