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.
Is this a bug in mysql-5.1.31?
Thanks.
-Ron Hu
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
