mysql wait for_MySQL：简单记录一下Waiting for commit lock

最新推荐文章于 2022-04-12 15:39:03 发布
最新推荐文章于 2022-04-12 15:39:03 发布
阅读量1.7k 收藏
点赞数
文章标签： mysql wait for
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_33085941/article/details/113134550
版权

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> show processlist;

+----+-----------------+-----------+---------+---------+------+------------------------------+----------------------------+-----------+---------------+

| Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined |

+----+-----------------+-----------+---------+---------+------+------------------------------+----------------------------+-----------+---------------+

| 1 | event_scheduler | localhost | NULL | Daemon | 268 | Waiting on empty queue | NULL | 0 | 0 |

| 5 | root | localhost | NULL | Query | 0 | starting | show processlist | 0 | 0 |

| 6 | root | localhost | testmts | Query | 61 | Waiting for commit lock | commit | 0 | 0 |

| 7 | root | localhost | testmts | Query | 6 | Waiting for global read lock | delete from testcp limit 1 | 0 | 0 |

+----+-----------------+-----------+---------+---------+------+------------------------------+----------------------------+-----------+---------------+

相关栈帧

Waiting for commit lock

#0 0x00007ffff7bd3a5e in pthread_cond_timedwait@@GLIBC_2.3.2 () from /lib64/libpthread.so.0

#1 0x000000000192027b in native_cond_timedwait (cond=0x7ffee0000d08, mutex=0x7ffee0000cc0, abstime=0x7fffec5ec950)

at /mysqldata/percona-server-locks-detail-5.7.22/include/thr_cond.h:129

#2 0x00000000019205ea in safe_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

#3 0x00000000014b9f21 in my_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

#4 0x00000000014ba484 in inline_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

#5 0x00000000014bb702 in MDL_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

#6 0x00000000014bd7ac in MDL_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

#7 0x0000000000f7ffe4 in ha_commit_trans (thd=0x7ffee0000c00, all=true, ignore_global_read_lock=false)

at /mysqldata/percona-server-locks-detail-5.7.22/sql/handler.cc:1795

#8 0x00000000016dcd2f in trans_commit (thd=0x7ffee0000c00) at /mysqldata/percona-server-locks-detail-5.7.22/sql/transaction.cc:239

#9 0x00000000015cf466 in mysql_execute_command (thd=0x7ffee0000c00, first_level=true) at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:4526

#10 0x00000000015d2fde in mysql_parse (thd=0x7ffee0000c00, parser_state=0x7fffec5ee600) at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:5901

#11 0x00000000015c6b72 in dispatch_command (thd=0x7ffee0000c00, com_data=0x7fffec5eed70, command=COM_QUERY)

at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:1490

#12 0x00000000015c58ff in do_command (thd=0x7ffee0000c00) at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:1021

#13 0x000000000170e578 in handle_connection (arg=0x67bf490) at /mysqldata/percona-server-locks-detail-5.7.22/sql/conn_handler/connection_handler_per_thread.cc:312

#14 0x0000000001945538 in pfs_spawn_thread (arg=0x66ff4f0) at /mysqldata/percona-server-locks-detail-5.7.22/storage/perfschema/pfs.cc:2190

#15 0x00007ffff7bcfaa1 in start_thread () from /lib64/libpthread.so.0

#16 0x00007ffff6b37c4d in clone () from /lib64/libc.so.6

Waiting for global read lock

#0 0x00007ffff7bd3a5e in pthread_cond_timedwait@@GLIBC_2.3.2 () from /lib64/libpthread.so.0

#1 0x000000000192027b in native_cond_timedwait (cond=0x7ffee4000c98, mutex=0x7ffee4000c50, abstime=0x7fffec58a0e0)

at /mysqldata/percona-server-locks-detail-5.7.22/include/thr_cond.h:129

#2 0x00000000019205ea in safe_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

#3 0x00000000014b9f21 in my_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

#4 0x00000000014ba484 in inline_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

#5 0x00000000014bb702 in MDL_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

#6 0x00000000014bd7ac in MDL_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

#7 0x000000000153bed8 in open_table (thd=0x7ffee4000b90, table_list=0x7ffee40066e0, ot_ctx=0x7fffec58a5a0)

at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_base.cc:3233

