电脑知识|欧美黑人一区二区三区|软件|欧美黑人一级爽快片淫片高清|系统|欧美黑人狂野猛交老妇|数据库|服务器|编程开发|网络运营|知识问答|技术教程文章 - 好吧啦网

您的位置:首頁技術文章
文章詳情頁

MySQL鎖等待與死鎖問題分析

瀏覽:5日期:2023-10-04 14:19:09
前言:

在 MySQL 運維過程中,鎖等待和死鎖問題是令各位 DBA 及開發同學非常頭痛的事。出現此類問題會造成業務回滾、卡頓等故障,特別是業務繁忙的系統,出現死鎖問題后影響會更嚴重。本篇文章我們一起來學習下什么是鎖等待及死鎖,出現此類問題又應該如何分析處理呢?

1.了解鎖等待與死鎖

出現鎖等待或死鎖的原因是訪問數據庫需要加鎖,那你可能要問了,為啥要加鎖呢?原因是為了確保并發更新場景下的數據正確性,保證數據庫事務的隔離性。

試想一個場景,如果你要去圖書館借一本《高性能MySQL》,為了防止有人提前把這本書借走,你可以提前進行預約(加鎖),這把鎖可以怎么加?

封鎖圖書館(數據庫級別的鎖) 把數據庫相關的書都鎖住(表級別的鎖) 只鎖 MySQL 相關的書(頁級別的鎖) 只鎖《高性能MySQL》這本書(行級別的鎖)

鎖的粒度越細,并發級別越高,實現也更復雜。

鎖等待也可稱為事務等待,后執行的事務等待前面處理的事務釋放鎖,但是等待時間超過了 MySQL 的鎖等待時間,就會引發這個異常。等待超時后的報錯為“Lock wait timeout exceeded...”。

死鎖發生的原因是兩個事務互相等待對方釋放相同資源的鎖,從而造成的死循環。產生死鎖后會立即報錯“Deadlock found when trying to get lock...”。

2.現象復現及處理

下面我們以 MySQL 5.7.23 版本為例(隔離級別是 RR ),來復現下上述兩種異常現象。

mysql> show create table test_tbG*************************** 1. row *************************** Table: test_tbCreate Table: CREATE TABLE `test_tb` ( `id` int(11) NOT NULL AUTO_INCREMENT, `col1` varchar(50) NOT NULL DEFAULT ’’, `col2` int(11) NOT NULL DEFAULT ’1’, `col3` varchar(20) NOT NULL DEFAULT ’’, PRIMARY KEY (`id`), KEY `idx_col1` (`col1`)) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf81 row in set (0.00 sec)mysql> select * from test_tb;+----+------+------+------+| id | col1 | col2 | col3 |+----+------+------+------+| 1 | fdg | 1 | abc || 2 | a | 2 | fg || 3 | ghrv | 2 | rhdv |+----+------+------+------+3 rows in set (0.00 sec)# 事務一首先執行mysql> begin;Query OK, 0 rows affected (0.00 sec)mysql> select * from test_tb where col1 = ’a’ for update;+----+------+------+------+| id | col1 | col2 | col3 |+----+------+------+------+| 2 | a | 2 | fg |+----+------+------+------+1 row in set (0.00 sec)# 事務二然后執行mysql> begin;Query OK, 0 rows affected (0.01 sec)mysql> update test_tb set col2 = 1 where col1 = ’a’;ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

出現上種異常的原因是事務二在等待事務一的行鎖,但事務一一直沒提交,等待超時而報錯。InnoDB 行鎖等待超時時間由 innodb_lock_wait_timeout 參數控制,此參數默認值為 50 ,單位為秒,即默認情況下,事務二會等待 50s ,若仍拿不到行鎖則會報等待超時異常并回滾此條語句。

對于 5.7 版本,出現鎖等待時,我們可以查看 information_schema 中的幾張系統表來查詢事務狀態。

innodb_trx 當前運行的所有事務。 innodb_locks 當前出現的鎖。 innodb_lock_waits 鎖等待的對應關系

