#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    17
    Rep Power
    0

    Is there a way to prevent other sessions from writing to the tables in the procedure


    Hi,

    here' my code.
    Code:
    delimiter //
    
    SELECT CONNECTION_ID()//
    
    LOCK TABLES source.jos_daikin_control_card_fcu_model WRITE//
    
    CREATE PROCEDURE transfer_to_control_card_fcu_model()
    BEGIN
    DECLARE v_source_daikin_max_fcu_id int;
    DECLARE v_target_daikin_max_fcu_id int;
    	SELECT max(fcu_id) INTO v_source_daikin_max_fcu_id from source.control_card_fcu_model;
    	SELECT v_source_daikin_max_fcu_id;
    	
    	SELECT max(fcu_id) INTO v_target_daikin_max_fcu_id from target.control_card_fcu_model;
    	SELECT v_target_daikin_max_fcu_id;
    
    END;
    //
    
    ERROR 1192 (HY000): Can't execute the given command because you have active lock
    ed tables or an active transaction
    Is there a way to prevent other session from accessing the tables called in the procedure?

    thanks a lot!
  2. #2
  3. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Location
    spaceBAR Central
    Posts
    229
    Rep Power
    42
    This is some useful oracle info on locks, but your code doesn't look like oracle sql:
    http://www.datadisk.co.uk/html_docs/oracle/locking.htm
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,784
    Rep Power
    348
    ERROR 1192 (HY000): Can't execute the given command because you have active lock
    That is not an Oracle error message. You are in the wrong forum.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    17
    Rep Power
    0
    Have now shift to My SQL Help

    Need forum moderator this thread from this forum.

    thanks

IMN logo majestic logo threadwatch logo seochat tools logo