mysql释放锁时机跟踪

      近几年学得比较迷茫，好像什么都学，一直想好好剖析一个好产品， 好好提升一下自我，好像进展蛮慢，博客也少写了。 人的精力是有限的，还是得在某一行深入。
      近来对mysql有些兴趣，专门学习了一下。
      我们都知道，数据库事务隔离级别下和普通sql加锁和释放锁的时机是不同的，
      mysql是如何实现的呢。（以mysql-6.0.11-alpha为例）
      mysql锁释放的代码在 lock_release_off_kernel。
     下面在红帽下跟踪：
     首先设置断点：
     break lock_release_off_kernel
     在mysql客户端，连接上面，在事务和非事务下执行，看看堆栈的不同
     非事务状态下面执行
     select * from test1 ;

     堆栈显示如下:
#0  lock_release_off_kernel (trx=0xb512ac68) at lock/lock0lock.c:3853
#1  0xb5dca803 in trx_commit_off_kernel (trx=0xb512ac68) at trx/trx0trx.c:853
#2  0xb5dcad19 in trx_commit_for_mysql (trx=0xb512ac68) at trx/trx0trx.c:1582
#3  0xb5dd2838 in innobase_commit_low (trx=0x0) at handler/ha_innodb.cc:1878
#4  0xb5dd5f42 in innobase_commit (hton=0x8f6c2e8, thd=0x982fec0, all=true)
    at handler/ha_innodb.cc:2021
#5  0xb5dd9f3e in ha_innobase::external_lock (this=0x97020e0, thd=0x982fec0,
    lock_type=2) at handler/ha_innodb.cc:7023
#6  0x082e25f1 in handler::ha_external_lock (this=0x97020e0, thd=0x982fec0,
    lock_type=2) at handler.cc:5450
#7  0x081cb7fe in unlock_external (thd=0x982fec0, table=<value optimized out>,
    count=1) at lock.cc:822
#8  0x081cb969 in mysql_unlock_read_tables (thd=0x982fec0, sql_lock=0x9831da0)
    at lock.cc:503
#9  0x082586f6 in JOIN::join_free (this=0x97eb8e8) at sql_select.cc:10445
#10 0x0825f0d9 in do_select (join=0x97eb8e8, fields=0x9831170, table=0x0,
    procedure=0x0) at sql_select.cc:15864
#11 0x08266372 in JOIN::exec (this=0x97eb8e8) at sql_select.cc:2886
#12 0x08266f5d in mysql_select (thd=0x982fec0, rref_pointer_array=0x98311e0,
    tables=0x96fb180, wild_num=1, fields=@0x9831170, conds=0x0, og_num=0,
    order=0x0, group=0x0, having=0x0, proc_param=0x0,
    select_options=2147764736, result=0x96fb618, unit=0x9830e48,
    select_lex=0x98310dc) at sql_select.cc:3067
#13 0x08267467 in handle_select (thd=0x982fec0, lex=0x9830dec,
    result=0x96fb618, setup_tables_done_option=0) at sql_select.cc:310
#14 0x081e0877 in execute_sqlcom_select (thd=0x982fec0, all_tables=0x96fb180)
    at sql_parse.cc:4922
#15 0x081e30af in mysql_execute_command (thd=0x982fec0) at sql_parse.cc:2150
#16 0x081ea940 in mysql_parse (thd=0x982fec0,
    inBuf=0x96fb048 "select * from test1", length=19,
    found_semicolon=0xb26f3f14) at sql_parse.cc:5937
#17 0x081eb82c in dispatch_command (command=COM_QUERY, thd=0x982fec0,
    packet=0x9826231 "", packet_length=19) at sql_parse.cc:1049
#18 0x081ecaec in do_command (thd=0x982fec0) at sql_parse.cc:731
#19 0x081dc327 in handle_one_connection (arg=0x982fec0) at sql_connect.cc:1146
#20 0x4dfe92db in start_thread (arg=0xb26f4790) at pthread_create.c:296
#21 0x006cf14e in clone () from /lib/libc.so.6
    由上可见，锁在join_free释放

    而事务模式下面

    start transaction;

    select * from test; --此时断点没有命中，必须下面执行commit

    commit;

    堆栈为

#0  lock_release_off_kernel (trx=0xb512ac68) at lock/lock0lock.c:3853
#1  0xb5dca803 in trx_commit_off_kernel (trx=0xb512ac68) at trx/trx0trx.c:853
#2  0xb5dcad19 in trx_commit_for_mysql (trx=0xb512ac68) at trx/trx0trx.c:1582
#3  0xb5dd2838 in innobase_commit_low (trx=0x0) at handler/ha_innodb.cc:1878
#4  0xb5dd5f42 in innobase_commit (hton=0x8f6c2e8, thd=0x982fec0, all=true)
    at handler/ha_innodb.cc:2021
#5  0x082e840a in ha_commit_one_phase (thd=0x982fec0, all=true)
    at handler.cc:1231
#6  0x082e897d in ha_commit_trans (thd=0x982fec0, all=true) at handler.cc:1199
#7  0x083925fc in trans_commit (thd=0x982fec0) at transaction.cc:136
#8  0x081e7972 in mysql_execute_command (thd=0x982fec0) at sql_parse.cc:4130
#9  0x081ea940 in mysql_parse (thd=0x982fec0, inBuf=0x96fb048 "commit",
    length=6, found_semicolon=0xb26f3f14) at sql_parse.cc:5937
#10 0x081eb82c in dispatch_command (command=COM_QUERY, thd=0x982fec0,
    packet=0x9826231 "commit", packet_length=6) at sql_parse.cc:1049
#11 0x081ecaec in do_command (thd=0x982fec0) at sql_parse.cc:731
#12 0x081dc327 in handle_one_connection (arg=0x982fec0) at sql_connect.cc:1146
#13 0x4dfe92db in start_thread (arg=0xb26f4790) at pthread_create.c:296
#14 0x006cf14e in clone () from /lib/libc.so.6

    可见这几个用法在释放锁的时机是不一样的。

    在那里产生分支呢？

    在下面的设置断点：

    break ha_innodb.cc:7021

    跟踪ha_innobase::external_lock下面这一行

    if (!thd_test_options(thd, OPTION_NOT_AUTOCOMMIT | OPTION_BEGIN)) {
         if (trx->active_trans != 0) {
                innobase_commit(ht, thd, TRUE);
         }
    } else {
         if (trx->isolation_level <= TRX_ISO_READ_COMMITTED
               && trx->global_read_view) {

               /* At low transaction isolation levels we let
                   each consistent read set its own snapshot */

              read_view_close_for_mysql(trx);
    }

   从上面可以看出有事务和没事务，事务隔离级别不同采用不同的处理方法。