怎么使用MySQL中的character_set_server影响参数
这篇文章主要讲解了“怎么使用MySQL中的character_set_server影响参数”,文中的讲解内容简单清晰,易于学习与理解,下面请大家跟着小编的思路慢慢深入,一起来研究和学习“怎么使用MySQL中的character_set_server影响参数”吧!
Waiting for global read lock:由于flush table with read lock调用函数lock_global_read_lock导致DML操作堵塞。
Waiting for commit lock :由于flush table with read lock 调用函数make_global_read_lock_block_commit导致事务不能提交
现象堵塞COMMIT和DML
mysql>showprocesslist;+----+-----------------+-----------+---------+---------+------+------------------------------+----------------------------+-----------+---------------+|Id|User|Host|db|Command|Time|State|Info|Rows_sent|Rows_examined|+----+-----------------+-----------+---------+---------+------+------------------------------+----------------------------+-----------+---------------+|1|event_scheduler|localhost|NULL|Daemon|268|Waitingonemptyqueue|NULL|0|0||5|root|localhost|NULL|Query|0|starting|showprocesslist|0|0||6|root|localhost|testmts|Query|61|Waitingforcommitlock|commit|0|0||7|root|localhost|testmts|Query|6|Waitingforglobalreadlock|deletefromtestcplimit1|0|0|+----+-----------------+-----------+---------+---------+------+------------------------------+----------------------------+-----------+---------------+相关栈帧Waiting for commit lock
#00x00007ffff7bd3a5einpthread_cond_timedwait@@GLIBC_2.3.2()from/lib64/libpthread.so.0#10x000000000192027binnative_cond_timedwait(cond=0x7ffee0000d08,mutex=0x7ffee0000cc0,abstime=0x7fffec5ec950)at/mysqldata/percona-server-locks-detail-5.7.22/include/thr_cond.h:129#20x00000000019205eainsafe_cond_timedwait(cond=0x7ffee0000d08,mp=0x7ffee0000c98,abstime=0x7fffec5ec950,file=0x204cdd0"/mysqldata/percona-server-locks-detail-5.7.22/sql/mdl.cc",line=1899)at/mysqldata/percona-server-locks-detail-5.7.22/mysys/thr_cond.c:88#30x00000000014b9f21inmy_cond_timedwait(cond=0x7ffee0000d08,mp=0x7ffee0000c98,abstime=0x7fffec5ec950,file=0x204cdd0"/mysqldata/percona-server-locks-detail-5.7.22/sql/mdl.cc",line=1899)at/mysqldata/percona-server-locks-detail-5.7.22/include/thr_cond.h:180#40x00000000014ba484ininline_mysql_cond_timedwait(that=0x7ffee0000d08,mutex=0x7ffee0000c98,abstime=0x7fffec5ec950,src_file=0x204cdd0"/mysqldata/percona-server-locks-detail-5.7.22/sql/mdl.cc",src_line=1899)at/mysqldata/percona-server-locks-detail-5.7.22/include/mysql/psi/mysql_thread.h:1229#50x00000000014bb702inMDL_wait::timed_wait(this=0x7ffee0000c98,owner=0x7ffee0000c00,abs_timeout=0x7fffec5ec950,set_status_on_timeout=true,wait_state_name=0x2d93240)at/mysqldata/percona-server-locks-detail-5.7.22/sql/mdl.cc:1899#60x00000000014bd7acinMDL_context::acquire_lock(this=0x7ffee0000c98,mdl_request=0x7fffec5ec9c0,lock_wait_timeout=31536000)at/mysqldata/percona-server-locks-detail-5.7.22/sql/mdl.cc:3746#70x0000000000f7ffe4inha_commit_trans(thd=0x7ffee0000c00,all=true,ignore_global_read_lock=false)at/mysqldata/percona-server-locks-detail-5.7.22/sql/handler.cc:1795#80x00000000016dcd2fintrans_commit(thd=0x7ffee0000c00)at/mysqldata/percona-server-locks-detail-5.7.22/sql/transaction.cc:239#90x00000000015cf466inmysql_execute_command(thd=0x7ffee0000c00,first_level=true)at/mysqldata/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:4526#100x00000000015d2fdeinmysql_parse(thd=0x7ffee0000c00,parser_state=0x7fffec5ee600)at/mysqldata/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:5901#110x00000000015c6b72indispatch_command(thd=0x7ffee0000c00,com_data=0x7fffec5eed70,command=COM_QUERY)at/mysqldata/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:1490#120x00000000015c58ffindo_command(thd=0x7ffee0000c00)at/mysqldata/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:1021#130x000000000170e578inhandle_connection(arg=0x67bf490)at/mysqldata/percona-server-locks-detail-5.7.22/sql/conn_handler/connection_handler_per_thread.cc:312#140x0000000001945538inpfs_spawn_thread(arg=0x66ff4f0)at/mysqldata/percona-server-locks-detail-5.7.22/storage/perfschema/pfs.cc:2190#150x00007ffff7bcfaa1instart_thread()from/lib64/libpthread.so.0#160x00007ffff6b37c4dinclone()from/lib64/libc.so.6Waiting for global read lock
#00x00007ffff7bd3a5einpthread_cond_timedwait@@GLIBC_2.3.2()from/lib64/libpthread.so.0#10x000000000192027binnative_cond_timedwait(cond=0x7ffee4000c98,mutex=0x7ffee4000c50,abstime=0x7fffec58a0e0)at/mysqldata/percona-server-locks-detail-5.7.22/include/thr_cond.h:129#20x00000000019205eainsafe_cond_timedwait(cond=0x7ffee4000c98,mp=0x7ffee4000c28,abstime=0x7fffec58a0e0,file=0x204cdd0"/mysqldata/percona-server-locks-detail-5.7.22/sql/mdl.cc",line=1899)at/mysqldata/percona-server-locks-detail-5.7.22/mysys/thr_cond.c:88#30x00000000014b9f21inmy_cond_timedwait(cond=0x7ffee4000c98,mp=0x7ffee4000c28,abstime=0x7fffec58a0e0,file=0x204cdd0"/mysqldata/percona-server-locks-detail-5.7.22/sql/mdl.cc",line=1899)at/mysqldata/percona-server-locks-detail-5.7.22/include/thr_cond.h:180#40x00000000014ba484ininline_mysql_cond_timedwait(that=0x7ffee4000c98,mutex=0x7ffee4000c28,abstime=0x7fffec58a0e0,src_file=0x204cdd0"/mysqldata/percona-server-locks-detail-5.7.22/sql/mdl.cc",src_line=1899)at/mysqldata/percona-server-locks-detail-5.7.22/include/mysql/psi/mysql_thread.h:1229#50x00000000014bb702inMDL_wait::timed_wait(this=0x7ffee4000c28,owner=0x7ffee4000b90,abs_timeout=0x7fffec58a0e0,set_status_on_timeout=true,wait_state_name=0x2d93180)at/mysqldata/percona-server-locks-detail-5.7.22/sql/mdl.cc:1899#60x00000000014bd7acinMDL_context::acquire_lock(this=0x7ffee4000c28,mdl_request=0x7fffec58a160,lock_wait_timeout=31536000)at/mysqldata/percona-server-locks-detail-5.7.22/sql/mdl.cc:3746#70x000000000153bed8inopen_table(thd=0x7ffee4000b90,table_list=0x7ffee40066e0,ot_ctx=0x7fffec58a5a0)at/mysqldata/percona-server-locks-detail-5.7.22/sql/sql_base.cc:3233#80x000000000153f904inopen_and_process_table(thd=0x7ffee4000b90,lex=0x7ffee40031c0,tables=0x7ffee40066e0,counter=0x7ffee4003280,flags=0,prelocking_strategy=0x7fffec58a6d0,has_prelocking_list=false,ot_ctx=0x7fffec58a5a0)at/mysqldata/percona-server-locks-detail-5.7.22/sql/sql_base.cc:5213#90x0000000001540a58inopen_tables(thd=0x7ffee4000b90,start=0x7fffec58a690,counter=0x7ffee4003280,flags=0,prelocking_strategy=0x7fffec58a6d0)at/mysqldata/percona-server-locks-detail-5.7.22/sql/sql_base.cc:5831#100x0000000001541e93inopen_tables_for_query(thd=0x7ffee4000b90,tables=0x7ffee40066e0,flags=0)at/mysqldata/percona-server-locks-detail-5.7.22/sql/sql_base.cc:6606#110x00000000017f2b53inSql_cmd_delete::mysql_delete(this=0x7ffee4006c80,thd=0x7ffee4000b90,limit=1)at/mysqldata/percona-server-locks-detail-5.7.22/sql/sql_delete.cc:76#120x00000000017f7098inSql_cmd_delete::execute(this=0x7ffee4006c80,thd=0x7ffee4000b90)at/mysqldata/percona-server-locks-detail-5.7.22/sql/sql_delete.cc:1386#130x00000000015cc801inmysql_execute_command(thd=0x7ffee4000b90,first_level=true)at/mysqldata/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:3756#140x00000000015d2fdeinmysql_parse(thd=0x7ffee4000b90,parser_state=0x7fffec58c600)at/mysqldata/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:5901#150x00000000015c6b72indispatch_command(thd=0x7ffee4000b90,com_data=0x7fffec58cd70,command=COM_QUERY)at/mysqldata/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:1490#160x00000000015c58ffindo_command(thd=0x7ffee4000b90)at/mysqldata/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:1021#170x000000000170e578inhandle_connection(arg=0x67182c0)at/mysqldata/percona-server-locks-detail-5.7.22/sql/conn_handler/connection_handler_per_thread.cc:312#180x0000000001945538inpfs_spawn_thread(arg=0x671aca0)at/mysqldata/percona-server-locks-detail-5.7.22/storage/perfschema/pfs.cc:2190#190x00007ffff7bcfaa1instart_thread()from/lib64/libpthread.so.0#200x00007ffff6b37c4dinclone()from/lib64/libc.so.6
感谢各位的阅读,以上就是“怎么使用MySQL中的character_set_server影响参数”的内容了,经过本文的学习后,相信大家对怎么使用MySQL中的character_set_server影响参数这一问题有了更深刻的体会,具体使用情况还需要大家实践验证。这里是亿速云,小编将为大家推送更多相关知识点的文章,欢迎关注!
声明:本站所有文章资源内容,如无特殊说明或标注,均为采集网络资源。如若本站内容侵犯了原著者的合法权益,可联系本站删除。