[華為云在線課程][7天玩轉MySQL基礎實戰營][day05事務和鎖][學習筆記]

ACID

事務的定義

A database transaction symbolizes a unit of work performed within a database management system (or similar system) against a database, and treated in a coherent and reliable way independent of other transactions. A transaction generally

represents any change in a database. Transactions in a database environment have two main purposes:

To provide reliable units of work that allow correct recovery from failures and keep a database consistent even in cases of system failure, when execution stops (completely or partially) and many operations upon a database remain

uncompleted, with unclear status.

To provide isolation between programs accessing a database concurrently. If this isolation is not provided, the programs’ outcomes are possibly erroneous.

數據庫事務通常包含了對數據庫的一系列的讀/寫操作。

數據庫通過事務（transaction）來支持ACID的特性，保證整個數據庫的有效性和可靠性。

什么是ACID

In computer science, ACID (atomicity, consistency,isolation,durability) is a set of properties of database transactions intended to guarantee validity even in the event of errors, power failures, etc. In the context of database, a

sequence of database operations that satisfies the ACID properties (and these can be perceived as a single logical operation on the data) is called a transaction. For example, a transfer of funds from one bank account to another, even

involving multiple changes such as debiting one account and crediting another, is a single transaction.

ACID是一組特性，它們定義了即使是在錯誤，斷電等極端異常情況下，數據庫事務操作應該滿足的特性。不同數據庫對ACID的支持程度可能存在不同。

ACID具體規范

A - Atomically（原子性）

事務包括的語句集必須要么都執行成功，要么都執行失敗。任何一個語句的失敗應該使得整個事務都失敗

C - Consistency（一致性）

事務只能把數據庫從一個有效狀態改變到另一個有效狀態，期間所有的數據改變都要符合所有預定義的規則

I - Isolation（隔離性）

事務即使并行執行，它們對數據庫的修改應該和事務是串行執行的效果一樣

D - Durability（持久性）

事務一旦提交，它的修改和提交狀態不會因為系統的任何問題而消失

事務

事務的生命周期

都以MySQL的事務為例

事務一般有三種開啟方式

BEGIN/START TRANSACTION

AUTOCOMMIT = 0

AUTOCOMMIT = 1，一條語句即一個事務

事務的結束一般有四種方式

COMMIT

所有修改都會生效

ROLLBACK

所有修改都會失效，好像事務從未執行過

當前連接斷開，回滾當前事務

執行某些特定語句，如DDL

原有的事務會被隱式提交，再執行DDL

BEGIN/START TRANSACTION; ... ... COMMIT/ROLLBACK;

SET autocommit = 0; DML1; ... COMMIT/ROLLBACK;

SET autocommit = 1; SQL STATEMENT; -- 單語句自動提交

事務的隔離級別

隔離級別解決了isolation的問題，MySQL/InnoDB支持SQL92標準定義的四種隔離級別：

READ UNCOMMITTED – 不建議使用

事務內所有的不加鎖讀都有可能看到不可預期的過期的數據

READ COMMITTED – 建議使用

事務內所有的一致性讀，讀到的是每個讀語句自己建立的最新快照

REPEATABLE READ – 默認隔離級別

事務內所有的一致性讀，讀到的數據都是事務內第一個讀操作建立的快照

SERIALIZABLE – 一般不需要使用

類似 REPEATABLE READ ，但是對于事務內的讀，都會隱式的轉換成加鎖讀

事務的可見性

事務一定能看到自己的修改

事務可能看得到已提交的數據

事務可能看得到未提交的修改

事務的可見性 - Repeatable Read（1）

事務看到的始終是本事務第一次讀時時候能看到的內容

事務的可見性 - Repeatable Read（2）

事務看到的始終是本事務第一次讀時時候能看到的內容

事務的可見性 - Read Committed（1）

事務看到的始終時每個讀開始時刻已提交的數據

事務的可見性 - Read Committed（2）

事務看到的始終時每個讀開始時刻已提交的數據

事務的可見性 - Read Committed（3）

事務看到的始終時每個讀開始時刻已提交的數據

事務的可見性 - Read Uncommitted

事務看得到當前最新的未提交數據（謹慎使用）

事務的可見性 - Serializable

事務需要對讀到的數據進行加鎖（謹慎使用）

鎖

鎖定義

摘自Wikipedia：Record locking

Record locking is the technique of preventing simultaneous access to data in a database, to prevent inconsistent results.

To allow several users to edit a database table at the same time and also prevent inconsistencies created by unrestricted access, a single record can be locked when retrieved for editing or updating. Anyone attempting to retrieve the same

record for editing is denied write access because of the lock (although, depending on the implementation, they may be able to view the record without editing it). Once the record is saved or edits are canceled, the lock is released.

Records can never be saved so as to overwrite other changes, preserving data integrity.

In database management theory, locking is used to implement isolation among multiple database users. This is the “I” in the acronym ACID

數據庫系統使用鎖機制來支持事務的并發控制和隔離性

數據庫系統使用的鎖包含多個層面的，不同類型的鎖機制

用戶需要關心的主要包括兩種，表鎖和記錄鎖

鎖類型

鎖類型 - 顯示的表鎖

顯示表鎖有兩種類型

