MySQL 8.0 Reference Manual(讀書筆記68節--Deadlocks)

来源:https://www.cnblogs.com/xuliuzai/p/18090935
-Advertisement-
Play Games

背景 近年來隨著國際形勢的變化,信創產業成為我國國家戰略的一部分。一直以來,一直以來,全球 ICT 產業底層標準、架構、產品、生態等要素均由國外公司或機構制定和控制,使我國 ICT 產業乃至廣大用戶面臨被卡脖子、數據泄露、信息安全等諸多風險,尤其是 2018年以來,中興、華為等公司的遭遇成為鮮活的實 ...


 Deadlocks的定義

A deadlock is a situation where different transactions are unable to proceed because each holds a lock that the other needs. Because both transactions are waiting for a resource to become available, neither ever release the locks it holds.

優化deadlocks的一般思路

To reduce the possibility of deadlocks, use transactions rather than LOCK TABLES statements; keep transactions that insert or update data small enough that they do not stay open for long periods of time; when different transactions update multiple tables or large ranges of rows, use the same order of operations (such as SELECT ... FOR UPDATE) in each transaction; create indexes on the columns used in SELECT ... FOR UPDATE and UPDATE ... WHERE statements.

Deadlocks與isolation level

The possibility of deadlocks is not affected by the isolation level, because the isolation level changes the behavior of read operations, while deadlocks occur because of write operations.

死鎖檢測

When deadlock detection is enabled (the default) and a deadlock does occur, InnoDB detects the condition and rolls back one of the transactions (the victim).【開啟了的化,就主動檢測,觸發處理】

If deadlock detection is disabled using the innodb_deadlock_detect variable, InnoDB relies on the innodb_lock_wait_timeout setting to roll back transactions in case of a deadlock.【沒開啟的化,就只能等超時】

Thus, even if your application logic is correct, you must still handle the case where a transaction must be retried. To view the last deadlock in an InnoDB user transaction, use SHOW ENGINE INNODB STATUS. If frequent deadlocks highlight a problem with transaction structure or application error handling, enable innodb_print_all_deadlocks to print information about all deadlocks to the mysqld error log.【如果經常遇到死鎖,可以開通設置,將死鎖信息輸出到error log中】

死鎖時,如何選擇被放棄的事務

InnoDB tries to pick small transactions to roll back, where the size of a transaction is determined by the number of rows inserted, updated, or deleted.

具體而言,如何減少deadlock

You can cope【處理、對付】 with deadlocks and reduce the likelihood of their occurrence【發生、出現】 with the following techniques:

• At any time, issue SHOW ENGINE INNODB STATUS to determine the cause of the most recent deadlock. That can help you to tune your application to avoid deadlocks.【多復盤】

• If frequent deadlock warnings cause concern, collect more extensive debugging information by enabling the innodb_print_all_deadlocks variable. Information about each deadlock, not just the latest one, is recorded in the MySQL error log. Disable this option when you are finished debugging.---【儘可能收集死鎖相關的信息】

• Always be prepared to re-issue a transaction if it fails due to deadlock. Deadlocks are not dangerous. Just try again.【收集後,多分析】

• Keep transactions small and short in duration to make them less prone to collision【碰撞、衝突】.【事務,應保持短小精悍】

• Commit transactions immediately after making a set of related changes to make them less prone to collision. In particular, do not leave an interactive mysql session open for a long time with an uncommitted transaction.【事務,應乾凈利索,不要拖泥帶水】

• If you use locking reads (SELECT ... FOR UPDATE or SELECT ... FOR SHARE), try using a lower isolation level such as READ COMMITTED.【滿足要求的隔離級別,不過分要求高級別】

• When modifying multiple tables within a transaction, or different sets of rows in the same table, do those operations in a consistent order each time. Then transactions form well-defined queues and do not deadlock. For example, organize database operations into functions within your application, or call stored routines, rather than coding multiple similar sequences of INSERT, UPDATE, and DELETE statements in different places.

