當前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

mysql ---- innodb-3-锁、事务

發布時間：2024/9/15 数据库 24 豆豆

生活随笔收集整理的這篇文章主要介紹了 mysql ---- innodb-3-锁、事务小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

6 鎖

數據庫區別與文件系統的一個關鍵特性

6.1 什么是鎖

鎖機制用于管理對共享資源的并發訪問。

Innodb引擎不僅僅是在行級別上使用鎖，在其他方面也會使用。例如：操作緩沖池中的LRU列表，刪除、添加、移動LRU列表中的元素

6.2 InnoDB存儲引擎中的鎖

6.2.1 鎖類型

兩種標準的行級鎖：

共享鎖(S lock)：允許事務讀一行數據

排他鎖(X lock)：允許事務刪除或更新一行數據

為了支持在不同粒度上進行加鎖操作，Innodb 引擎支持意象鎖(Intention Lock)，意象鎖是將鎖定的對象分為多個層次，意象鎖意味著事務希望在更細粒度上進行加鎖。(不會阻塞除全表掃描之外的其他任何請求)

在InnoDB Plugin之前，只能通過SHOW FULL PROCESSLIST、SHOW ENGINE INNODB STATUS等命令來查看當前的數據庫請求，然后在判斷當前事務中鎖的情況。

新版本中在INFORMATION_SCHEMA架構下添加了INNODB_TRX、INNODB_LOCKS、INNODB_LOCK_WAITS三張表。通過這三張表可以簡單的監控當前的事務并分析鎖的問題。

Mysql8.0 將 information_schema.innodb_locks改為 performance_schema.data_locks

INNODB_TRX字段

trx_id：事務id
Trx_state：當前事務的狀態
trx_started: 事務開啟時間
trx_requested_lock_id：等待事務的鎖ID
trx_wait_started:事務等待開始的時間
trx_weight:事務權重，反應了一個事務修改和鎖住的行數
trx_mysql_thread_id： mysql中的線程ID，show processlist 顯示的結果
trx_query: 事務運行的sql，實際中有時會顯示null

INNODB_LOCKS的字段

lock_id：鎖的id
lock_trx_id：事務的id
lock_mode：鎖的模式
lock_type：鎖的類型，行鎖或者是表鎖
lock_table：要枷鎖的表
lock_space：Innodb引擎表空間的id
lock_page：被鎖住的頁的數量，若為表鎖，則為null
lock_rec：被鎖主的行的數量，若為表鎖，澤偉null
lock_data：被鎖住行的主鍵，歐維表鎖，則為null

INNODB_LOCK_WAITS的字段

requesting_trx_id：申請鎖資源的事務id
requesting_lock_id：申請的鎖的id
block_trx_id：阻塞的事務id
block_lock_id：阻塞的鎖的id

6.2.2 一致性的非鎖定讀操作

非鎖定讀大大提高了讀取的并發性，是Innodb引擎中默認的讀取方式，即讀取不會占用和等待表上的鎖。

一致性的非鎖定讀(consistent nonlocking read)是指InnoDB引擎通過多版本控制(multi versioning)的方式來讀取當前執行時間數據庫中的數據。如果讀取的數據正在執行DELETE、UPDATE操作，這是操作并不會等待鎖的釋放，而是會去讀取行的一個快照數據。

上圖展示了Innodb引擎的一致性非鎖定讀(不需要等待X鎖的釋放)。快照數據是指該行數據之前版本的數據，該實現是通過Undo段實現的。而Undo用來在事務回滾數據，因此快照數據本身沒有額外的開銷(此外讀取快照數據不需要上鎖)

注意：

在不同事務隔離級別下，讀取的方式不同，并不是每個事務隔離級別下讀取的都是一致性讀。

