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. 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?
Ron,
Sergei stated that handler instances are not shared _concurrently_.
Your example does not demonstrate concurrent sharing. They are reused
by different connections.
--
Mark Callaghan
mdcallag@stripped