# 鎖等待發生時 查看innodb_trx表可以看到所有事務 # trx_state值為LOCK WAIT 則代表該事務處于等待狀態mysql> select * from information_schema.innodb_trxG*************************** 1. row *************************** trx_id: 38511 trx_state: LOCK WAIT trx_started: 2021-03-24 17:20:43 trx_requested_lock_id: 38511:156:4:2 trx_wait_started: 2021-03-24 17:20:43trx_weight: 2 trx_mysql_thread_id: 1668447 trx_query: update test_tb set col2 = 1 where col1 = ’a’ trx_operation_state: starting index read trx_tables_in_use: 1 trx_tables_locked: 1 trx_lock_structs: 2 trx_lock_memory_bytes: 1136 trx_rows_locked: 1 trx_rows_modified: 0 trx_concurrency_tickets: 0 trx_isolation_level: REPEATABLE READ trx_unique_checks: 1 trx_foreign_key_checks: 1trx_last_foreign_key_error: NULL trx_adaptive_hash_latched: 0 trx_adaptive_hash_timeout: 0 trx_is_read_only: 0trx_autocommit_non_locking: 0*************************** 2. row *************************** trx_id: 38510 trx_state: RUNNING trx_started: 2021-03-24 17:18:54 trx_requested_lock_id: NULL trx_wait_started: NULLtrx_weight: 4 trx_mysql_thread_id: 1667530 trx_query: NULL trx_operation_state: NULL trx_tables_in_use: 0 trx_tables_locked: 1 trx_lock_structs: 4 trx_lock_memory_bytes: 1136 trx_rows_locked: 3 trx_rows_modified: 0 trx_concurrency_tickets: 0 trx_isolation_level: REPEATABLE READ trx_unique_checks: 1 trx_foreign_key_checks: 1trx_last_foreign_key_error: NULL trx_adaptive_hash_latched: 0 trx_adaptive_hash_timeout: 0 trx_is_read_only: 0trx_autocommit_non_locking: 02 rows in set (0.00 sec)# innodb_trx 字段值含義trx_id:事務ID。trx_state:事務狀態,有以下幾種狀態:RUNNING、LOCK WAIT、ROLLING BACK 和 COMMITTING。trx_started:事務開始時間。trx_requested_lock_id:事務當前正在等待鎖的標識,可以和 INNODB_LOCKS 表 JOIN 以得到更多詳細信息。trx_wait_started:事務開始等待的時間。trx_weight:事務的權重。trx_mysql_thread_id:事務線程 ID,可以和 PROCESSLIST 表 JOIN。trx_query:事務正在執行的 SQL 語句。trx_operation_state:事務當前操作狀態。trx_tables_in_use:當前事務執行的 SQL 中使用的表的個數。trx_tables_locked:當前執行 SQL 的行鎖數量。trx_lock_structs:事務保留的鎖數量。trx_isolation_level:當前事務的隔離級別。# sys.innodb_lock_waits 視圖也可看到事務等待狀況,且給出了殺鏈接的SQLmysql> select * from sys.innodb_lock_waitsG*************************** 1. row ***************************wait_started: 2021-03-24 17:20:43 wait_age: 00:00:22 wait_age_secs: 22locked_table: `testdb`.`test_tb`locked_index: idx_col1 locked_type: RECORD waiting_trx_id: 38511 waiting_trx_started: 2021-03-24 17:20:43 waiting_trx_age: 00:00:22 waiting_trx_rows_locked: 1 waiting_trx_rows_modified: 0 waiting_pid: 1668447 waiting_query: update test_tb set col2 = 1 where col1 = ’a’ waiting_lock_id: 38511:156:4:2 waiting_lock_mode: X blocking_trx_id: 38510blocking_pid: 1667530 blocking_query: NULL blocking_lock_id: 38510:156:4:2 blocking_lock_mode: Xblocking_trx_started: 2021-03-24 17:18:54 blocking_trx_age: 00:02:11 blocking_trx_rows_locked: 3 blocking_trx_rows_modified: 0 sql_kill_blocking_query: KILL QUERY 1667530sql_kill_blocking_connection: KILL 1667530

sys.innodb_lock_waits 視圖整合了事務等待狀況,同時給出殺掉堵塞源端的 kill 語句。不過是否要殺掉鏈接還是需要綜合考慮的。

死鎖與鎖等待稍有不同,我們同樣也來簡單復現下死鎖現象。

# 開啟兩個事務# 事務一執行mysql> update test_tb set col2 = 1 where col1 = ’a’;Query OK, 1 row affected (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 0# 事務二執行mysql> update test_tb set col2 = 1 where id = 3;Query OK, 1 row affected (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 0# 回到事務一執行 回車后 此條語句處于鎖等待狀態mysql> update test_tb set col1 = ’abcd’ where id = 3;Query OK, 1 row affected (5.71 sec)Rows matched: 1 Changed: 1 Warnings: 0# 回到事務二再執行 此時二者相互等待發生死鎖mysql> update test_tb set col3 = ’gddx’ where col1 = ’a’;ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

發生死鎖后會選擇一個事務進行回滾,想查明死鎖原因,可以執行 show engine innodb status 來查看死鎖日志,根據死鎖日志,結合業務邏輯來進一步定位死鎖原因。

在實際應用中,我們要盡量避免死鎖現象的發生,可以從以下幾個方面入手:

事務盡可能小,不要將復雜邏輯放進一個事務里。 涉及多行記錄時,約定不同事務以相同順序訪問。 業務中要及時提交或者回滾事務,可減少死鎖產生的概率。 表要有合適的索引。 可嘗試將隔離級別改為 RC 。總結:

本篇文章簡單介紹了鎖等待及死鎖發生的原因,其實真實業務中發生死鎖還是很難分析的,需要一定的經驗積累。本篇文章只是面向初學者,希望各位對死鎖能夠有個初印象。

以上就是MySQL鎖等待與死鎖問題分析的詳細內容,更多關于MySQL鎖等待與死鎖的資料請關注好吧啦網其它相關文章!