在上圖得知，快照數據并不是只有一個版本，一個行可能有不止一個快照數據(多版本技術)。由此帶來的并發控制稱為多版本并發控制(Multi Version Concurrency Control, MVCC)。
- Read Committed
  
  在讀已提交事務隔離級別下，對于快照數據，總是讀取被鎖定行的最新的一份快照數據。
- Repeatable Read (innodb 引擎的默認隔離級別)
  
  在可重復度事務隔離級別下，對于快照數據，總是讀取事務開始時行的快照數據
上述情況的例子：
-- 回話A： begin; -- 開啟事務 select * from table where id = 1; -- 此時查出id=1的數據 (沒有提交事務)-- 會話B：(此時A沒有結束事務) begin; update table set id = 3 where id = 1; -- 將id改為3 (事務同樣沒有提交)
經過上述步驟，此時的會話B給行加了一個X鎖，如果此時再次回到會話A中讀取數據，根據Innodb引擎的特性，在Read Committed和Repeatable Read事務隔離級別下使用非鎖定一致性讀。

在上述兩個隔離級別下，會話A中仍然能夠查到id為1的數據(因為此時只有一份快照數據，因此不管哪個事務隔離級別，結果都一樣)。

然后回到會話B中，執行commit，提交事務，然后在回到會話A中，執行查詢，此時兩種隔離級別的查詢結構就不同了，

對于 ReadCommitted 事務隔離級別來說，他總是讀取行的最新版本，如果行被鎖定了，則讀取該行版本的最新的一個快照(fresh snapshot)。在上述情況下，會話 B 已經提交，所以 Read Committed事務隔離級別下，執行 sql select * from table where id = 1是查不到數據的

對于 Repeatable Read事務隔離級別，總是讀取事務開始的行數據，因此執行 sqlselect * from table where id = 1仍然可以查到 id 為1的數據。
-- 查詢當前事務隔離級別select @@tx_isolation;
對于 Read Committed事務隔離級別來說，其實是違背了事務的 ACID 中的 I特性(隔離性)

在上邊說過，默認情況下，innodb 引擎的 select 操作使用一致性非鎖定讀。但在某些情況下，需要對讀進行加鎖操作。

讀加鎖操作的兩種方式：

SELECT ... FOR UPDATE

對讀的行記錄加一個 X 鎖(排它鎖)，其他事務想在這些行上加任何鎖都會被阻塞

對于一致性非鎖定讀，此時讓然可以進行讀取

SELECT ... LOCK IN SHARE MODE

對讀取操作加一個 S 鎖(共享鎖)，其他事務可以向被鎖定的行加 S 鎖，但是對于X 鎖，會被阻塞

另外，上述兩種方式，必須在一個事務中，當事務提交了，鎖也就失效了(因為在使用上邊兩個語句時，是要加上 BEGIN、START TRANSACTION、SET AUTOCOMMIT=0)

6.2.4 自增長和鎖

對于含有自增長值的表都有一個自增長計數器(auto-increment counter)。當插入時，這個計數器會被初始化，執行以下 sql 來獲取值：

select MAX(auto_inc_col) from t for update;

插入操作會根據這個值加1給自增長列。即 AUTO-INC Locking，這種鎖定機制其實是一種表鎖機制，為了提高性能，鎖不是在一個事務完成之后才釋放，而是在完成對自增長值的插入后立即釋放。(這種方式對于大批量數據會影響效率)

在 mysql5.1.22版本開始，提供了一種輕量級互斥量的自增長實現機制，參數 innodb_autoinc_lock_mode默認值為1。(不同的參數對于不同的插入有不同的影響)

插入方式分類：

INSERT-like：指所有的插入語句，例如 INSERT、REPLACT、INSERT ... SELECT、REPLACE ... SELECT、LOAD DATA等

Simple inserts：指在插入之前就能確定插入的行數，如：INSERT、REPLACE，不包括INSERT ...ON DUPLICATE KEY REPLACE

Bulk inserts：指插入之前不能確定行數，如INSERT ... SELECT、REPLACE ... SELECT、LOAD DATA