• Add well-chosen indexes to your tables so that your queries scan fewer index records and set fewer locks. Use EXPLAIN SELECT to determine which indexes the MySQL server regards as the most appropriate for your queries.【擁有合適的索引】

• Use less locking. If you can afford to permit a SELECT to return data from an old snapshot, do not add a FOR UPDATE or FOR SHARE clause to it. Using the READ COMMITTED isolation level is good here, because each consistent read within the same transaction reads from its own fresh snapshot.【少用鎖】

• If nothing else helps, serialize your transactions with table-level locks. The correct way to use LOCK TABLES with transactional tables, such as InnoDB tables, is to begin a transaction with SET autocommit = 0 (not START TRANSACTION) followed by LOCK TABLES, and to not call UNLOCK TABLES until you commit the transaction explicitly.Table-level locks prevent concurrent updates to the table, avoiding deadlocks at the expense of less responsiveness for a busy system.

• Another way to serialize transactions is to create an auxiliary 【輔助的】“semaphore” table that contains just a single row. Have each transaction update that row before accessing other tables. In that way, all transactions happen in a serial【連續的;順序排列的】 fashion. Note that the InnoDB instant deadlock detection algorithm also works in this case, because the serializing lock is a row-level lock. With MySQL table-level locks, the timeout method must be used to resolve deadlocks.

死鎖相關的系統表(或SQL)

----performance_schema.data_locks;

----performance_schema.data_lock_waits;

