锁多表(The table cache: locking multiple tables)
使用一种尝试和回退(try and back-off)的技术来避免死锁(乐观锁)
为了DDL操作的一套诀窍，如使锁升级或者防止DDL失效
LOCK_open问题
Lock_open互斥量：
保护table cache缓存内的结构
分组存储引擎内的表和对象的.frm文件的创建，也为RENAME操作提供原子性操作
在每个语句访问表时会使用它两次：在open_tables()和close_thread_tables()
在使用DDL操作时，磁盘读写和甚至同步(sync)都会使用它
5.ALTER TABLE例子
ALTER TABLE执行的简化计划：
以TL_WRITE_ALLOW_READ的打开和加锁表
创建一个以临时名字的被ALTER的复制表
强制并等待直到表的所有实例都关闭(锁升级)
交换新和旧的版本
删除旧的版本
这是一个常规的情况，还有一些被优化的情况。
ALTER TABLE执行的调试：
T@8: | query: alter table t1 add column k int T@8: | >mysql_parse
T@8: | | >mysql_execute_command
T@8: | | | >mysql_alter_table
T@8: | | | | >open_ltable
T@8: | | | | | >open_table
T@8: | | | | | <open_table
T@8: | | | | | >mysql_lock_tables
T@8: | | | | | | >get_lock_data
T@8: | | | | | | | >ha_innobase::store_lock T@8: | | | | | | | <ha_innobase::store_lock T@8: | | | | | | <get_lock_data
T@8: | | | | | | >lock_external
T@8: | | | | | | | >ha_innobase::external_lock T@8: | | | | | | | | enter: lock_type: 1
T@8: | | | | | | | | >trans_register_ha
T@8: | | | | | | | | | enter: stmt
T@8: | | | | | | | | <trans_register_ha
T@8: | | | | | | | <ha_innobase::external_lock T@8: | | | | | | <lock_external
T@8: | | | | | | >thr_multi_lock
T@8: | | | | | | | >thr_lock
T@8: | | | | | | | <thr_lock
T@8: | | | | | | <thr_multi_lock
T@8: | | | | | <mysql_lock_tables
T@8: | | | | <open_ltable
T@8: | | | | >mysql_create_table
T@8: | | | | <mysql_create_table
T@8: | | | | >open_temporary_table T
@8: | | | | | >openfrm
T@8: | | | | | | >handler::ha_open
T@8: | | | | | | | enter: name: ./test/#sql-3081_1 db_type: 12 db_stat: 7 mode: 2 lock_test: 2
T@8: | | | | | | | >ha_innobase::open
T@8: | | | | | | | <ha_innobase::open
T@8: | | | | | | <handler::ha_open
T@8: | | | | | <openfrm
T@8: | | | | <open_temporary_table
T@8: | | | | >copy_data_between_tables
T@8: | | | | <copy_data_between_tables
T@8: | | | | >closefrm
T@8: | | | | <closefrm
T@8: | | | | >close_cached_table
T@8: | | | | | enter: table: t1
T@8: | | | | | >wait_while_table_is_used
T@8: | | | | | | >get_lock_data
T@8: | | | | | | <get_lock_data
T@8: | | | | | | >thr_abort_locks
T@8: | | | | | | <thr_abort_locks
T@8: | | | | | | >remove_table_from_cache
T@8: | | | | | | | enter: Table: 'test.t1' flags: 2
T@8: | | | | | | <remove_table_from_cache
T@8: | | | | | <wait_while_table_is_used
T@8: | | | | | >mysql_unlock_tables
T@8: | | | | | | >thr_multi_unlock
T@8: | | | | | | | lock: data: 0x8b7f9b0 count: 1
T@8: | | | | | | | >thr_unlock
T@8: | | | | | | | <thr_unlock
T@8: | | | | | | <thr_multi_unlock
T@8: | | | | | | >unlock_external
T@8: | | | | | | | >ha_innobase::external_lock T@8: | | | | | | | <ha_innobase::external_lock T@8: | | | | | | <unlock_external
T@8: | | | | | <mysql_unlock_tables
T@8: | | | | | >unlink_open_table
T@8: | | | | | | >hash_delete
T@8: | | | | | | | >free_cache_entry
T@8: | | | | | | | | >closefrm
T@8: | | | | | | | | | >ha_innobase::close
T@8: | | | | | | | | | <ha_innobase::close T@8: | | | | | | | | <closefrm
T@8: | | | | | | | <free_cache_entry
T@8: | | | | | | <hash_delete
T@8: | | | | | <unlink_open_table
T@8: | | | | <close_cached_table
T@8: | | | | >mysql_rename_table
T@8: | | | | | >ha_innobase::rename_table
T@8: | | | | | <ha_innobase::rename_table
T@8: | | | | <mysql_rename_table
T@8: | | | | >mysql_rename_table
T@8: | | | | | >ha_innobase::rename_table
T@8: | | | | | <ha_innobase::rename_table
T@8: | | | | <mysql_rename_table
T@8: | | | | >my_delete
T@8: | | | | | my: name ./test/#sql2-3081-1.frm MyFlags 0
T@8: | | | | <my_delete
T@8: | | | | >ha_delete_table
T@8: | | | | | >ha_innobase::delete_table
T@8: | | | | | <ha_innobase::delete_table
T@8: | | | | <ha_delete_table
T@8: | | | | >ha_commit_trans
T@8: | | | | <ha_commit_trans
T@8: | | | | >ha_commit_trans
T@8: | | | | <ha_commit_trans
T@8: | | | <mysql_alter_table
T@8: | | <mysql_execute_command
T@8: | <mysql_parse
T@8: <dispatch_command
T@8: | | >mysql_execute_command
T@8: | | | >mysql_alter_table
T@8: | | | | >open_ltable
T@8: | | | | | >open_table
T@8: | | | | | <open_table
T@8: | | | | | >mysql_lock_tables
T@8: | | | | | | >get_lock_data
T@8: | | | | | | | >ha_innobase::store_lock T@8: | | | | | | | <ha_innobase::store_lock T@8: | | | | | | <get_lock_data
T@8: | | | | | | >lock_external
T@8: | | | | | | | >ha_innobase::external_lock T@8: | | | | | | | | enter: lock_type: 1
T@8: | | | | | | | | >trans_register_ha
T@8: | | | | | | | | | enter: stmt
T@8: | | | | | | | | <trans_register_ha
T@8: | | | | | | | <ha_innobase::external_lock T@8: | | | | | | <lock_external
T@8: | | | | | | >thr_multi_lock
T@8: | | | | | | | >thr_lock
T@8: | | | | | | | <thr_lock
T@8: | | | | | | <thr_multi_lock
T@8: | | | | | <mysql_lock_tables
T@8: | | | | <open_ltable
T@8: | | | | >mysql_create_table
T@8: | | | | <mysql_create_table
T@8: | | | | >open_temporary_table T
@8: | | | | | >openfrm
T@8: | | | | | | >handler::ha_open
T@8: | | | | | | | enter: name: ./test/#sql-3081_1 db_type: 12 db_stat: 7 mode: 2 lock_test: 2
T@8: | | | | | | | >ha_innobase::open
T@8: | | | | | | | <ha_innobase::open
T@8: | | | | | | <handler::ha_open
T@8: | | | | | <openfrm
T@8: | | | | <open_temporary_table
T@8: | | | | >copy_data_between_tables
T@8: | | | | <copy_data_between_tables
T@8: | | | | >closefrm
T@8: | | | | <closefrm
T@8: | | | | >close_cached_table
T@8: | | | | | enter: table: t1
T@8: | | | | | >wait_while_table_is_used
T@8: | | | | | | >get_lock_data
T@8: | | | | | | <get_lock_data
T@8: | | | | | | >thr_abort_locks
T@8: | | | | | | <thr_abort_locks
T@8: | | | | | | >remove_table_from_cache
T@8: | | | | | | | enter: Table: 'test.t1' flags: 2
T@8: | | | | | | <remove_table_from_cache
T@8: | | | | | <wait_while_table_is_used
T@8: | | | | | >mysql_unlock_tables
T@8: | | | | | | >thr_multi_unlock
T@8: | | | | | | | lock: data: 0x8b7f9b0 count: 1
T@8: | | | | | | | >thr_unlock
T@8: | | | | | | | <thr_unlock
T@8: | | | | | | <thr_multi_unlock
T@8: | | | | | | >unlock_external
T@8: | | | | | | | >ha_innobase::external_lock T@8: | | | | | | | <ha_innobase::external_lock T@8: | | | | | | <unlock_external
T@8: | | | | | <mysql_unlock_tables
T@8: | | | | | >unlink_open_table
T@8: | | | | | | >hash_delete
T@8: | | | | | | | >free_cache_entry
T@8: | | | | | | | | >closefrm
T@8: | | | | | | | | | >ha_innobase::close
T@8: | | | | | | | | | <ha_innobase::close T@8: | | | | | | | | <closefrm
T@8: | | | | | | | <free_cache_entry
T@8: | | | | | | <hash_delete
T@8: | | | | | <unlink_open_table
T@8: | | | | <close_cached_table
T@8: | | | | >mysql_rename_table
T@8: | | | | | >ha_innobase::rename_table
T@8: | | | | | <ha_innobase::rename_table
T@8: | | | | <mysql_rename_table
T@8: | | | | >mysql_rename_table
T@8: | | | | | >ha_innobase::rename_table
T@8: | | | | | <ha_innobase::rename_table
T@8: | | | | <mysql_rename_table
T@8: | | | | >my_delete
T@8: | | | | | my: name ./test/#sql2-3081-1.frm MyFlags 0
T@8: | | | | <my_delete
T@8: | | | | >ha_delete_table
T@8: | | | | | >ha_innobase::delete_table
T@8: | | | | | <ha_innobase::delete_table
T@8: | | | | <ha_delete_table
T@8: | | | | >ha_commit_trans
T@8: | | | | <ha_commit_trans
T@8: | | | | >ha_commit_trans
T@8: | | | | <ha_commit_trans
T@8: | | | <mysql_alter_table
T@8: | | <mysql_execute_command
T@8: | <mysql_parse
T@8: <dispatch_command