Mixed-mode inserts：指插入數據有一部分指是自增長的，有一部分指是確定的，如：INSERT INTO t1(c1,c2)VALUES (1,'A'),(NULL,'B'),(4,'C'),(NULL,'D');也可以指INSERT ...ON DUPLICATE KEY REPLACE這類 sql

innodb_autoinc_lock_mode有3個可選指：

0，mysql5.1.22版本之前的自增長實現方式

1，默認值，對于 Simple inserts 會使用互斥量產生自增長值，對于 Bulk Inserts 仍然使用 AUTO-INC Locking 的方式

2，對于所有的 INSERT-like 自增長值的產生都是通過互斥量，而不是 AUTO-INC Locking (性能最高的方式)

innodb_autoinc_lock_mode=2，會有兩個問題：一是并發插入，會導致自增長值不是連續的，二是，對于基于 Statement-Base Replication 的復制會出現問題(Row-Base Replication 沒問題)

6.2.5 外鍵和鎖

6.3 鎖的算法

Innodb 引擎有3中行鎖：

Record Lock：單個行記錄上的鎖(總是會去鎖定索引的，如果沒有索引，Innodb 引擎會使用隱式的主鍵來進行鎖定)

Gap Lock：間隙鎖，鎖定一個范圍，但是不包括記錄本身

Next-Key Lock：Gap Lock + Record Lock，鎖定一個范圍，包括記錄(Innodb 引擎對于行的查詢都是基于這種算法，對于不同 sql 查詢語句，可能設置共享Next-Key Lock 和排他的 Next-Key Lock) -- 解決了幻讀問題

例子：

begin; select * from t where id < 5 lock in share mode; -- 在上述情況下執行 insert into t (id) values(3); -- 此時插入語句會阻塞 -- 因為在 Next-Key Locking 算法下，鎖定的是 id < 6的所有值，但是如果插入 id=9，是可以的-- 如果是查詢指定 id 的值，是不會影響其他數據的插入的，因為 innodb 引擎會選擇一個最小的算法模型

6.4 鎖問題

丟失更新

臟讀

不可重復讀

6.4.1 丟失更新

兩個查詢，分別顯示到兩個頁面，兩個分別更新，第二次更新會直接覆蓋第一次更新，導致了第一次更新丟失

解決：數據上加版本號，更新時對比版本號

6.4.2 臟讀

是指讀到了未提交的數據(這種情況只會發生在事務隔離級別設置為 Read Uncommitted的情況下)

6.4.3 不可重復讀

多次讀到的數據不同，發生在事務隔離級別為 Read Committed的情況下，違反了隔離性

不可重復讀的問題是可以接受的，因此像 oracle、microsoft sql sever 的隔離級別都是讀已提交

mysql 通過 Next-key Lock算法來避免不可重復讀

6.5 阻塞

Innodb 引擎中，通過參數 innodb_lock_wait_timeout 來控制等待的時間(默認50秒)，innodb_rollback_on_timeout用來設定是否在等待超時時對事物進行回滾操作(默認為 OFF，代表不回滾)

6.6 死鎖

Innodb 存儲引擎并不會回滾大部分的異常，但是死鎖除外，當發生死鎖時，Innodb 會馬上回滾一個事務

6.7 鎖升級

鎖升級(Lock Escalation)是指將當前鎖的粒度降低。例如：數據庫可以把一個表的1000個行鎖升級為一個頁鎖，或者將頁鎖升級為表鎖。

Innodb 存儲引擎中不存在鎖升級的問題，1個鎖和10000個鎖時一樣的，沒有開銷

7 事務

用來保證數據的完整性

分類：

扁平事務(Flat Transaction)

是事務類型中最簡單的一種，但是實際生產環境中使用最頻繁，即其間操作都是原子的，要么都執行，要么都回滾。因此扁平事務是應用程序稱為原子操作的基本組成模塊。