READ - 持有者只能讀加鎖的表，不同會話可以共同持有讀鎖

WRITE - 只有持有者可以讀寫加鎖的表，其他會話都不能訪問加鎖的表

語法：

LOCK TABLES t1 READ [, t2 READ [, t3 WRITE]]…;

UNLOCK TABLES;

所有當前會話要訪問表需要在同一個LOCK TABLES語句里面加鎖

加鎖語句會隱式的提交當前未完成事務

加鎖語句會隱式的釋放當前已持有的表鎖

加鎖粒度太大，不利于開發，謹慎使用

鎖類型 - 隱示的表鎖（1）

隱式表鎖一般對用戶不可見，用戶不可操作，但能感知到，主要用于數據庫內部并發同步保證正確性

鎖類型 - 隱示的表鎖（2）

隱式表鎖一般對用戶不可見，用戶不可操作，但能感知到，主要用于數據庫內部并發同步保證正確性

鎖類型 - 行鎖

行鎖存在于InnoDB存儲引擎層

行鎖主要類型

記錄鎖

間隙鎖

插入意向鎖

…

行鎖模式

共享鎖

互斥鎖

加鎖語句

DML 語句

SELECT 語句，帶加鎖提示

…

一致性讀和加鎖讀

InnoDB實現兩種不同的讀數據機制

一致性不加鎖讀

不加鎖

基于多版本機制

讀寫可執行

讀取的是指定時間點的快照內容，而不一定是最新內容

加鎖讀

讀取最新數據

基于鎖管理機制，按要求加鎖，鎖沖突需等待

可能產生死鎖

SELECT … LOCK IN SHARE MODE;

SELECT … FOR UPDATE;

鎖沖突

鎖沖突 - INSERT和SELECT（1）

INSERT和讀可能會沖突，例如先讀后插入場景

鎖沖突 - INSERT和SELECT（2）

INSERT和讀可能會沖突，例如先讀后插入場景

鎖沖突 - INSERT和INSERT

INSERT和INSERT可能會沖突

鎖沖突 - INSERT和DELETE（1）

INSERT和DELETE可能會沖突，例如先INSERT再DELETE場景

鎖沖突 - INSERT和DELETE（2）

INSERT和DELETE可能會沖突，例如先INSERT再DELETE場景

死鎖檢測

MySQL文檔對死鎖的說明：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 resource to become available, neither ever release the

locks it holds.

死鎖發生條件

多個事務并發

每個事務持有部分資源（行鎖），需要申請更多的資源（行鎖）

一旦申請存在相互依賴，資源等待構成環，即形成死鎖

死鎖檢測

MySQL/InnoDB內部默認會進行死鎖檢測，避免事務長時間等待

一旦檢測到死鎖，選擇一個事務進行回滾，其他事務可以繼續

禁用死鎖檢測

某些場景下，可以提高性能

通過innodb_lock_wait_timeout來控制死鎖超時

UPDATE語句導致的死鎖檢測和處理

最佳實踐

高效使用事務

高效使用事務 - 隔離級別的選擇

確認隔離級別對并發DML的影響：

最常用的隔離級別是REPEATABLE READ和READ COMMITTED

READ UNCOMMITTED和SERIALIZABLE請謹慎使用

高效使用事務 - 優化小事務

頻繁的單語句DML事務不利于性能

考慮將可以合并的DML在一個事務提交

多條語句合為一條語句

高效使用事務 - 大事務和小事務

避免大事務

主要指事務包含的語句很多，或者語句執行耗時很長

將大事務轉換成若干小事務提交，提高可靠性

優化大事務邏輯，如刪除全表數據改為TRUNCATE TABLE

注意DDL執行的耗時，以及它對資源、復制等其他問題的影響

避免長事務

主要指事務目前不繁忙，但是一直沒有提交

長事務占用連接資源

長事務可能占用系統資源，如磁盤空間等

長事務可能導致過期數據一直無法回收

高效使用事務 - 其他優化

確定是不是只能用只讀事務

START TRANSACTION READ ONLY;

可以提高性能

索引對加鎖的影響

如果表上沒有索引，一旦涉及到范圍加鎖，可能就整張表被鎖住

如果表上有唯一索引，唯一索引的加鎖粒度更小

如果使用二級索引掃描進行更新，二級索引和聚簇索引記錄都要加鎖

避免死鎖

死鎖導致事務回滾，降低系統效率，浪費系統資源，影響業務體驗

最主要的原則是避免死鎖條件的滿足

事務盡量小，比如只更新一條記錄，但不代表不會死鎖

事務盡量短，縮短或這避免沖突時間窗

事務更新多張表時，用同一個順序更新不同的表

事務更新一張表內的多行時，用同一個順序更新不同的行

另一個角度是減少事務的加鎖

避免事務（長時間）鎖一個范圍

如果一致性讀可以滿足要求，盡量少用加鎖讀

需要加鎖讀的時候，盡量使用READ COMMITTED隔離級別有利于減少死鎖的產生

使用索引掃描，減少事務加鎖的數量

如何監測和處理死鎖

應用程序做好重新啟動事務的準備，應對死鎖場景

SHOW ENGINE INNODB STATUS;/ 錯誤日志

根據死鎖信息，調整應用程序邏輯

MySQL

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。