#8 0x000000000153f904 in open_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

#9 0x0000000001540a58 in open_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

#10 0x0000000001541e93 in open_tables_for_query (thd=0x7ffee4000b90, tables=0x7ffee40066e0, flags=0)

at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_base.cc:6606

#11 0x00000000017f2b53 in Sql_cmd_delete::mysql_delete (this=0x7ffee4006c80, thd=0x7ffee4000b90, limit=1)

at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_delete.cc:76

#12 0x00000000017f7098 in Sql_cmd_delete::execute (this=0x7ffee4006c80, thd=0x7ffee4000b90) at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_delete.cc:1386

#13 0x00000000015cc801 in mysql_execute_command (thd=0x7ffee4000b90, first_level=true) at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:3756

#14 0x00000000015d2fde in mysql_parse (thd=0x7ffee4000b90, parser_state=0x7fffec58c600) at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:5901

#15 0x00000000015c6b72 in dispatch_command (thd=0x7ffee4000b90, com_data=0x7fffec58cd70, command=COM_QUERY)

at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:1490

#16 0x00000000015c58ff in do_command (thd=0x7ffee4000b90) at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:1021

#17 0x000000000170e578 in handle_connection (arg=0x67182c0) at /mysqldata/percona-server-locks-detail-5.7.22/sql/conn_handler/connection_handler_per_thread.cc:312

#18 0x0000000001945538 in pfs_spawn_thread (arg=0x671aca0) at /mysqldata/percona-server-locks-detail-5.7.22/storage/perfschema/pfs.cc:2190

#19 0x00007ffff7bcfaa1 in start_thread () from /lib64/libpthread.so.0

#20 0x00007ffff6b37c4d in clone () from /lib64/libc.so.6