扁平事務的主要限制是不能提交或回滾事務的某一部分，或分成幾個步驟提交。

帶有保存點的扁平事務(Flat Transaction with savepoints)

除了支持扁平事務支持的操作外，允許在事務執行的過程中回滾到同一事務中較早的一個狀態。這是因為某些事務可能執行過程中出現的錯誤并不會導致所有的操作都無效，放棄整個事務不合乎要求。

保存點(savepoint)用來同值系統應該記住事務當前的狀態，以便事務發生錯誤時，事務能回到保存點當時的狀態。

鏈式事務(Chained Transactions)

可視為保存點模式的一個變種。其區別是，帶有保存點的扁平事務能回滾到任意正確的保存點。而鏈式事務中回滾僅限于道歉事務。

嵌套事務(Nested Transactions)

是一種層次結構框架。由一個頂層事務控制著各個層次的事務。頂層事務之下嵌套的事務被稱為漬食物，其控制每一個局部的變換。

分布式事務(Distributed Transactions)

innodb引擎支持扁平事務，帶有保存點的扁平事務。鏈式事務。分布式事務。

7.1 概述

事務的特性：ACID

原子性(atomicity)

指整個數據庫事務是不可分割的單位。只有所有的數據庫操作都執行成功才算成功。

一致性(consistency)

指事務將數據庫從一種狀態轉變為下一種一致性的狀態，在事務開始和結束以后，數據庫的完整性約束沒有破壞

隔離性(isolation)

一個事務的影響在該事務提交前對其他事務不可見--通過鎖實現的

持久性(durability)

事務一旦提交，其結果就是永久性的

另事務的隔離級別：

讀未提交

讀已提交

可重復讀

序列化讀

7.2 事務的實現

隔離性通過鎖來實現

原子性、一致性、持久性通過數據庫的 redo 和 undo 來完成

原子性、持久性 ---> redo

持久性 ----> undo

7.2.1 redo

重做日志用來實現事務的持久性，即事務ACID中的D。由兩部分組成，一是內存中的重做日志緩沖(redo log buffer)，其實易失的。二是重做日志文件(redo log file)，其實持久的。

當開始一個事務時，會記錄該事務的一個 LSN(Log Sequence Number，日志序列號)，當事務執行時，會往 Innodb 引擎的日志緩沖里插入事務日志；當事務提交時，必須將 InnoDB 引擎的日志緩沖寫入磁盤。也就是在寫數據前，需要先寫日志(預寫日志方式 Write-Ahead Logging, WAL)

在innodb引擎中，重做日志都是以512字節進行存儲的。這意味著重做日志緩存、重做日志文件都是以塊(block)的方式保存的，也就是重做日志塊(redo log block)，每塊為512字節。

若一個頁中產生的重做日志數量大于512字節，那么需要分割為多個重做日志塊及逆行存儲。此外，由于重做日志塊的大小和扇區大小一樣，都是512字節，因此重做日志的寫入可以保證原子性，不需要doublewrite技術。

重做日志文件中存儲的就是log buffer中保存的log block，因此其也是根據塊的方式進行物理存儲的管理，每個塊的大小與log block一樣，為512字節。在innodb引擎運行過程中，log buffer根據一定的規則將內存中的log block刷新到磁盤：1. 事務提交時 2. 當log buffer中有一般的內存空間已經被使用時 3. log checkpoint 時

對于log block的寫入追加在redo log file的最后部分，當一個redo log file寫滿時，會接著寫入下一個redo log file。

雖然log block的寫入追加在redo log file的最后部分，但是log buffer刷新到磁盤是并不是順序地，因為在刷新是，會刷新redo log file前2KB的信息。

LSN是log sequence Number的縮寫，代表日志序列號。在Innodb引擎中占用8字節。含義有：

重做日志寫入的總量

checkpoint的位置

頁的版本

