mysql释放锁_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=,

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);

}

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