周福来
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
wait-for-mysql:等待一个mysql数据库出现
06-16
等待-mysql 等待一个mysql数据库出现 趁你还能跑 只需链接您的 mysql 容器，此容器一旦启动并准备好就会退出！ docker run --rm --link mum:mysql digit/wait-for-mysql
MySQL 主从同步延迟排查Slave_SQL_Running_State: Waiting for dependent transaction to commit
eagle89的专栏
12-12 703
Slave_SQL_Running_State: Waiting for dependent transaction to commit
MySQL备份问题排查和思考
uuqaz的博客
04-12 823
1. 背景 2. 疑点 3. 问题分析 4. 问题定位 5. checking permissions的疑惑 6. 探索优化思路 7. 补充：关于几个timeout参数生效点 1. 背景 行内数据库备份在使用某备份软件，使用的数据库版本MySQL 8.0社区版，全备使用mysqldump进行，DBA早上巡检发现有一套数据库全备份失败，心里一疙瘩怎么回事呢？来看看如下报错 [mysqldump: Error: 'Lost connection to MySQL server during q
mysql hang住 原因_MySQL5.6.12 Waiting for commit lock导致从库hang住的问题剖析
weixin_36155936的博客
02-07 512
Nagios报警，线上一台从库检测不到slave状态，于是远程上去查看问题：1，show slave status\G卡住：Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.MySQL> show slave status\Gshow slave status卡住了，动弹不了，这种情况还...
Jumpserver堡垒机的部署（docker）
nanhavezhi的博客
12-22 2032
启动mysql docker run -d --name mysql --restart=always -e MYSQL_ROOT_PASSWORD=nanhavezhi123 -p 3306:3306 -v /data/mysql/data:/var/lib/mysql mysql:5.7 --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci 创建jumpserver的数据库 注意：密码不要设置简单密码，否则会导致ju
waiting for table metadata lock
weixin_42070186的博客
12-11 115
MySQL在进行alter table等DDL操作时，有时会出现Waiting for table metadata lock的等待场景。而且，一旦alter table TableA的操作停滞在Waiting for table metadata lock的状态，后续对TableA的任何操作（包括读）都无法进行，因为他们也会在Opening tables的阶段进入到Waiting for table metadata lock的锁等待队列。如果是产品环境的核心表出现了这样的锁等待队列，就会造成灾难性的后果
commit-emoji：使用随机表情符号消息执行git commit。 :face_with_tears_of_joy::face_with_tears_of_joy::face_with_tears_of_joy:
02-05
使用随机表情符号消息执行git commit。 :face_with_tears_of_joy: :face_with_tears_of_joy: :face_with_tears_of_joy: 安装 npm install -g commit-emoji 用法 只需从命令行运行commit-emoji 。 这将在子shell中...
latest_commit_msg:将提交消息记录到此提交中
04-19
Latest_commit_msg 将提交消息记录到此提交中
placepic_server::laptop:Cutty-Kitty-Server :woman_technologist_light_skin_tone::pig_face:
04-29
:pig_face: placepic：...:sparkles: :desktop_computer: 代码约定git分支master | |--- kdk |--- junyup |--- cyhgit commit消息规则[Feat] 기능 추가[Fix] 버그 수정[Refactor] 리팩토링[Chore] 간단한 수정[Delete
two_procedure_commit:两个程序commit的简单java实现
06-14
two_procedure_commit 两个程序commit的简单java实现
save-walter-white:为Save Walter White创建了Breaking Bad系列新网站。 :man_scientist_light_skin_tone::test_tube::woman_scientist_light_skin_tone::rolling_on_the_floor_laughing::woman_technologist_light_skin_tone:
05-28
提交更改： bash $ git commit -m 'feat: Minha nova feature' ； 推送到您的分支： bash $ git push origin minha-feature 。 完成合并请求请求后，可以删除分支。 完成 :heart_suit: 通过 :woman::laptop:
Waiting for Commit Lock
wxc20062006的专栏
05-20 1944
问题现在：在使用xtrabackup进行备份时，备份被阻塞，并助数据库中大量show slave status进程。 １.mysql版本信息： mysql> select @@version; +------------+ | @@version  | +------------+ | 5.6.19-log | +------------+ Xtrabackup
MySQL5.6 Waiting for Commit Lock
欧辰的专栏
08-04 695
mysql Bug#19843808 自动修复记录 MySQL5.6和Xtrabackup之间存在一个bug，这个bug在5.6.23中已经修复。 Xtrabackup备份的时候执行flushs tables with read lock和show slave status会有可能和SQL Thread形成死锁，导致SQL Thread一直被卡主，STOP也没有用，Kill我们测试会丢失数据，只有Restart Server才行。 原因是SQL Thread的DML操作完成之后，持有rl...
MySQL 大事务导致其他dml阻塞
渔夫数据库笔记
12-17 3355
1.版本信息 MySQL 8.0.13 2.问题描述 通过阿里云的 sql 洞察监控到某些时间点，数据库的部分dml操作耗时异常，截图如下: #从截图中我们看到在差不多的时间点，有的dml语句执行时间异常，有些又正常。 3.问题分析 #先说结论：大事务在刷binlog cache时候阻塞了其他事务刷binlog cache的动作 3.1 根据问题时间点查看活跃事务监控信息 发现一个可以的 update 操作，这个update 是活跃连接中执行时间最长的一个。 当时存在的一些
mysql 延迟等待函数_sqlserver waitfor time 延迟函数的用法
weixin_34310322的博客
02-05 1894
SQL有定时执行的语句 WaitFor,可以写到一个存储过程中再执行一次 语法:WaitFor{Delay 'time'|Time 'time} Delay后面的时间为延迟多少时间执行 Time后面的时间为指定何时执行,式为HH:MM:SS,不支持日期 例: ------指定10:00执行 Begin waitfor time '10:00' seSQL有定时执行的语句 WaitFor,可以写到一...
mysql waiting for_剖析MySQL5.6.12 Waiting for commit lock导致从卡死
weixin_34062155的博客
01-18 852
nagios报警，线上一台从库检测不到slave状态，于是远程上去查看问题：1，show slave status\G卡住：Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> show slave status\Gshow slave status卡住了，动弹不了，这种情况还...
android手机应用源码Imsdroid语音视频通话源码.rar
最新发布
05-20
android手机应用源码Imsdroid语音视频通话源码.rar
关于mysql参数 lock_wait_timeout 和 innodb_lock_wait_timeout 的案例
06-02
同时，如果 `lock_wait_timeout` 参数的值大于 `innodb_lock_wait_timeout` 参数的值，那么 `innodb_lock_wait_timeout` 参数的值会被覆盖，即事务 B 最长只会等待 `innodb_lock_wait_timeout` 参数设定的时间。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值