• MySQL知识库 :: performance
  • How can two threads use the same temporary table?

  • Summary

    Two sessions may use the same name for a temporary table, at the same time. However, if the binary log is used to recover from a backup,this·may result in data corruption.

    Background

    The binary log in MySQL tracks all statements which modify the database.It logs the text of each such statement (logical-level logging), not the actual data which were changed (row-level logging). The binary log issued to transfer data from master to slave in MySQL replication, and forincremental backups (the ability to roll forward from the time of a full backup to a later point in time). MySQL allows the creation of TEMPORARY tables. These tables differ fromnormal tables in two ways. First, they are dropped automatically at the end of the session. Second, they are visible only to the session. Another client session can create a TEMPORARY table with the same name, in the same database, and the two tables are entirely separate.

    Detailed Problem Description

    When a session uses a TEMPORARY table in a statement that modifies data,that statement is entered in the binary log. All entries in the binary log are tagged with the ID of the session which created them. When used with MySQL replication, this tag allows the slave to differentiate between two TEMPORARY tables created with the same name in separate sessions.However, when the binary log is used to roll forward from a full backup to a point in time, TEMPORARY tables which have the same name in different sessions will become confused with each other. Here is an example that demonstrates the problem:Assume we have created this table:

    CREATE TABLE results 
    (session_id CHAR(1), val INT);
    

    Then we run two separate sessions at the same time. (I've called them 'a' and 'b'.)

    ------------------------------------------------------------------------ 
    $mysql --prompt='a> ' test
    
    Reading table information for completion of table and column names 
    You can turn off this feature to get a quicker startup with -A 
          
    Welcome to the MySQL monitor.  
    Commands end with ; or \g. 
    Your MySQL connection id is 4 to server version: 4.0.23-debug-log
    
    Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
    
    a> CREATE TEMPORARY TABLE tmp_results (val INT);
    Query OK, 0 rows affected (0.07 sec)
    
    $ mysql --prompt='b> ' test
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A
          
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 5 to server version: 4.0.23-debug-log
          
    Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
          
    b> create temporary table tmp_results (val int);Query OK, 0 rows affected (0.01 sec)
          
    a> insert into tmp_results (val) values (1), (2);
    Query OK, 2 rows affected (0.06 sec)
    Records: 2  Duplicates: 0  Warnings: 0
          
    b> insert into tmp_results (val) values (3), (4); 
          
    Query OK, 2 rows affected (0.01 sec)
    Records: 2  Duplicates: 0  Warnings: 0
          
    a> insert into results (session_id, val) select 'a', val from tmp_results;
          
    Query OK, 2 rows affected (0.05 sec)
    Records: 2  Duplicates: 0  Warnings: 0
          
    b> insert into results (session_id, val) select 'b', val from tmp_results;
          
    Query OK, 2 rows affected (0.01 sec)
    Records: 2  Duplicates: 0  Warnings: 0
    
    a> select * from results;
    +------------+------+
    | session_id | val  |
    +------------+------+
    | a          |    1 |
    | a          |    2 |
    | b          |    3 |
    | b          |    4 |
    +------------+------+
    4 rows in set (0.01 sec)
          
    a> Bye
    b> Bye
    ------------------------------------------------------------------------
    

    Now, this is all correct, and a replication slave will have all of the correct data in the results table. However, here is the output of the 'mysqlbinlog' program, which is used to convert the binary log into an SQL script which is applied to a full backup to roll forward to a later point in time.

          
    ------------------------------------------------------------------------
    /*!40019 SET @@session.max_insert_delayed_threads=0*/;
    # at 4
    #041123 15:01:40 server id 33400  log_pos 4     
    Start: binlog v 3, server v 4.0.23-debug-log created 041123 15:01:40 at startup
    # at 79#041123 15:03:52 server id 33400  log_pos 79    Query   thread_id=4     
    exec_time=0     error_code=0
    use test;
    
    SET TIMESTAMP=1101247432;
    create temporary table tmp_results (val int);
    # at 158
    #041123 15:04:02 server id 33400  log_pos 158   Query   thread_id=5     
    exec_time=0     error_code=0
    
    SET TIMESTAMP=1101247442;
    create temporary table tmp_results (val int);
    # at 237#041123 15:04:25 server id 33400  log_pos 237   Query   thread_id=4     
    exec_time=0     error_code=0
          
    SET TIMESTAMP=1101247465;
    insert into tmp_results (val) values (1), (2);
    # at 317#041123 15:04:49 server id 33400  log_pos 317   Query   thread_id=5     
    exec_time=0     error_code=0
    
    SET TIMESTAMP=1101247489;
    insert into tmp_results (val) values (3), (4);
    # at 397#041123 15:07:03 server id 33400  log_pos 485   Query   thread_id=4     
    exec_time=0     error_code=0
          
    SET TIMESTAMP=1101247623;
    insert into results (session_id, val) select 'a', val from tmp_results;
    # at 502#041123 15:07:37 server id 33400  log_pos 590   Query   thread_id=5     
    exec_time=0     error_code=0
          
    SET TIMESTAMP=1101247657;
    insert into results (session_id, val) select 'b', val from tmp_results;
    # at 607#041123 15:07:57 server id 33400  log_pos 695   Query   thread_id=4     
    exec_time=0     error_code=0
          
    SET TIMESTAMP=1101247677;
          
    DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `test`.`tmp_results`;
    # at 705#041123 15:07:58 server id 33400  log_pos 793   Query   thread_id=5     
    exec_time=0     error_code=0
          
    SET TIMESTAMP=1101247678;
    DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `test`.`tmp_results`;
     ------------------------------------------------------------------------
    

    These statements will all be run in one session when restoring from a backup. As you can see, the statements which applied to the 'tmp_results'table in session 'a' can not be distinguished from those of session 'b'.If we replay this log, the first problem is I get an error: ERROR 1050: Table 'tmp_results' already exists The two CREATE TEMPORARY TABLE statements conflict. However, the rest ofthe statements run fine, and return no errors or warnings. However, if at the end I examine the results:

    mysql> select * from results;
    
    +------------+------+
    | session_id | val  |
    +------------+------+
    | a          |    1 |
    | a          |    2 |
    | a          |    3 |
    | a          |    4 |
    | b          |    1 |
    | b          |    2 |
    | b          |    3 |
    | b          |    4 |
    +------------+------+
    8 rows in set (0.00 sec)
    

    The INSERT statements, which previously had affected two separate tables, now both affect the same table. So session 'a' is wrongly paired with values 3 and 4, and session 'b' is wrongly paired with values 1 and 2.

    Solutions

    There are a few solutions to this problem. The first is to upgrade to MySQL version 4.1 or later. The MySQL 4.1 server knows about a specialvariable called @@session.pseudo_thread_id, which it uses to mimicsession-specific behavior. And the mysqlbinlog program in MySQL 4.1 knows to set that variable to the appropriate value in the SQL script itgenerates. If you can not upgrade your MySQL installation, then the other solution isto name all your temporary tables uniquely. Ensure that no two sessions will use the same temporary table name at the same time. The CONNECTION_ID() function can be used to get a unique number.