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

您的位置:首頁技術(shù)文章
文章詳情頁

MySQL鎖等待與死鎖問題分析

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

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

1.了解鎖等待與死鎖

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

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

封鎖圖書館(數(shù)據(jù)庫級別的鎖) 把數(shù)據(jù)庫相關(guān)的書都鎖?。ū砑墑e的鎖) 只鎖 MySQL 相關(guān)的書(頁級別的鎖) 只鎖《高性能MySQL》這本書(行級別的鎖)

鎖的粒度越細(xì),并發(fā)級別越高,實(shí)現(xiàn)也更復(fù)雜。

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

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

2.現(xiàn)象復(fù)現(xiàn)及處理

下面我們以 MySQL 5.7.23 版本為例(隔離級別是 RR ),來復(fù)現(xiàn)下上述兩種異?,F(xiàn)象。

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)# 事務(wù)一首先執(zhí)行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)# 事務(wù)二然后執(zhí)行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

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

對于 5.7 版本,出現(xiàn)鎖等待時,我們可以查看 information_schema 中的幾張系統(tǒng)表來查詢事務(wù)狀態(tài)。

innodb_trx 當(dāng)前運(yùn)行的所有事務(wù)。 innodb_locks 當(dāng)前出現(xiàn)的鎖。 innodb_lock_waits 鎖等待的對應(yīng)關(guān)系

# 鎖等待發(fā)生時 查看innodb_trx表可以看到所有事務(wù) # trx_state值為LOCK WAIT 則代表該事務(wù)處于等待狀態(tài)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:事務(wù)ID。trx_state:事務(wù)狀態(tài),有以下幾種狀態(tài):RUNNING、LOCK WAIT、ROLLING BACK 和 COMMITTING。trx_started:事務(wù)開始時間。trx_requested_lock_id:事務(wù)當(dāng)前正在等待鎖的標(biāo)識,可以和 INNODB_LOCKS 表 JOIN 以得到更多詳細(xì)信息。trx_wait_started:事務(wù)開始等待的時間。trx_weight:事務(wù)的權(quán)重。trx_mysql_thread_id:事務(wù)線程 ID,可以和 PROCESSLIST 表 JOIN。trx_query:事務(wù)正在執(zhí)行的 SQL 語句。trx_operation_state:事務(wù)當(dāng)前操作狀態(tài)。trx_tables_in_use:當(dāng)前事務(wù)執(zhí)行的 SQL 中使用的表的個數(shù)。trx_tables_locked:當(dāng)前執(zhí)行 SQL 的行鎖數(shù)量。trx_lock_structs:事務(wù)保留的鎖數(shù)量。trx_isolation_level:當(dāng)前事務(wù)的隔離級別。# sys.innodb_lock_waits 視圖也可看到事務(wù)等待狀況,且給出了殺鏈接的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 視圖整合了事務(wù)等待狀況,同時給出殺掉堵塞源端的 kill 語句。不過是否要?dú)⒌翩溄舆€是需要綜合考慮的。

死鎖與鎖等待稍有不同,我們同樣也來簡單復(fù)現(xiàn)下死鎖現(xiàn)象。

# 開啟兩個事務(wù)# 事務(wù)一執(zhí)行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# 事務(wù)二執(zhí)行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# 回到事務(wù)一執(zhí)行 回車后 此條語句處于鎖等待狀態(tài)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# 回到事務(wù)二再執(zhí)行 此時二者相互等待發(fā)生死鎖mysql> update test_tb set col3 = ’gddx’ where col1 = ’a’;ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

發(fā)生死鎖后會選擇一個事務(wù)進(jìn)行回滾,想查明死鎖原因,可以執(zhí)行 show engine innodb status 來查看死鎖日志,根據(jù)死鎖日志,結(jié)合業(yè)務(wù)邏輯來進(jìn)一步定位死鎖原因。

在實(shí)際應(yīng)用中,我們要盡量避免死鎖現(xiàn)象的發(fā)生,可以從以下幾個方面入手:

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

本篇文章簡單介紹了鎖等待及死鎖發(fā)生的原因,其實(shí)真實(shí)業(yè)務(wù)中發(fā)生死鎖還是很難分析的,需要一定的經(jīng)驗(yàn)積累。本篇文章只是面向初學(xué)者,希望各位對死鎖能夠有個初印象。

以上就是MySQL鎖等待與死鎖問題分析的詳細(xì)內(nèi)容,更多關(guān)于MySQL鎖等待與死鎖的資料請關(guān)注好吧啦網(wǎng)其它相關(guān)文章!

