List:Internals« Previous MessageNext Message »
From:Ron Hu Date:March 12 2009 6:34pm
Subject:Is handler object shared by two concurrent users?
View as plain text  
Hi Sergei:

Thanks for your reply.  It helps.  I have a more complex test case to show
that same handler is shared by two concurrent users to some degree.  I want
to know the scope where a handler is not shared among users.  Is it between
the pair of ::external_lock calls (imposing a lock and release a lock)?  If
so, then store_lock is not within the scope.  

Also some methods such as delete_table is not within the pair of
::external_lock calls.  What other methods are not within the pair of
::external_lock calls?

Please advise.

Thanks.

-Ron

Here is my test script.  

======================================

--  drop locked tables from another connection
--

-- t215x200=con1.create_table(pk1) 
-- con1.lock_write(t215x200)
-- TRY: con2.drop(t215x200)  && wait
-- con1.insert(t215x200)
-- con1.unlock(t215x200)
-- con1.select(t215x200) && error
-- con2.select(t215x200) && error

connect (conn1,localhost,root,,test);

connection conn1;

eval set session storage_engine = scaledb;

set autocommit = 1;

--disable_warnings
drop table if exists t215x200;
--enable_warnings

create table t215x200 (
c1 int auto_increment primary key,
c2 varchar(32),
c3 double
) engine=scaledb;

lock tables t215x200 write;

# 
connect (conn2,localhost,root,,test);
connection conn2;
send drop table t215x200;

#
connection conn1;

insert into t215x200 values
(null,'aaa',1.11),
(null,'bbb',2.22),
(null,'ccc',3.33);

select count(*) from t215x200;

unlock tables;

-- table should be deleted
select count(*) as CON_1_RESULT from t215x200;
disconnect conn1;

connection conn2;

-- table should be deleted
select count(*) as CON_2_RESULT from t215x200;
disconnect conn2;

=====================

I save the above script into a file called "215x2.test" and then use this
command:
  mysqltest -uroot test < 215x2.test

I added some debugging message in ha_innodb.cc to print out handler and thd
values.  Here is my output:

executing ha_innobase::create(...) 
executing ha_innobase::ha_innobase(...), handler=0x058d75c8
executing ha_innobase::ha_innobase(...), handler=0x058d79e8
......
executing ha_innobase::store_lock(thd=058c3f88, lock_type=12),
handler=0x058d79e8, query:lock tables t215x200 write
executing ha_innobase::external_lock(thd=0x058c3f88, lock_type=4),
handler=0x058d79e8, query:lock tables t215x200 write
executing ha_innobase::store_lock(thd=058d51d8, lock_type=-1),
handler=0x058d79e8, query:drop table t215x200  <======
executing ha_innobase::start_stmt(thd=0x058c3f88, lock_type=8),
handler=0x058d79e8, query:insert into t215x200 values ...
executing ha_innobase::write_row(...), thd=058c3f88, handler=0x058d79e8,
query:insert into t215x200 values ...
executing ha_innobase::write_row(...), thd=058c3f88, handler=0x058d79e8,
query:insert into t215x200 values ...
executing ha_innobase::write_row(...), thd=058c3f88, handler=0x058d79e8,
query:insert into t215x200 values ...
executing innobase_commit(thd=058c3f88, all=false
executing ha_innobase::start_stmt(thd=0x058c3f88, lock_type=2),
handler=0x058d79e8, query:select count(*) from t215x200
executing ha_innobase::external_lock(thd=0x058c3f88, lock_type=0),
handler=0x058d79e8, query:unlock tables
executing ha_innobase::ha_innobase(...), handler=0x058ca4e8
executing ha_innobase::delete_table(name=.\test\t215x200), handler=058ca4e8,
thd=058d51d8, query:drop table t215x200 <======
executing innobase_close_connection(thd=058c3f88)
executing innobase_close_connection(thd=058d51d8)

=====================

In the above printout, when the second connection (thd=058d51d8) first calls
::store_lock for SQL statement "drop table t215x200", it shares same handler
0x058d79e8
with thd=0x058c3f88.  However connection thd=058d51d8 is blocked by MySQL.
When it resumes execution again, MySQL assigns it another
handler=0x058ca4e8.


Thanks.

-Ron Hu


> -----Original Message-----
> From: Sergei Golubchik [mailto:serg@stripped]
> Sent: Thursday, March 12, 2009 1:38 AM
> To: Ron Hu
> Cc: internals@stripped
> Subject: Re: Is active_index thread safe?
> 
> Hi, Ron!
> 
> On Mar 11, Ron Hu 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:
> ...
> > Note that same table handler (value 0x058c8410) is used by two different
> > user threads.
> 
> But it's not shared between threads. Only your first threads is using it
> exclusively. Then it releases it, and the handler object is migrated to
> another thread. Then this other thread is using it exclusively. No two
> threads can use a handler object at the same time, it wasn't designed
> for that.
> 
> When ::external_lock was called the handler object is locked by a
> thread, no other thread may access it until ::external_lock(F_UNLCK) is
> called. After that an object may go to a free pool and be picked up by a
> different thread.
> 
> 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