Hitachi Vantara Pentaho Community Forums
Results 1 to 7 of 7

Thread: release database lock mechanism using pdi-ce-6.0 or below version

  1. #1
    Join Date
    Aug 2015
    Posts
    313

    Default release database lock mechanism using pdi-ce-6.0 or below version

    Hi Sir,Madam,

    Please suggest me which step/process are we have in pdi to release Database locks when data is successfully loaded. here is my scenario.

    I am using pdi-ce-6.0, java 1.7, windows, MySQL database. the problem is whenever i am trying to load the data using PDI GUI tool then data is loading successfully but database connections locks are not releasing, i.e still it is locking my database. i hope there is process in PDI to release the locks if it is.

    if i run jobs using batch file with GUI tool then no issue, but always to run using batch file is not correct approach for every transformation and jobs.

    as per my observation we have step called "check files locked" in the same way are we have some approach for database ? Please help me.

    Thank you

  2. #2
    Join Date
    Aug 2011
    Posts
    360

    Default

    Somes questions:
    What is your MySQL version?
    Which kind of locks are you talking about and how do tell it's locked?
    Are setting locks by yourself in a script or is it PDI?
    Which step do you use to load data?

    I don't understand when say "run batch file with GUI"??

  3. #3
    Join Date
    Aug 2015
    Posts
    313

    Default release database lock mechanism using pdi-ce-6.0 or below version

    for your reference attached the snap shot.

    Quote Originally Posted by Mathias.CH View Post
    Somes questions:
    What is your MySQL version?
    MySQL version is 5.6.23-log

    Quote Originally Posted by Mathias.CH View Post
    Which kind of locks are you talking about and how do tell it's locked?
    Are setting locks by yourself in a script or is it PDI?
    Which step do you use to load data?
    NO, I wasn't kept any locks in a script in PDI. here is my scenario.

    generally when we start PDI, it will extract data from SOURCE and loaded to TARGET. once data is loaded to TARGET database then database locks will be released we can see using show processlist command. Detailed images below
    when i ran ETL using bat file( i have created bat file using script and will execute the ETL's without SPOON GUI mode. then MySQL show processlist will be like below.

    my .bat file
    d:
    cd /d "D:\data-integration\"
    for /F "tokens=2-4 delims=/ " %%i in ('date /t') do set yyyymmdd=%%k%%i%%j
    call Kitchen.bat /file:\PDI\form\MODEL.kjb /level:basic -logfile=D:\data-integration\LOGS\ETL_JOB_%yyyymmdd%.log
    pause

    Name:  without.jpg
Views: 128
Size:  10.0 KB



    when i run ETL using SPOON GUI tool(i.e using start button using spoon GUI tool) then even data loaded to target database ,database locks are not releasing because of this reason CPU usage reaching to 100%. i hope you can observe the difference.

    Name:  with.jpg
Views: 132
Size:  28.1 KB


    Quote Originally Posted by Mathias.CH View Post
    I don't understand when say "run batch file with GUI"??
    Thank you
    Last edited by santhi; 11-26-2015 at 03:55 AM. Reason: header was missed

  4. #4
    Join Date
    Aug 2011
    Posts
    360

    Default

    Quote Originally Posted by santhi View Post

    when i run ETL using SPOON GUI tool(i.e using start button using spoon GUI tool) then even data loaded to target database ,database locks are not releasing because of this reason CPU usage reaching to 100%. i hope you can observe the difference.

    Name:  with.jpg
Views: 132
Size:  28.1 KB
    Hi,

    what you see in the processlist when you use Spoon GUI is not database locks (which means tables are locked for write or read operations by a particular session), but it is just opened connections that are idling and waiting for SQL instructions. You can see that the Command column say "Sleep". This should not be a problem, except if you have more and more connections opened each time you run a job in Spoon.
    You would have a real lock if when you send other SQL commands (like insert/update/delete) with a SQL Client, the processlist show you a message like "waiting for read/write lock to be released (something like that).

    Do you use connection pooling features in the configuration of the database connections in Spoon / or in the jdbc.properties file for JNDI datasources? If so, this is a normal behaviour since the connection pool will open the database connections needed by your job, but when PDI issues a Connection.close(), the connection is not really closed, but it returns back to the connection pool in this idling state, waiting for another job to reuse theses connections.

    As soon as you close Spoon, these will be killed.
    You do not have this behaviour with a batch run, because then the JVM is created just for the batch, and at the end of the job everything is closed.

    Finally, if you don't use connection pooling, this may be an odd stuff, like PDI not closing connections.

    Finally, second, you can issue a "show open tables" SQL command to see which tables are in use (In_use=1) or have are locked by name (Name_locked=1). You should not have a name_lock even with all these connections opened and sleeping.

    Hope this helps!

    References:
    http://dev.mysql.com/doc/refman/5.6/...nsactions.html

    http://dev.mysql.com/doc/refman/5.6/...ng-issues.html
    (be aware of "metadata locking" feature introduced in MySQL 5.5, you can see many people complaining on that on the web)

  5. #5
    Join Date
    Aug 2015
    Posts
    313

    Default

    Thank you Sir, good information.

    I am not using any pooling features in my PDI settings, i was applied "show open tables" and result is: In_use =0 and Name_locked = 0 for all my tables.

    i applied below paramaters on Database connection option tab and i am trying how can i use connection pooling technique in PDI. will test once it is done. I hope your suggestion is, better to use connection pooling, then my issue has been resolved. am i correct?

    rewriteBatchedStatements=true
    useServerPrepStmts=false
    useCompression=true

  6. #6
    Join Date
    Aug 2011
    Posts
    360

    Default

    Hi,

    using connection pooling is good, but will not resolve your "issue", on contrary, having idling connection is what you have with connection pooling!

    In fact, I think you don't have an issue at all having these connections opened while designing stuff in Spoon!

    The only thing you should test, is if these connections are really closed once you have closed Spoon.

    Except for having lot of lines in the processlist, have you had other problems?
    If not, you should forget all of this and not waste any time more on that!

  7. #7
    Join Date
    Aug 2015
    Posts
    313

    Default

    Quote Originally Posted by Mathias.CH View Post
    Hi,

    using connection pooling is good, but will not resolve your "issue", on contrary, having idling connection is what you have with connection pooling!

    In fact, I think you don't have an issue at all having these connections opened while designing stuff in Spoon!
    Yes, as per my observation there is NO issue from PDI perspective but the problem is when the connections are in sleep mode, database machine CPU usage reaching to 100%, this is the MAIN problem for me.

    Quote Originally Posted by Mathias.CH View Post
    The only thing you should test, is if these connections are really closed once you have closed Spoon.

    Except for having lot of lines in the processlist, have you had other problems?
    If not, you should forget all of this and not waste any time more on that!
    YES, when i closed spoon GUI then closing all process list connections.

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.