From: MARK CALLAGHAN
Date: March 11 2009 6:46pm
Subject: Re: Is active_index thread safe?
List-Archive: http://lists.mysql.com/internals/36378
Message-Id: <f0661f8c0903111146x23b53fddo5d67df0603aaf80f@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

On Wed, Mar 11, 2009 at 11:38 AM, Ron Hu <ron@stripped> wrote:
> Hi Sergei:
>
> I use mysql-5.1.31 and observe that a table handler is shared by two user
> threads. =A0Here is my experiment:
>
> Step 1: Prepare a test table:
>
> use test;
> CREATE TABLE Department ( DeptNo SMALLINT not null, DeptName char(32) not
> null,
> =A0 =A0Budget INT null, Description varchar(62) null,
> =A0 =A0PRIMARY KEY (DeptNo), UNIQUE INDEX idx_DeptName ( DeptName ) =A0)
> =A0 =A0ENGINE=3D 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=3D1;
>
> I added some debugging code in ha_innodb to print the handler and thd
> values:
>
> executing ha_innobase::external_lock(thd=3D0x05898440, lock_type=3D2),
> handler=3D0x058c8410
> executing ha_innobase::index_read(...) , handler=3D0x058c8410
> executing ha_innobase::external_lock(thd=3D0x05898440, lock_type=3D0),
> handler=3D0x058c8410
>
>
> 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=3D'Sales';
>
> Here is the handler and thd values:
>
> executing ha_innobase::external_lock(thd=3D0x058c3f88, lock_type=3D2),
> handler=3D0x058c8410
> executing ha_innobase::index_read(...) , handler=3D0x058c8410
> executing ha_innobase::external_lock(thd=3D0x058c3f88, lock_type=3D0),
> handler=3D0x058c8410
>
> 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. =A0We can also use LOCK
> TABLE statement to have one thread insert records and force the other thr=
ead
> wait on its SQL statement. =A0Again I observe same table handler is share=
d by
> two user threads.
>
> Is this a bug in mysql-5.1.31?

Ron,

Sergei stated that handler instances are not shared _concurrently_.
Your example does not demonstrate concurrent sharing. They are reused
by different connections.


--=20
Mark Callaghan
mdcallag@stripped