List:Internals« Previous MessageNext Message »
From:Ron Hu Date:March 11 2009 6:51pm
Subject:RE: Is active_index thread safe? --Resend
View as plain text  
Resend this email in order to have the right format.
======================================

Hi Sergei:

I use mysql-5.1.31 and observe that a table handler is shared by two user
threads.  Here is my experiment:

Step 1: Prepare a test table:

use test;
CREATE TABLE Department ( DeptNo SMALLINT not null, DeptName char(32) not
null,
    Budget INT null, Description varchar(62) null,
    PRIMARY KEY (DeptNo), UNIQUE INDEX idx_DeptName ( DeptName )  )
    ENGINE= INNODB ;
INSERT Department VALUES (1, 'Engineering', 100000, 'filler_d'); 
INSERT Department VALUES (2, 'Marketing', 200000, 'filler_d'); 
INSERT Department VALUES (3, 'Sales', 500000, 'filler_d'); 
INSERT Department VALUES (4, 'Marketing2', 200000, null ); 
INSERT Department VALUES (5, 'Sales2', 500000, null );

Step 2: login user root and issue SELECT statement against the test table

% mysql -u root test
mysql> SELECT * FROM Department WHERE DeptNo=1;

I added some debugging code in ha_innodb to print the handler and thd
values:

executing ha_innobase::external_lock(thd=0x05898440, lock_type=2),
handler=0x058c8410 
executing ha_innobase::index_read(...) , handler=0x058c8410 
executing ha_innobase::external_lock(thd=0x05898440, lock_type=0),
handler=0x058c8410


Step 3: login user root again with another mysql client and issue SELECT
statement against the test table

% mysql -u root test
mysql> SELECT * FROM Department WHERE DeptName='Sales';

Here is the handler and thd values:

executing ha_innobase::external_lock(thd=0x058c3f88, lock_type=2),
handler=0x058c8410 
executing ha_innobase::index_read(...) , handler=0x058c8410 
executing ha_innobase::external_lock(thd=0x058c3f88, lock_type=0),
handler=0x058c8410

Note that same table handler (value 0x058c8410) is used by two different
user threads.

In both clients, we can execute other SQL statements and I found that the
same table handler is shared by two user threads.  We can also use LOCK
TABLE statement to have one thread insert records and force the other thread
wait on its SQL statement.  Again I observe same table handler is shared by
two user threads.

I also set breakpoints in ha_myisam.cc and found that the same table handler
is shared by two user threads.

Is this a bug in mysql-5.1.31? 

Thanks.

  -Ron


> -----Original Message-----
> From: Sergei Golubchik [mailto:serg@stripped]
> Sent: Wednesday, March 11, 2009 1:16 AM
> To: Ron Hu
> Cc: internals@stripped
> Subject: Re: Is active_index thread safe?
> 
> Hi, Ron!
> 
> On Mar 10, Ron Hu wrote:
> > Hi,
> >
> > active_index is a class member variable in MySQL's handler class.  MySQL
> > query processor uses this variable to tell storage engine which index id
> it
> > is using for a given table.  Storage engine may override the index id
> used
> > for a given table used in a SQL statement by assigning a specific index
> id
> > to active_index.
> >
> > I observed that an instance of handler object is created for each table
> > descriptor.  A table handler may be shared by two concurrent users if
> they
> > access same table at the same time.  Suppose a table t1 has two indexes
> idx1
> 
> no, a table handler may *not* be shared by two concurrent users even if
> they access same table at the same time.
> 
> > and idx2.  User 1 needs to use idx1 while user 2 needs to use idx2.  If
> > these two users run their queries concurrently, how can MySQL and
> storage
> > engine guarantee the right active_index value is used for two concurrent
> > users?
> 
> See above, handler objects aren't shared between threads.
> 
> Regards / Mit vielen GrЭъen,
> Sergei
> 
> --
>    __  ___     ___ ____  __
>   /  |/  /_ __/ __/ __ \/ /   Sergei Golubchik <serg@stripped>
>  / /|_/ / // /\ \/ /_/ / /__  Principal Software Engineer/Server Architect
> /_/  /_/\_, /___/\___\_\___/  Sun Microsystems GmbH, HRB MЭnchen 161028
>        <___/                  Sonnenallee 1, 85551 Kirchheim-Heimstetten
> GeschДftsfЭhrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer
> Vorsitzender des Aufsichtsrates: Martin HДring
> 
> --
> MySQL Internals Mailing List
> For list archives: http://lists.mysql.com/internals
> To unsubscribe:    http://lists.mysql.com/internals?unsub=1


Thread
Is active_index thread safe?Ron Hu11 Mar
  • Re: Is active_index thread safe?Sergei Golubchik11 Mar
    • RE: Is active_index thread safe?Ron Hu11 Mar
      • Re: Is active_index thread safe?MARK CALLAGHAN11 Mar
      • Re: Is active_index thread safe?Sergei Golubchik12 Mar
        • Is handler object shared by two concurrent users?Ron Hu12 Mar
          • Re: Is handler object shared by two concurrent users?Sergei Golubchik16 Mar
            • RE: Is handler object shared by two concurrent users?Ron Hu16 Mar
    • RE: Is active_index thread safe? --ResendRon Hu11 Mar