----SELECT `count` FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME="lock_deadlocks";

 


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 前提 centos7系統 新建系統時選擇的預設分區 vgs等查看捲組命令無回顯 根目錄磁碟空間不足 大致流程及註意事項 處理方法:刪除再重建 不會導致其中的數據丟失 防止出現意外,請備份或者快照 1.率先在VMware中對虛擬機進行磁碟容量擴容 此處進行20g --> 40g的擴容 2.再在虛擬機中 ...
  • 問題描述 在使用VMWare17.5.0版本安裝ubuntu22.0.4.4的時候遇到問題。安裝完成ubuntu之後,我在虛擬機中點擊滑鼠左鍵沒有問題,單獨按下鍵盤也沒有問題,但是如果按下鍵盤的同時在按下滑鼠左鍵就會卡住。而且100%穩定復現。 具體的卡死按鍵: ctrl+滑鼠左鍵 alt+滑鼠左鍵 ...
  • Ubuntu Server 20.04詳細安裝教程 1. Ubuntu Server20.04啟動盤製作 1.1 下載鏡像 去Ubuntu官網找到20.04的鏡像文件(20.04下載地址),按照自己的需求下載 iso文件 1.2 製作啟動盤 用Rufus製作啟動盤,到官網下載文件(rufus官網) ...
  • 蘇州農商銀行基於GaussDB資料庫,對核心業務系統“超級網銀”進行了全面改造升級,極大提升了系統的穩定性和安全性。 ...
  • 在GreatSQL中,Binlog可以說是 GreatSQL 中比較重要的日誌了,在日常開發及運維過程中經常會遇到。Binlog即Binary Log,二進位日誌文件,也叫作變更日誌(Update Log)。 詳細Binglog日誌介紹 Binglog主要應用於數據恢復和數據複製,但是在Binlog ...
  • 本文分享自華為雲社區《GaussDB(DWS)業務高可靠原理》,作者: yd_291396996。 1. 前言 適用版本:【8.1.0及以上】 GaussDB(DWS)所有內部組件CN、DN、GTM、CM等採用多活或主備設計,通過集群管理進行故障檢測和切換,保證了單點故障場景下業務的可靠性。此外還採 ...
  • windows 安裝sqlserver服務 SQL Server 下載 | Microsoft 或者MSDN, 我告訴你 - 做一個安靜的工具站 (itellyou.cn) 安裝 關閉windows防火牆並重新運行 一路下一步直到 這樣設置可以讓安裝該服務的用戶直接 安裝管理工具 docker 20 ...
  • 增 增加單條數據 insert into Department(DepartmentName,DepartmentDesc) values('研發部','這是研發部')--插入單條數據 多條 /*insert into [Rank](RankName,RankDesc) select 'A','A級 ...
一周排行
    -Advertisement-
    Play Games
  • 不廢話,直接代碼 private Stack<Action> actionStack = new Stack<Action>(); private void SetCellValues() { var worksheet = Globals.ThisAddIn.Application.ActiveS ...
  • OpenAPI 規範是用於描述 HTTP API 的標準。該標準允許開發人員定義 API 的形狀,這些 API 可以插入到客戶端生成器、伺服器生成器、測試工具、文檔等中。儘管該標準具有普遍性和普遍性,但 ASP.NET Core 在框架內預設不提供對 OpenAPI 的支持。 當前 ASP.NET ...
  • @DateTimeFormat 和 @JsonFormat 是 Spring 和 Jackson 中用於處理日期時間格式的註解,它們有不同的作用: @DateTimeFormat @DateTimeFormat 是 Spring 框架提供的註解,用於指定字元串如何轉換為日期時間類型,以及如何格式化日 ...
  • 一、背景說明 1.1 效果演示 用python開發的爬蟲採集軟體,可自動抓取抖音評論數據,並且含二級評論! 為什麼有了源碼還開發界面軟體呢?方便不懂編程代碼的小白用戶使用,無需安裝python、無需懂代碼,雙擊打開即用! 軟體界面截圖: 爬取結果截圖: 以上。 1.2 演示視頻 軟體運行演示視頻:見 ...
  • SpringBoot筆記 SpringBoot文檔 官網: https://spring.io/projects/spring-boot 學習文檔: https://docs.spring.io/spring-boot/docs/current/reference/html/ 線上API: http ...
  • 作為後端工程師,多數情況都是給別人提供介面,寫的好不好使你得重視起來。 最近我手頭一些活,需要和外部公司對接,我們需要提供一個介面文檔,這樣可以節省雙方時間、也可以防止後續扯皮。這是就要考驗我的介面是否規範化。 1. 介面名稱清晰、明確 顧名思義,介面是做什麼的,是否準確、清晰?讓使用這一眼就能知道 ...
  • 本文介紹基於Python語言,遍歷文件夾並從中找到文件名稱符合我們需求的多個.txt格式文本文件,並從上述每一個文本文件中,找到我們需要的指定數據,最後得到所有文本文件中我們需要的數據的合集的方法~ ...
  • Java JUC&多線程 基礎完整版 目錄Java JUC&多線程 基礎完整版1、 多線程的第一種啟動方式之繼承Thread類2、多線程的第二種啟動方式之實現Runnable介面3、多線程的第三種實現方式之實現Callable介面4、多線的常用成員方法5、線程的優先順序6、守護線程7、線程的讓出8、線 ...
  • 實時識別關鍵詞是一種能夠將搜索結果提升至新的高度的API介面。它可以幫助我們更有效地分析文本,並提取出關鍵詞,以便進行進一步的處理和分析。 該介面是挖數據平臺提供的,有三種模式:精確模式、全模式和搜索引擎模式。不同的模式在分詞的方式上有所不同,適用於不同的場景。 首先是精確模式。這種模式會儘量將句子 ...
  • 1 為啥要折騰搭建一個專屬圖床? 技術大佬寫博客都用 md 格式,要在多平臺發佈,圖片就得有外鏈 後續如博客遷移,國內博客網站如掘金,簡書,語雀等都做了防盜鏈,圖片無法遷移 2 為啥選擇CloudFlare R2 跳轉:https://dash.cloudflare.com/ 有白嫖額度 免費 CD ...