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

    Join Date
    May 2013
    Posts
    17
    Rep Power
    0

    Why isn't commited transaction not reflected in another session?


    Hi,

    here' my code.
    Code:
    delimiter //
    
    CREATE PROCEDURE transfer_to_control_card_fcu_model()
    BEGIN
    DECLARE v_source_min_fcu_id int;
    DECLARE v_target_max_fcu_id int;
    DECLARE v_target_offset int;
    	SELECT min(fcu_id) INTO v_source_min_fcu_id from source_db.control_card_fcu_model;
    	SELECT v_source_min_fcu_id;
    	
    	SELECT max(fcu_id) INTO v_target_max_fcu_id from target_db.control_card_fcu_model;
    	SELECT v_target_max_fcu_id;
    	
    	IF v_target_max_fcu_id >= v_source_min_fcu_id  THEN
    		SET v_target_offset := v_target_max_fcu_id - v_source_min_fcu_id;
    		
    		insert into target_db.control_card_fcu_model (
    	fcu_id,
    	fcu_name,
    	creation_date,
    	last_update_date,
    	country_iso
    )
    SELECT
    source.fcu_id+v_target_offset,
    	source.fcu_name,
    	source.creation_date,
    	source.last_update_date,
    	source.country_iso
    FROM source_db.control_card_fcu_model source LEFT JOIN target_db.control_card_fcu_model target on source.country_iso = target.country_iso where target.country_iso is NULL ORDER BY source.fcu_id;
    	END IF;
    	
    
    END;
    //
    executing the code

    Code:
    SELECT CONNECTION_ID()//
    
    mysql>1
    
    mysql> call transfer_to_control_card_fcu_model()//
    +----------------------------+
    | v_source_min_fcu_id |
    +----------------------------+
    |                          3 |
    +----------------------------+
    1 row in set (0.00 sec)
    
    +----------------------------+
    | v_target_max_fcu_id |
    +----------------------------+
    |                          7 |
    +----------------------------+
    1 row in set (0.02 sec)
    
    Query OK, 5 rows affected (0.03 sec)
    
    mysql> SELECT * FROM target.control_card_fcu_model target//
    +--------+----------------+---------------+------------------+-------------+
    | fcu_id | fcu_name       | creation_date | last_update_date | country_iso |
    +--------+----------------+---------------+------------------+-------------+
    |      7 | Benjamain      | 2013-06-26    | 2013-06-26       | ESP         |
    |      8 | FX4321E        | 0000-00-00    | 2013-06-01       | SGP         |
    |      9 | FXDD 345       | 2013-06-01    | 2013-06-01       | SGP         |
    |     10 | FCU VN Model 1 | 2013-06-18    | 2013-06-18       | VNM         |
    |     11 | FCU VN Model 2 | 2013-06-18    | 2013-06-18       | VNM         |
    |     12 | FCU VN Model 0 | 2013-06-19    | 2013-06-19       | VNM         |
    +--------+----------------+---------------+------------------+-------------+
    6 rows in set (0.00 sec)
    
    mysql> commit;
        -> //
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> SELECT * FROM target.control_card_fcu_model target//
    +--------+----------------+---------------+------------------+-------------+
    | fcu_id | fcu_name       | creation_date | last_update_date | country_iso |
    +--------+----------------+---------------+------------------+-------------+
    |      7 | Benjamain      | 2013-06-26    | 2013-06-26       | ESP         |
    |      8 | FX4321E        | 0000-00-00    | 2013-06-01       | SGP         |
    |      9 | FXDD 345       | 2013-06-01    | 2013-06-01       | SGP         |
    |     10 | FCU VN Model 1 | 2013-06-18    | 2013-06-18       | VNM         |
    |     11 | FCU VN Model 2 | 2013-06-18    | 2013-06-18       | VNM         |
    |     12 | FCU VN Model 0 | 2013-06-19    | 2013-06-19       | VNM         |
    +--------+----------------+---------------+------------------+-------------+
    6 rows in set (0.00 sec)
    in another session
    Code:
    mysql> show variables where variable_name='autocommit';
    +---------------+-------+
    | Variable_name | Value |
    +---------------+-------+
    | autocommit    | OFF   |
    +---------------+-------+
    1 row in set (0.00 sec)
    
    mysql> SELECT * FROM target.control_card_fcu_model target;
    +--------+-----------+---------------+------------------+-------------+
    | fcu_id | fcu_name  | creation_date | last_update_date | country_iso |
    +--------+-----------+---------------+------------------+-------------+
    |      7 | Benjamain | 2013-06-26    | 2013-06-26       | ESP         |
    +--------+-----------+---------------+------------------+-------------+
    1 row in set (0.00 sec)
    
    mysql> rollback;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> SELECT * FROM target.control_card_fcu_model target;
    +--------+----------------+---------------+------------------+-------------+
    | fcu_id | fcu_name       | creation_date | last_update_date | country_iso |
    +--------+----------------+---------------+------------------+-------------+
    |      7 | Benjamain      | 2013-06-26    | 2013-06-26       | ESP         |
    |      8 | FX4321E        | 0000-00-00    | 2013-06-01       | SGP         |
    |      9 | FXDD 345       | 2013-06-01    | 2013-06-01       | SGP         |
    |     10 | FCU VN Model 1 | 2013-06-18    | 2013-06-18       | VNM         |
    |     11 | FCU VN Model 2 | 2013-06-18    | 2013-06-18       | VNM         |
    |     12 | FCU VN Model 0 | 2013-06-19    | 2013-06-19       | VNM         |
    +--------+----------------+---------------+------------------+-------------+
    6 rows in set (0.00 sec)
    
    mysql>
    Why isn't commited transaction not reflected in another session?

    thanks
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,777
    Rep Power
    348
    Originally Posted by mysqlnoob1
    Why isn't commited transaction not reflected in another session?
    This is due to the default isolation level in MySQL which is "REPEATABLE READ ".

    There are two ways to "solve" this:

    Comments on this post

    • mysqlnoob1 agrees : answer is correct
    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
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    17
    Rep Power
    0
    Hi Shammat,

    you are right
    with reference to http://dev.mysql.com/doc/refman/5.5/en/set-transaction.html
    With the GLOBAL keyword, the statement sets the default transaction level globally for all subsequent sessions. Existing sessions are unaffected.
    Code:
    mysql> set global transaction isolation level read committed;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> show variables where variable_name like '%isolation%';
    +---------------+-----------------+
    | Variable_name | Value           |
    +---------------+-----------------+
    | tx_isolation  | REPEATABLE-READ |
    +---------------+-----------------+
    1 row in set (0.00 sec)
    that's why I have to exit in the other session and relogin again.

    and many thanks for the solution and clarification

IMN logo majestic logo threadwatch logo seochat tools logo