http://blog.itpub.net/22664653/viewspace-1791608/
http://blog.csdn.net/dba_waterbin/article/details/17371937
实验1:
mysql> show create table t; +-------+------------------------------------------------------------------------------------+ | Table | Create Table | +-------+------------------------------------------------------------------------------------+ | t | CREATE TABLE `t` ( `a` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-------+------------------------------------------------------------------------------------+ 1 row in set (0.18 sec)
mysql> select @@tx_isolation; +-----------------+ | @@tx_isolation | +-----------------+ | REPEATABLE-READ | +-----------------+ 1 row in set (0.00 sec)
insert into t select 1; insert into t select 2;
会话1：事物未提交
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from t where a=1; +------+ | a | +------+ | 1 | +------+ 1 row in set (0.19 sec)
会话2：执行DDL语句
mysql> alter table t add column comment varchar(200) default "123"; //阻塞了
会话3：
mysql> show processlist; +----+------+-----------+------+---------+------+---------------------------------+-------------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+------+---------+------+---------------------------------+-------------------------------------------------------------+ | 1 | root | localhost | test | Sleep | 418 | | NULL | | 2 | root | localhost | test | Query | 359 | Waiting for table metadata lock | alter table t add column comment varchar(200) default "123" | | 3 | root | localhost | test | Query | 0 | init | show processlist | +----+------+-----------+------+---------+------+---------------------------------+-------------------------------------------------------------+
GDB 调试：
[root@localhost test]# gdb -p 4373 //全部线程中断了 (gdb) info threads Id Target Id Frame 25 Thread 0x7f9c20367940 (LWP 4374) 0x00000030738005b4 in ?? () from /usr/lib64/libaio.so.1 24 Thread 0x7f9c1fb66940 (LWP 4375) 0x00000030738005b4 in ?? () from /usr/lib64/libaio.so.1 23 Thread 0x7f9c1f365940 (LWP 4376) 0x00000030738005b4 in ?? () from /usr/lib64/libaio.so.1 22 Thread 0x7f9c1eb64940 (LWP 4377) 0x00000030738005b4 in ?? () from /usr/lib64/libaio.so.1 21 Thread 0x7f9c1e363940 (LWP 4378) 0x00000030738005b4 in ?? () from /usr/lib64/libaio.so.1 20 Thread 0x7f9c1db62940 (LWP 4379) 0x00000030738005b4 in ?? () from /usr/lib64/libaio.so.1 19 Thread 0x7f9c1d361940 (LWP 4380) 0x00000030738005b4 in ?? () from /usr/lib64/libaio.so.1 18 Thread 0x7f9c1cb60940 (LWP 4381) 0x00000030738005b4 in ?? () from /usr/lib64/libaio.so.1 17 Thread 0x7f9c1c35f940 (LWP 4382) 0x00000030738005b4 in ?? () from /usr/lib64/libaio.so.1 16 Thread 0x7f9c1bb5e940 (LWP 4383) 0x00000030738005b4 in ?? () from /usr/lib64/libaio.so.1 15 Thread 0x7f9c1ab5c940 (LWP 4386) 0x00007f9c7f897280 in pthread_cond_timedwait@@GLIBC_2.3.2 () from /lib64/libpthread.so.0 14 Thread 0x7f9c19910940 (LWP 4387) 0x00007f9c7f897280 in pthread_cond_timedwait@@GLIBC_2.3.2 () from /lib64/libpthread.so.0 13 Thread 0x7f9c1910f940 (LWP 4388) 0x00007f9c7f897280 in pthread_cond_timedwait@@GLIBC_2.3.2 () from /lib64/libpthread.so.0 12 Thread 0x7f9c1890e940 (LWP 4389) 0x00007f9c7ed39172 in select () from /lib64/libc.so.6 11 Thread 0x7f9c1810d940 (LWP 4390) 0x00007f9c7f897019 in pthread_cond_wait@@GLIBC_2.3.2 () from /lib64/libpthread.so.0 10 Thread 0x7f9c1b35d940 (LWP 4391) 0x00007f9c7ed39172 in select () from /lib64/libc.so.6 9 Thread 0x7f9c1790c940 (LWP 4392) 0x00007f9c7f897019 in pthread_cond_wait@@GLIBC_2.3.2 () from /lib64/libpthread.so.0 8 Thread 0x7f9c0ffff940 (LWP 4393) 0x00007f9c7f897280 in pthread_cond_timedwait@@GLIBC_2.3.2 () from /lib64/libpthread.so.0 7 Thread 0x7f9c1710b940 (LWP 4394) 0x00007f9c7f897280 in pthread_cond_timedwait@@GLIBC_2.3.2 () from /lib64/libpthread.so.0 6 Thread 0x7f9c1690a940 (LWP 4395) 0x00007f9c7f89a9c8 in do_sigwait () from /lib64/libpthread.so.0 5 Thread 0x7f9c168c8940 (LWP 5335) 0x00007f9c7ed370b6 in poll () from /lib64/libc.so.6 4 Thread 0x7f9c16887940 (LWP 5367) 0x00007f9c7f897280 in pthread_cond_timedwait@@GLIBC_2.3.2 () from /lib64/libpthread.so.0 3 Thread 0x7f9c16846940 (LWP 5408) 0x00007f9c7f897019 in pthread_cond_wait@@GLIBC_2.3.2 () from /lib64/libpthread.so.0 2 Thread 0x7f9c16805940 (LWP 5684) 0x00007f9c7f897019 in pthread_cond_wait@@GLIBC_2.3.2 () from /lib64/libpthread.so.0 * 1 Thread 0x7f9c7fcaa6f0 (LWP 4373) 0x00007f9c7ed370b6 in poll () from /lib64/libc.so.6 (gdb)
(gdb) thread apply all bt Thread 25 (Thread 0x7f9c20367940 (LWP 4374)): #0 0x00000030738005b4 in ?? () from /usr/lib64/libaio.so.1 #1 0x0000000000ba441d in os_aio_linux_collect (array=0x1988ee0, segment=0, seg_size=256) at /usr/src/mysql-5.6.28/storage/innobase/os/os0file.cc:4975 #2 0x0000000000ba4be2 in os_aio_linux_handle (global_seg=0, message1=0x7f9c203670e0, message2=0x7f9c203670d8, type=0x7f9c203670d0) at /usr/src/mysql-5.6.28/storage/innobase/os/os0file.cc:5119 #3 0x0000000000d4deca in fil_aio_wait (segment=0) at /usr/src/mysql-5.6.28/storage/innobase/fil/fil0fil.cc:5796 #4 0x0000000000c6e122 in io_handler_thread (arg=0x1829c20 <n>) at /usr/src/mysql-5.6.28/storage/innobase/srv/srv0start.cc:492 #5 0x00007f9c7f89283d in start_thread () from /lib64/libpthread.so.0 #6 0x00007f9c7ed3ffdd in clone () from /lib64/libc.so.6 #7 0x0000000000000000 in ?? () Thread 24 (Thread 0x7f9c1fb66940 (LWP 4375)): #0 0x00000030738005b4 in ?? () from /usr/lib64/libaio.so.1 #1 0x0000000000ba441d in os_aio_linux_collect (array=0x197bc40, segment=0, seg_size=256) at /usr/src/mysql-5.6.28/storage/innobase/os/os0file.cc:4975 #2 0x0000000000ba4be2 in os_aio_linux_handle (global_seg=1, message1=0x7f9c1fb660e0, message2=0x7f9c1fb660d8, type=0x7f9c1fb660d0) at /usr/src/mysql-5.6.28/storage/innobase/os/os0file.cc:5119 #3 0x0000000000d4deca in fil_aio_wait (segment=1) at /usr/src/mysql-5.6.28/storage/innobase/fil/fil0fil.cc:5796 #4 0x0000000000c6e122 in io_handler_thread (arg=0x1829c28 <n+8>) at /usr/src/mysql-5.6.28/storage/innobase/srv/srv0start.cc:492 #5 0x00007f9c7f89283d in start_thread () from /lib64/libpthread.so.0 #6 0x00007f9c7ed3ffdd in clone () from /lib64/libc.so.6 #7 0x0000000000000000 in ?? () Thread 23 (Thread 0x7f9c1f365940 (LWP 4376)): #0 0x00000030738005b4 in ?? () from /usr/lib64/libaio.so.1 #1 0x0000000000ba441d in os_aio_linux_collect (array=0x1973980, segment=0, seg_size=256) at /usr/src/mysql-5.6.28/storage/innobase/os/os0file.cc:4975 #2 0x0000000000ba4be2 in os_aio_linux_handle (global_seg=2, message1=0x7f9c1f3650e0, message2=0x7f9c1f3650d8, type=0x7f9c1f3650d0) at /usr/src/mysql-5.6.28/storage/innobase/os/os0file.cc:5119 #3 0x0000000000d4deca in fil_aio_wait (segment=2) at /usr/src/mysql-5.6.28/storage/innobase/fil/fil0fil.cc:5796 #4 0x0000000000c6e122 in io_handler_thread (arg=0x1829c30 <n+16>) at /usr/src/mysql-5.6.28/storage/innobase/srv/srv0start.cc:492 #5 0x00007f9c7f89283d in start_thread () from /lib64/libpthread.so.0 #6 0x00007f9c7ed3ffdd in clone () from /lib64/libc.so.6 #7 0x0000000000000000 in ?? () Thread 22 (Thread 0x7f9c1eb64940 (LWP 4377)): #0 0x00000030738005b4 in ?? () from /usr/lib64/libaio.so.1 #1 0x0000000000ba441d in os_aio_linux_collect (array=0x1973980, segment=1, seg_size=256) at /usr/src/mysql-5.6.28/storage/innobase/os/os0file.cc:4975 #2 0x0000000000ba4be2 in os_aio_linux_handle (global_seg=3, message1=0x7f9c1eb640e0, message2=0x7f9c1eb640d8, type=0x7f9c1eb640d0) at /usr/src/mysql-5.6.28/storage/innobase/os/os0file.cc:5119 #3 0x0000000000d4deca in fil_aio_wait (segment=3) at /usr/src/mysql-5.6.28/storage/innobase/fil/fil0fil.cc:5796 #4 0x0000000000c6e122 in io_handler_thread (arg=0x1829c38 <n+24>) at /usr/src/mysql-5.6.28/storage/innobase/srv/srv0start.cc:492 #5 0x00007f9c7f89283d in start_thread () from /lib64/libpthread.so.0 #6 0x00007f9c7ed3ffdd in clone () from /lib64/libc.so.6 #7 0x0000000000000000 in ?? () Thread 21 (Thread 0x7f9c1e363940 (LWP 4378)): #0 0x00000030738005b4 in ?? () from /usr/lib64/libaio.so.1 #1 0x0000000000ba441d in os_aio_linux_collect (array=0x1973980, segment=2, seg_size=256) at /usr/src/mysql-5.6.28/storage/innobase/os/os0file.cc:4975 #2 0x0000000000ba4be2 in os_aio_linux_handle (global_seg=4, message1=0x7f9c1e3630e0, message2=0x7f9c1e3630d8, type=0x7f9c1e3630d0) at /usr/src/mysql-5.6.28/storage/innobase/os/os0file.cc:5119 #3 0x0000000000d4deca in fil_aio_wait (segment=4) at /usr/src/mysql-5.6.28/storage/innobase/fil/fil0fil.cc:5796 #4 0x0000000000c6e122 in io_handler_thread (arg=0x1829c40 <n+32>) at /usr/src/mysql-5.6.28/storage/innobase/srv/srv0start.cc:492 #5 0x00007f9c7f89283d in start_thread () from /lib64/libpthread.so.0 #6 0x00007f9c7ed3ffdd in clone () from /lib64/libc.so.6 #7 0x0000000000000000 in ?? () Thread 20 (Thread 0x7f9c1db62940 (LWP 4379)): #0 0x00000030738005b4 in ?? () from /usr/lib64/libaio.so.1 #1 0x0000000000ba441d in os_aio_linux_collect (array=0x1973980, segment=3, seg_size=256) at /usr/src/mysql-5.6.28/storage/innobase/os/os0file.cc:4975 #2 0x0000000000ba4be2 in os_aio_linux_handle (global_seg=5, message1=0x7f9c1db620e0, message2=0x7f9c1db620d8, type=0x7f9c1db620d0) at /usr/src/mysql-5.6.28/storage/innobase/os/os0file.cc:5119 #3 0x0000000000d4deca in fil_aio_wait (segment=5) at /usr/src/mysql-5.6.28/storage/innobase/fil/fil0fil.cc:5796 #4 0x0000000000c6e122 in io_handler_thread (arg=0x1829c48 <n+40>) at /usr/src/mysql-5.6.28/storage/innobase/srv/srv0start.cc:492 #5 0x00007f9c7f89283d in start_thread () from /lib64/libpthread.so.0 #6 0x00007f9c7ed3ffdd in clone () from /lib64/libc.so.6 #7 0x0000000000000000 in ?? () Thread 19 (Thread 0x7f9c1d361940 (LWP 4380)): #0 0x00000030738005b4 in ?? () from /usr/lib64/libaio.so.1 #1 0x0000000000ba441d in os_aio_linux_collect (array=0x19961a0, segment=0, seg_size=256) at /usr/src/mysql-5.6.28/storage/innobase/os/os0file.cc:4975 #2 0x0000000000ba4be2 in os_aio_linux_handle (global_seg=6, message1=0x7f9c1d3610e0, message2=0x7f9c1d3610d8, type=0x7f9c1d3610d0) at /usr/src/mysql-5.6.28/storage/innobase/os/os0file.cc:5119 #3 0x0000000000d4deca in fil_aio_wait (segment=6) at /usr/src/mysql-5.6.28/storage/innobase/fil/fil0fil.cc:5796 #4 0x0000000000c6e122 in io_handler_thread (arg=0x1829c50 <n+48>) at /usr/src/mysql-5.6.28/storage/innobase/srv/srv0start.cc:492 #5 0x00007f9c7f89283d in start_thread () from /lib64/libpthread.so.0 #6 0x00007f9c7ed3ffdd in clone () from /lib64/libc.so.6 #7 0x0000000000000000 in ?? () Thread 18 (Thread 0x7f9c1cb60940 (LWP 4381)): #0 0x00000030738005b4 in ?? () from /usr/lib64/libaio.so.1 #1 0x0000000000ba441d in os_aio_linux_collect (array=0x19961a0, segment=1, seg_size=256) at /usr/src/mysql-5.6.28/storage/innobase/os/os0file.cc:4975 #2 0x0000000000ba4be2 in os_aio_linux_handle (global_seg=7, message1=0x7f9c1cb600e0, message2=0x7f9c1cb600d8, type=0x7f9c1cb600d0) at /usr/src/mysql-5.6.28/storage/innobase/os/os0file.cc:5119 #3 0x0000000000d4deca in fil_aio_wait (segment=7) at /usr/src/mysql-5.6.28/storage/innobase/fil/fil0fil.cc:5796 #4 0x0000000000c6e122 in io_handler_thread (arg=0x1829c58 <n+56>) at /usr/src/mysql-5.6.28/storage/innobase/srv/srv0start.cc:492 #5 0x00007f9c7f89283d in start_thread () from /lib64/libpthread.so.0 #6 0x00007f9c7ed3ffdd in clone () from /lib64/libc.so.6 #7 0x0000000000000000 in ?? () Thread 17 (Thread 0x7f9c1c35f940 (LWP 4382)): #0 0x00000030738005b4 in ?? () from /usr/lib64/libaio.so.1 #1 0x0000000000ba441d in os_aio_linux_collect (array=0x19961a0, segment=2, seg_size=256) at /usr/src/mysql-5.6.28/storage/innobase/os/os0file.cc:4975 #2 0x0000000000ba4be2 in os_aio_linux_handle (global_seg=8, message1=0x7f9c1c35f0e0, message2=0x7f9c1c35f0d8, type=0x7f9c1c35f0d0) at /usr/src/mysql-5.6.28/storage/innobase/os/os0file.cc:5119 #3 0x0000000000d4deca in fil_aio_wait (segment=8) at /usr/src/mysql-5.6.28/storage/innobase/fil/fil0fil.cc:5796 #4 0x0000000000c6e122 in io_handler_thread (arg=0x1829c60 <n+64>) at /usr/src/mysql-5.6.28/storage/innobase/srv/srv0start.cc:492 #5 0x00007f9c7f89283d in start_thread () from /lib64/libpthread.so.0 #6 0x00007f9c7ed3ffdd in clone () from /lib64/libc.so.6 #7 0x0000000000000000 in ?? () Thread 16 (Thread 0x7f9c1bb5e940 (LWP 4383)): #0 0x00000030738005b4 in ?? () from /usr/lib64/libaio.so.1 #1 0x0000000000ba441d in os_aio_linux_collect (array=0x19961a0, segment=3, seg_size=256) at /usr/src/mysql-5.6.28/storage/innobase/os/os0file.cc:4975 #2 0x0000000000ba4be2 in os_aio_linux_handle (global_seg=9, message1=0x7f9c1bb5e0e0, message2=0x7f9c1bb5e0d8, type=0x7f9c1bb5e0d0) at /usr/src/mysql-5.6.28/storage/innobase/os/os0file.cc:5119 #3 0x0000000000d4deca in fil_aio_wait (segment=9) at /usr/src/mysql-5.6.28/storage/innobase/fil/fil0fil.cc:5796 #4 0x0000000000c6e122 in io_handler_thread (arg=0x1829c68 <n+72>) at /usr/src/mysql-5.6.28/storage/innobase/srv/srv0start.cc:492 #5 0x00007f9c7f89283d in start_thread () from /lib64/libpthread.so.0 #6 0x00007f9c7ed3ffdd in clone () from /lib64/libc.so.6 #7 0x0000000000000000 in ?? () Thread 15 (Thread 0x7f9c1ab5c940 (LWP 4386)): #0 0x00007f9c7f897280 in pthread_cond_timedwait@@GLIBC_2.3.2 () from /lib64/libpthread.so.0 #1 0x0000000000baa77b in os_cond_wait_timed (cond=0x4766d10, fast_mutex=0x4766cd0, abstime=0x7f9c1ab5c0c0) at /usr/src/mysql-5.6.28/storage/innobase/os/os0sync.cc:177 #2 0x0000000000baa9b0 in os_event_wait_time_low (event=0x4766cd0, time_in_usec=1000000, reset_sig_count=1) at /usr/src/mysql-5.6.28/storage/innobase/os/os0sync.cc:705 #3 0x0000000000b88945 in lock_wait_timeout_thread (arg=0x0) at /usr/src/mysql-5.6.28/storage/innobase/lock/lock0wait.cc:503 #4 0x00007f9c7f89283d in start_thread () from /lib64/libpthread.so.0 #5 0x00007f9c7ed3ffdd in clone () from /lib64/libc.so.6 #6 0x0000000000000000 in ?? () Thread 14 (Thread 0x7f9c19910940 (LWP 4387)): #0 0x00007f9c7f897280 in pthread_cond_timedwait@@GLIBC_2.3.2 () from /lib64/libpthread.so.0 #1 0x0000000000baa77b in os_cond_wait_timed (cond=0x194ebc0, fast_mutex=0x194eb80, abstime=0x7f9c19910080) at /usr/src/mysql-5.6.28/storage/innobase/os/os0sync.cc:177 #2 0x0000000000baa9b0 in os_event_wait_time_low (event=0x194eb80, time_in_usec=1000000, reset_sig_count=1) at /usr/src/mysql-5.6.28/storage/innobase/os/os0sync.cc:705 #3 0x0000000000c65ce8 in srv_error_monitor_thread (arg=0x0) at /usr/src/mysql-5.6.28/storage/innobase/srv/srv0srv.cc:1769 #4 0x00007f9c7f89283d in start_thread () from /lib64/libpthread.so.0 #5 0x00007f9c7ed3ffdd in clone () from /lib64/libc.so.6 #6 0x0000000000000000 in ?? () Thread 13 (Thread 0x7f9c1910f940 (LWP 4388)): #0 0x00007f9c7f897280 in pthread_cond_timedwait@@GLIBC_2.3.2 () from /lib64/libpthread.so.0 #1 0x0000000000baa77b in os_cond_wait_timed (cond=0x194ec50, fast_mutex=0x194ec10, abstime=0x7f9c1910f090) at /usr/src/mysql-5.6.28/storage/innobase/os/os0sync.cc:177 #2 0x0000000000baa9b0 in os_event_wait_time_low (event=0x194ec10, time_in_usec=5000000, reset_sig_count=1) at /usr/src/mysql-5.6.28/storage/innobase/os/os0sync.cc:705 #3 0x0000000000c66e58 in srv_monitor_thread (arg=0x0) at /usr/src/mysql-5.6.28/storage/innobase/srv/srv0srv.cc:1538 #4 0x00007f9c7f89283d in start_thread () from /lib64/libpthread.so.0 #5 0x00007f9c7ed3ffdd in clone () from /lib64/libc.so.6 #6 0x0000000000000000 in ?? () Thread 12 (Thread 0x7f9c1890e940 (LWP 4389)): #0 0x00007f9c7ed39172 in select () from /lib64/libc.so.6 #1 0x0000000000bab334 in os_thread_sleep (tm=1000000) at /usr/src/mysql-5.6.28/storage/innobase/os/os0thread.cc:261 #2 0x0000000000c64c96 in srv_master_sleep () at /usr/src/mysql-5.6.28/storage/innobase/srv/srv0srv.cc:2300 #3 0x0000000000c65853 in srv_master_thread (arg=0x0) at /usr/src/mysql-5.6.28/storage/innobase/srv/srv0srv.cc:2344 #4 0x00007f9c7f89283d in start_thread () from /lib64/libpthread.so.0 #5 0x00007f9c7ed3ffdd in clone () from /lib64/libc.so.6 #6 0x0000000000000000 in ?? () Thread 11 (Thread 0x7f9c1810d940 (LWP 4390)): #0 0x00007f9c7f897019 in pthread_cond_wait@@GLIBC_2.3.2 () from /lib64/libpthread.so.0 #1 0x0000000000baaa63 in os_cond_wait (cond=0x194eb30, fast_mutex=0x194eaf0) at /usr/src/mysql-5.6.28/storage/innobase/os/os0sync.cc:214 #2 0x0000000000baaadf in os_event_wait_low (event=0x194eaf0, reset_sig_count=4) at /usr/src/mysql-5.6.28/storage/innobase/os/os0sync.cc:592 #3 0x0000000000c64129 in srv_purge_coordinator_suspend (slot=0x194ea20, rseg_history_len=653) at /usr/src/mysql-5.6.28/storage/innobase/srv/srv0srv.cc:2634 #4 0x0000000000c64639 in srv_purge_coordinator_thread (arg=0x0) at /usr/src/mysql-5.6.28/storage/innobase/srv/srv0srv.cc:2753 #5 0x00007f9c7f89283d in start_thread () from /lib64/libpthread.so.0 #6 0x00007f9c7ed3ffdd in clone () from /lib64/libc.so.6 #7 0x0000000000000000 in ?? () Thread 10 (Thread 0x7f9c1b35d940 (LWP 4391)): #0 0x00007f9c7ed39172 in select () from /lib64/libc.so.6 #1 0x0000000000bab334 in os_thread_sleep (tm=1000000) at /usr/src/mysql-5.6.28/storage/innobase/os/os0thread.cc:261 #2 0x0000000000cf9215 in page_cleaner_sleep_if_needed (next_loop_time=1467172254235) at /usr/src/mysql-5.6.28/storage/innobase/buf/buf0flu.cc:2375 #3 0x0000000000cfd758 in buf_flush_page_cleaner_thread (arg=0x0) at /usr/src/mysql-5.6.28/storage/innobase/buf/buf0flu.cc:2416 #4 0x00007f9c7f89283d in start_thread () from /lib64/libpthread.so.0 #5 0x00007f9c7ed3ffdd in clone () from /lib64/libc.so.6 #6 0x0000000000000000 in ?? () Thread 9 (Thread 0x7f9c1790c940 (LWP 4392)): #0 0x00007f9c7f897019 in pthread_cond_wait@@GLIBC_2.3.2 () from /lib64/libpthread.so.0 #1 0x0000000000baaa63 in os_cond_wait (cond=0x194ece0, fast_mutex=0x194eca0) at /usr/src/mysql-5.6.28/storage/innobase/os/os0sync.cc:214 #2 0x0000000000baaadf in os_event_wait_low (event=0x194eca0, reset_sig_count=1) at /usr/src/mysql-5.6.28/storage/innobase/os/os0sync.cc:592 #3 0x0000000000cf6f1d in buf_dump_thread (arg=0x0) at /usr/src/mysql-5.6.28/storage/innobase/buf/buf0dump.cc:594 #4 0x00007f9c7f89283d in start_thread () from /lib64/libpthread.so.0 #5 0x00007f9c7ed3ffdd in clone () from /lib64/libc.so.6 #6 0x0000000000000000 in ?? () Thread 8 (Thread 0x7f9c0ffff940 (LWP 4393)): #0 0x00007f9c7f897280 in pthread_cond_timedwait@@GLIBC_2.3.2 () from /lib64/libpthread.so.0 #1 0x0000000000baa77b in os_cond_wait_timed (cond=0x476b760, fast_mutex=0x476b720, abstime=0x7f9c0ffff0e0) at /usr/src/mysql-5.6.28/storage/innobase/os/os0sync.cc:177 #2 0x0000000000baa9b0 in os_event_wait_time_low (event=0x476b720, time_in_usec=10000000, reset_sig_count=2) at /usr/src/mysql-5.6.28/storage/innobase/os/os0sync.cc:705 #3 0x0000000000d44dd2 in dict_stats_thread (arg=0x0) at /usr/src/mysql-5.6.28/storage/innobase/dict/dict0stats_bg.cc:348 #4 0x00007f9c7f89283d in start_thread () from /lib64/libpthread.so.0 #5 0x00007f9c7ed3ffdd in clone () from /lib64/libc.so.6 #6 0x0000000000000000 in ?? () Thread 7 (Thread 0x7f9c1710b940 (LWP 4394)): #0 0x00007f9c7f897280 in pthread_cond_timedwait@@GLIBC_2.3.2 () from /lib64/libpthread.so.0 #1 0x0000000000baa77b in os_cond_wait_timed (cond=0x47b2460, fast_mutex=0x47b2420, abstime=0x7f9c1710b020) at /usr/src/mysql-5.6.28/storage/innobase/os/os0sync.cc:177 #2 0x0000000000baa9b0 in os_event_wait_time_low (event=0x47b2420, time_in_usec=5000000, reset_sig_count=1) at /usr/src/mysql-5.6.28/storage/innobase/os/os0sync.cc:705 #3 0x0000000000ca6df8 in ib_wqueue_timedwait (wq=0x47a7928, wait_in_usecs=5000000) at /usr/src/mysql-5.6.28/storage/innobase/ut/ut0wqueue.cc:154 #4 0x0000000000d8173e in fts_optimize_thread (arg=0x47a7928) at /usr/src/mysql-5.6.28/storage/innobase/fts/fts0opt.cc:3000 #5 0x00007f9c7f89283d in start_thread () from /lib64/libpthread.so.0 #6 0x00007f9c7ed3ffdd in clone () from /lib64/libc.so.6 #7 0x0000000000000000 in ?? () Thread 6 (Thread 0x7f9c1690a940 (LWP 4395)): #0 0x00007f9c7f89a9c8 in do_sigwait () from /lib64/libpthread.so.0 #1 0x00007f9c7f89aa6d in sigwait () from /lib64/libpthread.so.0 #2 0x0000000000603688 in signal_hand (arg=0x0) at /usr/src/mysql-5.6.28/sql/mysqld.cc:3190 #3 0x0000000000b11172 in pfs_spawn_thread (arg=0x47a1f70) at /usr/src/mysql-5.6.28/storage/perfschema/pfs.cc:1860 #4 0x00007f9c7f89283d in start_thread () from /lib64/libpthread.so.0 #5 0x00007f9c7ed3ffdd in clone () from /lib64/libc.so.6 #6 0x0000000000000000 in ?? () Thread 5 (Thread 0x7f9c168c8940 (LWP 5335)): #0 0x00007f9c7ed370b6 in poll () from /lib64/libc.so.6 #1 0x0000000000e7da67 in vio_io_wait (vio=0x4780e40, event=VIO_IO_EVENT_READ, timeout=28800000) at /usr/src/mysql-5.6.28/vio/viosocket.c:771 #2 0x0000000000e7c350 in vio_socket_io_wait (vio=0x4780e40, event=VIO_IO_EVENT_READ) at /usr/src/mysql-5.6.28/vio/viosocket.c:68 #3 0x0000000000e7c42b in vio_read (vio=0x4780e40, buf=0x4a04400 "001", size=4) at /usr/src/mysql-5.6.28/vio/viosocket.c:123 #4 0x0000000000709e3e in net_read_raw_loop (net=0x48eda88, count=4) at /usr/src/mysql-5.6.28/sql/net_serv.cc:669 #5 0x0000000000709ff7 in net_read_packet_header (net=0x48eda88) at /usr/src/mysql-5.6.28/sql/net_serv.cc:751 #6 0x000000000070ade5 in net_read_packet (net=0x48eda88, complen=0x7f9c168c7f88) at /usr/src/mysql-5.6.28/sql/net_serv.cc:816 #7 0x000000000070af62 in my_net_read (net=0x48eda88) at /usr/src/mysql-5.6.28/sql/net_serv.cc:894 #8 0x00000000007d9a30 in do_command (thd=0x48ed830) at /usr/src/mysql-5.6.28/sql/sql_parse.cc:970 #9 0x000000000078e3fb in do_handle_one_connection (thd_arg=0x48ed830) at /usr/src/mysql-5.6.28/sql/sql_connect.cc:982 #10 0x000000000078e4c1 in handle_one_connection (arg=0x48ed830) at /usr/src/mysql-5.6.28/sql/sql_connect.cc:898 #11 0x0000000000b11172 in pfs_spawn_thread (arg=0x47891a0) at /usr/src/mysql-5.6.28/storage/perfschema/pfs.cc:1860 #12 0x00007f9c7f89283d in start_thread () from /lib64/libpthread.so.0 #13 0x00007f9c7ed3ffdd in clone () from /lib64/libc.so.6 #14 0x0000000000000000 in ?? () Thread 4 (Thread 0x7f9c16887940 (LWP 5367)): #0 0x00007f9c7f897280 in pthread_cond_timedwait@@GLIBC_2.3.2 () from /lib64/libpthread.so.0 #1 0x0000000000abe163 in safe_cond_timedwait (cond=0x4945228, mp=0x49451b8, abstime=0x7f9c16882b20, file=0xf07240 "/usr/src/mysql-5.6.28/include/mysql/psi/mysql_thread.h", line=1199) at /usr/src/mysql-5.6.28/mysys/thr_mutex.c:278 #2 0x000000000070497e in inline_mysql_cond_timedwait (that=0x4945228, mutex=0x49451b8, abstime=0x7f9c16882b20, src_file=0xf070a8 "/usr/src/mysql-5.6.28/sql/mdl.cc", src_line=1327) at /usr/src/mysql-5.6.28/include/mysql/psi/mysql_thread.h:1199 #3 0x0000000000704a42 in MDL_wait::timed_wait (this=0x49451b8, owner=0x49450d0, abs_timeout=0x7f9c16882b20, set_status_on_timeout=false, wait_state_name=0x14f4b30 <MDL_key::m_namespace_to_wait_state_name+48>) at /usr/src/mysql-5.6.28/sql/mdl.cc:1326 #4 0x0000000000706a0d in MDL_context::acquire_lock (this=0x49451b8, mdl_request=0x7f9c16882be0, lock_wait_timeout=31536000) at /usr/src/mysql-5.6.28/sql/mdl.cc:2246 #5 0x0000000000706e99 in MDL_context::upgrade_shared_lock (this=0x49451b8, mdl_ticket=0x49c5c80, new_type=MDL_EXCLUSIVE, lock_wait_timeout=31536000) at /usr/src/mysql-5.6.28/sql/mdl.cc:2428 #6 0x000000000083e9a3 in mysql_inplace_alter_table (thd=0x49450d0, table_list=0x4989018, table=0x49c53a0, altered_table=0x49e2260, ha_alter_info=0x7f9c16883a20, inplace_supported=HA_ALTER_INPLACE_NO_LOCK_AFTER_PREPARE, target_mdl_request=0x7f9c16884380, alter_ctx=0x7f9c16882f70) at /usr/src/mysql-5.6.28/sql/sql_table.cc:6518 #7 0x0000000000840839 in mysql_alter_table (thd=0x49450d0, new_db=0x4989578 "test", new_name=0x0, create_info=0x7f9c16884e20, table_list=0x4989018, alter_info=0x7f9c16884f00, order_num=0, order=0x0, ignore=false) at /usr/src/mysql-5.6.28/sql/sql_table.cc:8454 #8 0x000000000099390d in Sql_cmd_alter_table::execute (this=0x49896f8, thd=0x49450d0) at /usr/src/mysql-5.6.28/sql/sql_alter.cc:313 #9 0x00000000007d5e0d in mysql_execute_command (thd=0x49450d0) at /usr/src/mysql-5.6.28/sql/sql_parse.cc:4976 #10 0x00000000007d67c4 in mysql_parse (thd=0x49450d0, rawbuf=0x4988ef0 "alter table t add column comment varchar(200) default "123"", length=59, parser_state=0x7f9c16886910) at /usr/src/mysql-5.6.28/sql/sql_parse.cc:6386 #11 0x00000000007d83f4 in dispatch_command (command=COM_QUERY, thd=0x49450d0, packet=0x490d681 "alter table t add column comment varchar(200) default "123"", packet_length=59) at /usr/src/mysql-5.6.28/sql/sql_parse.cc:1340 #12 0x00000000007d9c80 in do_command (thd=0x49450d0) at /usr/src/mysql-5.6.28/sql/sql_parse.cc:1037 #13 0x000000000078e3fb in do_handle_one_connection (thd_arg=0x49450d0) at /usr/src/mysql-5.6.28/sql/sql_connect.cc:982 #14 0x000000000078e4c1 in handle_one_connection (arg=0x49450d0) at /usr/src/mysql-5.6.28/sql/sql_connect.cc:898 #15 0x0000000000b11172 in pfs_spawn_thread (arg=0x4792af0) at /usr/src/mysql-5.6.28/storage/perfschema/pfs.cc:1860 #16 0x00007f9c7f89283d in start_thread () from /lib64/libpthread.so.0 #17 0x00007f9c7ed3ffdd in clone () from /lib64/libc.so.6 #18 0x0000000000000000 in ?? () Thread 3 (Thread 0x7f9c16846940 (LWP 5408)): #0 0x00007f9c7f897019 in pthread_cond_wait@@GLIBC_2.3.2 () from /lib64/libpthread.so.0 #1 0x0000000000abdfb0 in safe_cond_wait (cond=0x17ecc60 <COND_thread_cache>, mp=0x17ea080 <LOCK_thread_count>, file=0xee0078 "/usr/src/mysql-5.6.28/include/mysql/psi/mysql_thread.h", line=1162) at /usr/src/mysql-5.6.28/mysys/thr_mutex.c:240 #2 0x0000000000600361 in inline_mysql_cond_wait (that=0x17ecc60 <COND_thread_cache>, mutex=0x17ea080 <LOCK_thread_count>, src_file=0xedef70 "/usr/src/mysql-5.6.28/sql/mysqld.cc", src_line=2687) at /usr/src/mysql-5.6.28/include/mysql/psi/mysql_thread.h:1162 #3 0x000000000060bced in block_until_new_connection () at /usr/src/mysql-5.6.28/sql/mysqld.cc:2687 #4 0x000000000060bfc2 in one_thread_per_connection_end (thd=0x49c02f0, block_pthread=true) at /usr/src/mysql-5.6.28/sql/mysqld.cc:2775 #5 0x000000000078e45e in do_handle_one_connection (thd_arg=0x49c02f0) at /usr/src/mysql-5.6.28/sql/sql_connect.cc:989 #6 0x000000000078e4c1 in handle_one_connection (arg=0x49c02f0) at /usr/src/mysql-5.6.28/sql/sql_connect.cc:898 #7 0x0000000000b11172 in pfs_spawn_thread (arg=0x47ac8d0) at /usr/src/mysql-5.6.28/storage/perfschema/pfs.cc:1860 #8 0x00007f9c7f89283d in start_thread () from /lib64/libpthread.so.0 #9 0x00007f9c7ed3ffdd in clone () from /lib64/libc.so.6 #10 0x0000000000000000 in ?? () Thread 2 (Thread 0x7f9c16805940 (LWP 5684)): #0 0x00007f9c7f897019 in pthread_cond_wait@@GLIBC_2.3.2 () from /lib64/libpthread.so.0 #1 0x0000000000abdfb0 in safe_cond_wait (cond=0x17ecc60 <COND_thread_cache>, mp=0x17ea080 <LOCK_thread_count>, file=0xee0078 "/usr/src/mysql-5.6.28/include/mysql/psi/mysql_thread.h", line=1162) at /usr/src/mysql-5.6.28/mysys/thr_mutex.c:240 #2 0x0000000000600361 in inline_mysql_cond_wait (that=0x17ecc60 <COND_thread_cache>, mutex=0x17ea080 <LOCK_thread_count>, src_file=0xedef70 "/usr/src/mysql-5.6.28/sql/mysqld.cc", src_line=2687) at /usr/src/mysql-5.6.28/include/mysql/psi/mysql_thread.h:1162 #3 0x000000000060bced in block_until_new_connection () at /usr/src/mysql-5.6.28/sql/mysqld.cc:2687 #4 0x000000000060bfc2 in one_thread_per_connection_end (thd=0x4a108b0, block_pthread=true) at /usr/src/mysql-5.6.28/sql/mysqld.cc:2775 #5 0x000000000078e45e in do_handle_one_connection (thd_arg=0x4a108b0) at /usr/src/mysql-5.6.28/sql/sql_connect.cc:989 #6 0x000000000078e4c1 in handle_one_connection (arg=0x4a108b0) at /usr/src/mysql-5.6.28/sql/sql_connect.cc:898 #7 0x0000000000b11172 in pfs_spawn_thread (arg=0x478fe40) at /usr/src/mysql-5.6.28/storage/perfschema/pfs.cc:1860 #8 0x00007f9c7f89283d in start_thread () from /lib64/libpthread.so.0 #9 0x00007f9c7ed3ffdd in clone () from /lib64/libc.so.6 #10 0x0000000000000000 in ?? () Thread 1 (Thread 0x7f9c7fcaa6f0 (LWP 4373)): #0 0x00007f9c7ed370b6 in poll () from /lib64/libc.so.6 #1 0x00000000006047fd in handle_connections_sockets () at /usr/src/mysql-5.6.28/sql/mysqld.cc:6254 #2 0x000000000060b103 in mysqld_main (argc=8, argv=0x1841d80) at /usr/src/mysql-5.6.28/sql/mysqld.cc:5673 #3 0x00000000005fa9c3 in main (argc=4, argv=0x7fffab7fc558) at /usr/src/mysql-5.6.28/sql/main.cc:25
会话2:
函数调用顺序:
Thread 4 (Thread 0x7f9c16887940 (LWP 5367)):
pthread_cond_timedwait
safe_cond_timedwait
inline_mysql_cond_timedwait
MDL_wait::timed_wait
MDL_context::acquire_lock
MDL_context::upgrade_shared_lock
mysql_inplace_alter_table
//INPLACE：索引创建或者删除操作时不需要创建临时表
mysql_alter_table
Sql_cmd_alter_table::execute
mysql_execute_command
mysql_parse
dispatch_command
do_command
do_handle_one_connection
handle_one_connection
pfs_spawn_thread
start_thread ()
clone
mysql> show variables like "old_alter_table"; //INPLACE +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | old_alter_table | OFF | +-----------------+-------+ 1 row in set (0.03 sec) mysql> select @@version; +------------------+ | @@version | +------------------+ | 5.6.28-debug-log | +------------------+ 1 row in set (0.00 sec)
实验2：
当：set global old_alter_table=on 创建临时表，重复实验1操作
会话3:
Thread 2 (Thread 0x7f9c16805940 (LWP 5684)): pthread_cond_timedwait@@GLIBC_2.3.2 () from /lib64/libpthread.so.0 #1 0x0000000000abe163 in safe_cond_timedwait (cond=0x4945228, mp=0x49451b8, abstime=0x7f9c16800bb0, file=0xf07240 "/usr/src/mysql-5.6.28/include/mysql/psi/mysql_thread.h", line=1199) at /usr/src/mysql-5.6.28/mysys/thr_mutex.c:278 #2 0x000000000070497e in inline_mysql_cond_timedwait (that=0x4945228, mutex=0x49451b8, abstime=0x7f9c16800bb0, src_file=0xf070a8 "/usr/src/mysql-5.6.28/sql/mdl.cc", src_line=1327) at /usr/src/mysql-5.6.28/include/mysql/psi/mysql_thread.h:1199 #3 0x0000000000704a42 in MDL_wait::timed_wait (this=0x49451b8, owner=0x49450d0, abs_timeout=0x7f9c16800bb0, set_status_on_timeout=false, wait_state_name=0x14f4b30 <MDL_key::m_namespace_to_wait_state_name+48>) at /usr/src/mysql-5.6.28/sql/mdl.cc:1326 #4 0x0000000000706a0d in MDL_context::acquire_lock (this=0x49451b8, mdl_request=0x7f9c16800c70, lock_wait_timeout=31536000) at /usr/src/mysql-5.6.28/sql/mdl.cc:2246 #5 0x0000000000706e99 in MDL_context::upgrade_shared_lock (this=0x49451b8, mdl_ticket=0x4979de0, new_type=MDL_EXCLUSIVE, lock_wait_timeout=31536000) at /usr/src/mysql-5.6.28/sql/mdl.cc:2428 #6 0x000000000076117e in wait_while_table_is_used (thd=0x49450d0, table=0x49c53a0, function=HA_EXTRA_PREPARE_FOR_RENAME) at /usr/src/mysql-5.6.28/sql/sql_base.cc:2295 #7 0x0000000000841143 in mysql_alter_table (thd=0x49450d0, new_db=0x1932c28 "test", new_name=0x0, create_info=0x7f9c16802e20, table_list=0x19326c8, alter_info=0x7f9c16802f00, order_num=0, order=0x0, ignore=false) at /usr/src/mysql-5.6.28/sql/sql_table.cc:8656 #8 0x000000000099390d in Sql_cmd_alter_table::execute (this=0x1932da8, thd=0x49450d0) at /usr/src/mysql-5.6.28/sql/sql_alter.cc:313 #9 0x00000000007d5e0d in mysql_execute_command (thd=0x49450d0) at /usr/src/mysql-5.6.28/sql/sql_parse.cc:4976 #10 0x00000000007d67c4 in mysql_parse (thd=0x49450d0, rawbuf=0x19325a0 "alter table t add column comment varchar(200) default "123"", length=59, parser_state=0x7f9c16804910) at /usr/src/mysql-5.6.28/sql/sql_parse.cc:6386 #11 0x00000000007d83f4 in dispatch_command (command=COM_QUERY, thd=0x49450d0, packet=0x48d2e21 "alter table t add column comment varchar(200) default "123"", packet_length=59) at /usr/src/mysql-5.6.28/sql/sql_parse.cc:1340 #12 0x00000000007d9c80 in do_command (thd=0x49450d0) at /usr/src/mysql-5.6.28/sql/sql_parse.cc:1037 #13 0x000000000078e3fb in do_handle_one_connection (thd_arg=0x4a108b0) at /usr/src/mysql-5.6.28/sql/sql_connect.cc:982 #14 0x000000000078e4c1 in handle_one_connection (arg=0x4a108b0) at /usr/src/mysql-5.6.28/sql/sql_connect.cc:898 #15 0x0000000000b11172 in pfs_spawn_thread (arg=0x478fe40) at /usr/src/mysql-5.6.28/storage/perfschema/pfs.cc:1860 #16 0x00007f9c7f89283d in start_thread () from /lib64/libpthread.so.0 #17 0x00007f9c7ed3ffdd in clone () from /lib64/libc.so.6 #18 0x0000000000000000 in ?? ()
GDB调试MYSQL
会话0：
mysqld --datadir=/data --basedir=/usr/local/mysql56 --user=mysql --gdb //--gdb ctrl+c可中断到gdb命令行
会话2：
gdb -ex "set target-async 1" -ex "set pagination off" -ex "set non-stop on" attach mysqlpid
(gdb) info threads Id Target Id Frame 22 Thread 0x7f1370ba2940 (LWP 8588) (running) ！！！！！！运行状态 21 Thread 0x7f13703a1940 (LWP 8589) (running) 20 Thread 0x7f136fba0940 (LWP 8590) (running) 19 Thread 0x7f136f39f940 (LWP 8591) (running) 18 Thread 0x7f136eb9e940 (LWP 8592) (running) 17 Thread 0x7f136e39d940 (LWP 8593) (running) 16 Thread 0x7f136db9c940 (LWP 8594) (running) 15 Thread 0x7f136d39b940 (LWP 8595) (running) 14 Thread 0x7f136cb9a940 (LWP 8596) (running) 13 Thread 0x7f136c399940 (LWP 8597) (running) 12 Thread 0x7f136b397940 (LWP 8599) (running) 11 Thread 0x7f136a275940 (LWP 8600) (running) 10 Thread 0x7f1369a74940 (LWP 8601) (running) 9 Thread 0x7f136bb98940 (LWP 8602) (running) 8 Thread 0x7f1369273940 (LWP 8603) (running) 7 Thread 0x7f1368a72940 (LWP 8604) (running) 6 Thread 0x7f1363fff940 (LWP 8605) (running) 5 Thread 0x7f13637fe940 (LWP 8606) (running) 4 Thread 0x7f1362ffd940 (LWP 8607) (running) 3 Thread 0x7f136ab96940 (LWP 8608) (running) 2 Thread 0x7f136ab55940 (LWP 8640) (running) * 1 Thread 0x7f13d03bb6f0 (LWP 8587) 0x00007f13cf4480b6 in poll () from /lib64/libc.so.6 //主线程中断 thread apply 1 c
1.如果想要立即中断当前函数运行并返回，用return，当前函数的剩余语句将 不被执行。（return可带参数，可以此随意设定当前函数的返回值） 2.如果想让程序执行到当前函数返回之后停止，用finish，当前函数的剩余语句将 会正常运行。
会话1：
mysql> alter table t add column aaa varchar(200) default "123";
会话2：
b：
MDL_context::acquire_lock
MDL_context::upgrade_shared_lock
mysql_inplace_alter_table
//INPLACE：索引创建或者删除操作时不需要创建临时表
mysql_alter_table
会话3：
mysql> show processlist; +----+------+-----------+------+---------+------+-------+----------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+------+---------+------+-------+----------------------------------------------------------+ | 1 | root | localhost | test | Query | 14 | init | alter table t add column aaa varchar(200) default "123" | | 4 | root | localhost | test | Query | 0 | init | show processlist | +----+------+-----------+------+---------+------+-------+----------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> show processlist; +----+------+-----------+------+---------+------+----------------------+----------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+------+---------+------+----------------------+----------------------------------------------------------+ | 1 | root | localhost | test | Query | 24 | checking permissions | alter table t add column aaa varchar(200) default "123" | | 4 | root | localhost | test | Query | 0 | init | show processlist | +----+------+-----------+------+---------+------+----------------------+----------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> show processlist; +----+------+-----------+------+---------+------+----------------+----------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+------+---------+------+----------------+----------------------------------------------------------+ | 1 | root | localhost | test | Query | 31 | Opening tables | alter table t add column aaa varchar(200) default "123" | | 4 | root | localhost | test | Query | 0 | init | show processlist | +----+------+-----------+------+---------+------+----------------+----------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> show processlist; +----+------+-----------+------+---------+------+--------------+----------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+------+---------+------+--------------+----------------------------------------------------------+ | 1 | root | localhost | test | Query | 553 | After create | alter table t add column aaa varchar(200) default "123" | | 4 | root | localhost | test | Query | 0 | init | show processlist | +----+------+-----------+------+---------+------+--------------+----------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> show processlist; +----+------+-----------+------+---------+------+----------------+----------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+------+---------+------+----------------+----------------------------------------------------------+ | 1 | root | localhost | test | Query | 614 | altering table | alter table t add column aaa varchar(200) default "123" | | 4 | root | localhost | test | Query | 0 | init | show processlist | +----+------+-----------+------+---------+------+----------------+----------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> show processlist; +----+------+-----------+------+---------+------+------------------------------------------+----------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+------+---------+------+------------------------------------------+----------------------------------------------------------+ | 1 | root | localhost | test | Query | 642 | committing alter table to storage engine | alter table t add column aaa varchar(200) default "123" | | 4 | root | localhost | test | Query | 0 | init | show processlist | +----+------+-----------+------+---------+------+------------------------------------------+----------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> show processlist; +----+------+-----------+------+---------+------+-------+----------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+------+---------+------+-------+----------------------------------------------------------+ | 1 | root | localhost | test | Query | 675 | end | alter table t add column aaa varchar(200) default "123" | | 4 | root | localhost | test | Query | 0 | init | show processlist | +----+------+-----------+------+---------+------+-------+----------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> show processlist; +----+------+-----------+------+---------+------+-----------+----------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+------+---------+------+-----------+----------------------------------------------------------+ | 1 | root | localhost | test | Query | 689 | query end | alter table t add column aaa varchar(200) default "123" | | 4 | root | localhost | test | Query | 0 | init | show processlist | +----+------+-----------+------+---------+------+-----------+----------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> show processlist; +----+------+-----------+------+---------+------+----------------+----------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+------+---------+------+----------------+----------------------------------------------------------+ | 1 | root | localhost | test | Query | 715 | closing tables | alter table t add column aaa varchar(200) default "123" | | 4 | root | localhost | test | Query | 0 | init | show processlist | +----+------+-----------+------+---------+------+----------------+----------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> show processlist; +----+------+-----------+------+---------+------+---------------+----------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+------+---------+------+---------------+----------------------------------------------------------+ | 1 | root | localhost | test | Query | 726 | freeing items | alter table t add column aaa varchar(200) default "123" | | 4 | root | localhost | test | Query | 0 | init | show processlist | +----+------+-----------+------+---------+------+---------------+----------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> show processlist; +----+------+-----------+------+---------+------+-------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+------+---------+------+-------------+------------------+ | 1 | root | localhost | test | Sleep | 733 | cleaning up | NULL | | 4 | root | localhost | test | Query | 0 | init | show processlist | +----+------+-----------+------+---------+------+-------------+------------------+ 2 rows in set (0.00 sec)