LSN不僅記錄在重做日志中，還存在與每個頁中。在每個頁的頭部，有一個值FIL_PAGE_LSN，記錄了該頁的LSN。

7.2.2 undo

重做日志記錄了事務的行為，可以很好地通過其進行重做，但是事務還需要撤銷(undo)。

undo 和 redo 正好相反，對數據庫進行修改時，不但會產生 redo，還會產生一定的 undo。

和 redo不同，redo 存放在重做日志文件中，undo 存放在數據庫內部的一個特殊段(segment)中，也就是 undo 段，該段位于共享表空間內。(從innodb1.2版本開始可以指定獨立的表空間) (innodb_undo_directiry/ innodb_undo_logs / innodb_undo_tablespaces)

邏輯的將數據恢復到之前的樣子，例如：對于每個 INSERT，存儲引擎回滾時會執行 DELETE，對于 DELETE，回執行 INSERT，對于 UPDATE，會執行相反的 UPDATE。

除了回滾操作外，undo另一個作用是MVCC。

undo log會產生redo log，也就是undo log的產生會伴隨著redo log的產生，這時因為undo log也需要持久性的保護。

7.3 事務控制語句

默認事務都是自動提交的

手動開始事務的3中方式：

BEGIN

START TRANSACTION

SET AUTOCOMMIT=0

BEGIN、START TRANSACTION ，當執行這倆個時，mysql 會自動執行 SET AUTOCOMMIT=0(在默認配置下)，當執行 COMMIT或者 ROLLBACK 時，會自動執行 SET AUTOCOMMIT=1

事務控制語句：

START TRANSACTION | BEGIN：顯示開啟一個事務

COMMIT：提交事務

ROLLBACK：回滾

SAVEPOINT identifier：在事務中創建一個保存點(可以有多個)

RELEASE SAVEPOINT identifier：刪除一個事務的保存點(當沒有保存點時，會跑出異常)

ROLLBACK TO [SAVEPOINT] identifier：和 SAVEPOINT 命令一起使用，可以把事務回滾到標記點，而標記點之前的事務不回滾

SET TRANSACTION：設置事務的隔離級別(READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ、SERIALIZABLE)

7.4 隱式提交的 sql

當執行完這些sql 后，會有一個隱式的 COMMIT 操作(即執行這些操作后，不能回滾)：

DDL 語句(包括 alter、create、drop、rename、truncate) (注意delete 不是 DDL，因此可以回滾)
用來隱式的修改 mysql 架構的操作：CREATE USER 、DROP USER、GRANT、RENAME USER、REVOKE、SET PASSWORD
管理語句：ANALYZE TABLE、CACHE INDEX、CHECK TABLE、LOAD INDEX INTO CACHE、OPTIMIZE TABLE、REPAIR TABLE

7.5 對于事務的統計操作

每秒請求數(Question Per Second，QPS)、每秒事務處理能力(Transaction Per Second，TPS)

TPS=(com_commit + com_rollback) / time

7.7 分布式事務

Innodb 存儲引擎支持 XA 事務，通過 XA 事務可以實現分布式事務。

分布式事務是指允許多個獨立的事務資源(transactional resources)參與一個全局事務。

在使用分布式事務時，Innodb 存儲引擎的事務隔離級別必須設置為 SERIALIABLE

分布式事務由一個或者多個資源管理器(Resource Managers)、一個事務管理器(Transaction Manager)以及一個應用程序(Application Program)組成。

分布式事務才有兩階段提交(tow-phase commit)的方式，在第一階段，所有參數全局事務的節點都開始準備(PREPARE)，告訴事務管理器他們準備好了，第二階段，事務管理器告訴資源管理器執行 ROLLBACK 還是 COMMIT，如果任何一個節點顯示不能提交，則所有節點都要回滾。

總結

以上是生活随笔為你收集整理的mysql ---- innodb-3-锁、事务的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。

上一篇： mysql ---- innodb-2-
下一篇： mysql ---- innodb-4-