標簽: MySQL 數據庫
相關文章:
主站蜘蛛池模板: 匀胶机旋涂仪-声扫显微镜-工业水浸超声-安赛斯(北京)科技有限公司 | 税筹星_灵活用工平台_企业财务顾问_财税法薪综合服务平台 | 钛合金标准件-钛合金螺丝-钛管件-钛合金棒-钛合金板-钛合金锻件-宝鸡远航钛业有限公司 | 砂磨机_立式纳米砂磨机_实验室砂磨机-广州儒佳化工设备厂家 | [官网]叛逆孩子管教_戒网瘾学校_全封闭问题青少年素质教育_新起点青少年特训学校 | 贴片电感_贴片功率电感_贴片绕线电感_深圳市百斯特电子有限公司 贴片电容代理-三星电容-村田电容-风华电容-国巨电容-深圳市昂洋科技有限公司 | 网站建设-高端品牌网站设计制作一站式定制_杭州APP/微信小程序开发运营-鼎易科技 | 灌木树苗-绿化苗木-常绿乔木-价格/批发/基地 - 四川成都途美园林 | 轻型地埋电缆故障测试仪,频响法绕组变形测试仪,静荷式卧式拉力试验机-扬州苏电 | 玉米深加工设备-玉米深加工机械-新型玉米工机械生产厂家-河南粮院机械制造有限公司 | 喷砂机厂家_自动除锈抛丸机价格-成都泰盛吉自动化喷砂设备 | 紫外线老化试验箱_uv紫外线老化试验箱价格|型号|厂家-正航仪器设备 | 家用净水器代理批发加盟_净水机招商代理_全屋净水器定制品牌_【劳伦斯官网】 | STRO|DTRO-STRO反渗透膜(科普)_碟滤 | 上海心叶港澳台联考一对一培训_上海心叶港澳台联考,港澳台联考一对一升学指导 | 通风气楼_通风天窗_屋顶风机-山东美创通风设备有限公司 | 杭州中央空调维修_冷却塔/新风机柜/热水器/锅炉除垢清洗_除垢剂_风机盘管_冷凝器清洗-杭州亿诺能源有限公司 | 上海深蓝_缠绕机_缠膜机-上海深蓝机械装备有限公司 | 无缝方管|无缝矩形管|无缝方矩管|无锡方管厂家 | 石栏杆_青石栏杆_汉白玉栏杆_花岗岩栏杆 - 【石雕之乡】点石石雕石材厂 | 沈阳真空机_沈阳真空包装机_沈阳大米真空包装机-沈阳海鹞真空包装机械有限公司 | 骨密度检测仪_骨密度分析仪_骨密度仪_动脉硬化检测仪专业生产厂家【品源医疗】 | 长沙印刷厂-包装印刷-画册印刷厂家-湖南省日大彩色印务有限公司 青州搬家公司电话_青州搬家公司哪家好「鸿喜」青州搬家 | 执业药师报名条件,考试时间,考试真题,报名入口—首页 | 活性氧化铝|无烟煤滤料|活性氧化铝厂家|锰砂滤料厂家-河南新泰净水材料有限公司 | 鹤壁创新仪器公司-全自动量热仪,定硫仪,煤炭测硫仪,灰熔点测定仪,快速自动测氢仪,工业分析仪,煤质化验仪器 | 多米诺-多米诺世界纪录团队-多米诺世界-多米诺团队培训-多米诺公关活动-多米诺创意广告-多米诺大型表演-多米诺专业赛事 | 快速卷帘门_硬质快速卷帘门-西朗门业 | 软文世界-软文推广-软文营销-新闻稿发布-一站式软文自助发稿平台 | 无线遥控更衣吊篮_IC卡更衣吊篮_电动更衣吊篮配件_煤矿更衣吊篮-力得电子 | 深圳工程师职称评定条件及流程_深圳职称评审_职称评审-职称网 | 镀锌角钢_槽钢_扁钢_圆钢_方矩管厂家_镀锌花纹板-海邦钢铁(天津)有限公司 | EDLC超级法拉电容器_LIC锂离子超级电容_超级电容模组_软包单体电容电池_轴向薄膜电力电容器_深圳佳名兴电容有限公司_JMX专注中高端品牌电容生产厂家 | 棉柔巾代加工_洗脸巾oem_一次性毛巾_浴巾生产厂家-杭州禾壹卫品科技有限公司 | 金现代信息产业股份有限公司--数字化解决方案供应商 | 防火门-专业生产甲级不锈钢钢质防火门厂家资质齐全-广东恒磊安防设备有限公司 | 高硼硅玻璃|水位计玻璃板|光学三棱镜-邯郸奥维玻璃科技有限公司 高温高压釜(氢化反应釜)百科 | 河南正规膏药生产厂家-膏药贴牌-膏药代加工-修康药业集团官网 | 煤机配件厂家_刮板机配件_链轮轴组_河南双志机械设备有限公司 | 顶空进样器-吹扫捕集仪-热脱附仪-二次热解吸仪-北京华盛谱信仪器 | 福兰德PVC地板|PVC塑胶地板|PVC运动地板|PVC商用地板-中国弹性地板系统专业解决方案领先供应商! 福建成考网-福建成人高考网 |