標(biāo)簽: MySQL 數(shù)據(jù)庫
相關(guān)文章:
主站蜘蛛池模板: 天津蒸汽/热水锅炉-电锅炉安装维修直销厂家-天津鑫淼暖通设备有限公司 | 北京晚会活动策划|北京节目录制后期剪辑|北京演播厅出租租赁-北京龙视星光文化传媒有限公司 | 环境模拟实验室_液体-气体控温机_气体控温箱_无锡双润冷却科技有限公司 | ASA膜,ASA共挤料,篷布色母料-青岛未来化学有限公司 | 铝合金重力铸造_铝合金翻砂铸造_铝铸件厂家-东莞市铝得旺五金制品有限公司 | 模温机-油温机-电加热导热油炉-工业冷水机「欧诺智能」 | 压滤机滤板_厢式_隔膜_板框压滤机滤板厂家价格型号材质-大凯环保 | 艺术涂料_进口艺术涂料_艺术涂料加盟_艺术涂料十大品牌 -英国蒙太奇艺术涂料 | 铝机箱_铝外壳加工_铝外壳厂家_CNC散热器加工-惠州市铂源五金制品有限公司 | 锂电混合机-新能源混合机-正极材料混料机-高镍,三元材料混料机-负极,包覆混合机-贝尔专业混合混料搅拌机械系统设备厂家 | 礼仪庆典公司,礼仪策划公司,庆典公司,演出公司,演艺公司,年会酒会,生日寿宴,动工仪式,开工仪式,奠基典礼,商务会议,竣工落成,乔迁揭牌,签约启动-东莞市开门红文化传媒有限公司 | 济南电缆桥架|山东桥架-济南航丰实业有限公司 | 行业分析:提及郑州火车站附近真有 特殊按摩 ?2025实地踩坑指南 新手如何避坑不踩雷 | 抓斗式清污机|螺杆式|卷扬式启闭机|底轴驱动钢坝|污水处理闸门-方源水利机械 | 应急灯_消防应急灯_应急照明灯_应急灯厂家-大成智慧官网 | 预制舱-电力集装箱预制舱-模块化预制舱生产厂家-腾达电器设备 | 钢制拖链生产厂家-全封闭钢制拖链-能源钢铝拖链-工程塑料拖链-河北汉洋机械制造有限公司 | 雷冲击高压发生器-水内冷直流高压发生器-串联谐振分压器-武汉特高压电力科技有限公司 | 医学动画公司-制作3d医学动画视频-医疗医学演示动画制作-医学三维动画制作公司 | MVE振动电机_MVE震动电机_MVE卧式振打电机-河南新乡德诚生产厂家 | 电缆接头_防水接头_电缆防水接头_防水电缆接头_上海闵彬 | 玉米深加工设备|玉米加工机械|玉米加工设备|玉米深加工机械-河南成立粮油机械有限公司 | 海德莱电力(HYDELEY)-无功补偿元器件生产厂家-二十年专业从事电力电容器 | 承插管件_不锈钢承插管件_锻钢高压管件-温州科正阀门管件有限公司 | 蔡司三坐标-影像测量机-3D扫描仪-蔡司显微镜-扫描电镜-工业CT-ZEISS授权代理商三本工业测量 | 膜结构车棚|上海膜结构车棚|上海车棚厂家|上海膜结构公司 | 烽火安全网_加密软件、神盾软件官网| 右手官网|右手工业设计|外观设计公司|工业设计公司|产品创新设计|医疗产品结构设计|EMC产品结构设计 | 低浓度恒温恒湿称量系统,强光光照培养箱-上海三腾仪器有限公司 | 亮化工程,亮化设计,城市亮化工程,亮化资质合作,长沙亮化照明,杰奥思【官网】 | 全自动五线打端沾锡机,全自动裁线剥皮双头沾锡机,全自动尼龙扎带机-东莞市海文能机械设备有限公司 | 设定时间记录电子秤-自动累计储存电子秤-昆山巨天仪器设备有限公司 | 缓蚀除垢剂_循环水阻垢剂_反渗透锅炉阻垢剂_有机硫化物-郑州威大水处理材料有限公司 | 细砂提取机,隔膜板框泥浆污泥压滤机,螺旋洗砂机设备,轮式洗砂机械,机制砂,圆锥颚式反击式破碎机,振动筛,滚筒筛,喂料机- 上海重睿环保设备有限公司 | 特种阀门-调节阀门-高温熔盐阀-镍合金截止阀-钛阀门-高温阀门-高性能蝶阀-蒙乃尔合金阀门-福建捷斯特阀门制造有限公司 | 济南玻璃安装_济南玻璃门_济南感应门_济南玻璃隔断_济南玻璃门维修_济南镜片安装_济南肯德基门_济南高隔间-济南凯轩鹏宇玻璃有限公司 | 电子万能试验机_液压拉力试验机_冲击疲劳试验机_材料试验机厂家-济南众标仪器设备有限公司 | 赛默飞Thermo veritiproPCR仪|ProFlex3 x 32PCR系统|Countess3细胞计数仪|371|3111二氧化碳培养箱|Mirco17R|Mirco21R离心机|仟诺生物 | 耐热钢-耐磨钢-山东聚金合金钢铸造有限公司 | 冷却塔改造厂家_不锈钢冷却塔_玻璃钢冷却塔改造维修-广东特菱节能空调设备有限公司 | 带压开孔_带压堵漏_带压封堵-菏泽金升管道工程有限公司 |