GaussDB資料庫SQL系列-LOCK TABLE

MySQL成长之路發表於2024-11-26
資料庫SQL

一、前言
GaussDB是一款高效能、高可用的分散式資料庫，廣泛應用於各類行業和場景。在GaussDB中，鎖是實現併發控制的關鍵機制之一，用於協調多個事務之間的資料訪問，確保資料的一致性和完整性。本文將圍繞GaussDB資料庫的LOCK TABLE 做一簡單介紹。

二、GaussDB資料庫的鎖
GaussDB提供了多種鎖模式用於控制對錶中資料的併發訪問。這些模式可以用在MVCC（多版本併發控制）無法給出期望行為的場合。同樣，大多數GaussDB命令自動施加恰當的鎖，以保證被引用的表在命令的執行過程中不會以一種不相容的方式被刪除或者修改。比如，在存在其他併發操作的時候，ALTER TABLE是不能在同一個表上執行的。

1、GaussDB中的LOCK TABLE
LOCK TABLE獲取表級鎖。

如果需要保持資料庫資料的一致性，可以使用LOCK TABLE來阻止其他使用者修改表。例如，一個應用需要保證表中的資料在事務的執行過程中不被修改。為實現這個目的，則可以對錶進行鎖定，這樣將防止資料不被併發修改。LOCK TABLE只在一個事務塊的內部有用，在事務結束時就會被釋放。

1）語法格式

LOCK [ TABLE ] name  IN {ACCESS SHARE | ROW SHARE | ROW EXCLUSIVE | SHARE UPDATE EXCLUSIVE | SHARE | SHARE ROW EXCLUSIVE | EXCLUSIVE | ACCESS EXCLUSIVE} MODE

2）引數說明

1）name：要鎖定的表的名稱。

2）鎖的模式：

ACCESS SHARE：只讀取表而不修改。所有對錶進行讀取而不修改的SQL語句都會自動請求這種鎖。
ROW SHARE：允許對錶進行併發讀取，禁止對錶進行其他操作。SELECT FOR UPDATE和SELECT FOR SHARE命令會自動在目標表上請求ROW SHARE鎖（且所有被引用但不是FOR SHARE/FOR UPDATE的其他表上，還會自動加上ACCESS SHARE鎖）。對於分割槽表，SELECT FOR SHARE操作還會在DN上獲取partition物件的ROW EXCLUSIVE鎖進行併發控制。
ROW EXCLUSIVE：與ROW SHARE鎖相同，ROW EXCLUSIVE允許併發讀取表，但是禁止修改表中資料。UPDATE，DELETE，INSERT命令會自動在目標表上請求這個鎖（且所有被引用的其他表上還會自動加上的ACCESS SHARE鎖）。通常情況下，所有會修改表資料的命令都會請求表的ROW EXCLUSIVE鎖。
SHARE UPDATE EXCLUSIVE：保護一個表的模式不被併發修改，以及禁止在目標表上執行垃圾回收命令（VACUUM）。VACUUM（不帶FULL選項）、ANALYZE、CREATE INDEX CONCURRENTLY命令會自動請求這樣的鎖。
SHARE：允許併發的查詢，但是禁止對錶進行修改。CREATE INDEX（不帶CONCURRENTLY）語句會自動請求這種鎖。
EXCLUSIVE:允許對目標表進行併發查詢，但是禁止任何其他操作。這個模式只允許併發加ACCESS SHARE鎖，也就是說，只有對錶的讀動作可以和持有這個鎖模式的事務併發執行。任何SQL語句都不會在使用者表上自動請求這個鎖模式。然而在某些操作的時候，會在某些系統表上請求它。
SHARE ROW EXCLUSIVE:禁止對錶進行任何的併發修改，而且是獨佔鎖，因此一個會話中只能獲取一次。任何SQL語句都不會自動請求這個鎖模式。
ACCESS EXCLUSIVE:保證其所有者（事務）是可以訪問該表的唯一事務。ALTER TABLE，DROP TABLE，TRUNCATE，REINDEX，CLUSTER，VACUUM FULL命令會自動請求這種鎖。在LOCK TABLE命令沒有明確宣告需要的鎖模式時，它是預設鎖模式。
2、示例一：ACCESS EXCLUSIVE 模式

--建立測試表
DROP TABLE IF EXISTS omm2.company;
CREATE TABLE omm2.company(
   id int4 PRIMARY key NOT NULL
  ,name varchar(10) NOT NULL
  ,age int4 NOT NULL
  ,address varchar(20) NOT NULL
  ,salary float4 NOT NULL
);

--初始化資料
INSERT INTO omm2.company VALUES (1, 'Paul', 32, 'California', 20000);
INSERT INTO omm2.company VALUES (2, 'Allen', 25, 'Texas', 15000);
INSERT INTO omm2.company VALUES (3, 'Teddy', 23, 'Norway', 20000);
INSERT INTO omm2.company VALUES (4, 'ZhangSan', 30, 'BeiJing', 30000);


--啟動一個事務
START TRANSACTION;
LOCK TABLE omm2.company IN ACCESS EXCLUSIVE MODE;
DELETE FROM omm2.company WHERE name ='Allen';
COMMIT;

解析：

“START TRANSACTION;”：啟動一個新的事務。在資料庫中，事務是一組一起執行的SQL語句，要麼全部成功，要麼全部失敗。這確保了資料的一致性。
“LOCK TABLE omm2.company IN ACCESS EXCLUSIVE MODE;”：對omm2.company表進行了排他性鎖定。在此模式下，其他會話不能對錶進行讀寫操作，直到這個事務結束。這樣可以防止在刪除操作過程中其他事務對錶產生干擾。
“DELETE FROM omm2.company WHERE name ='Allen'; ”：從omm2.company表中刪除了所有名為'Allen'的記錄。
“COMMIT;”：這行程式碼提交了前面的事務。在事務中執行的任何更改（在本例中是刪除操作）在提交後才會永久儲存在資料庫中。
總的來說，這段程式碼刪除了名為'Allen'的所有記錄，並確保這個操作在提交之前不會被其他事務干擾。

3、示例二：SHARE ROW EXCLUSIVE 模式

--複用示例一的測試表
CREATE TABLE omm2.company1 AS TABLE omm2.company;

--啟動一個事務
START TRANSACTION;
LOCK TABLE omm2.company1 IN SHARE ROW EXCLUSIVE MODE;
DELETE FROM omm2.company1 WHERE name ='Allen';
COMMIT;

解析：

“CREATE TABLE omm2.company1 AS TABLE omm2.company;”：建立了一個新的表omm2.company1，其結構複製自已有的表omm2.company。這種操作通常用於建立表的副本，或者為某個操作建立一個臨時的、與原表結構相同的新表。
“START TRANSACTION; ”：啟動一個新的事務。在資料庫中，事務是一組一起執行的SQL語句，要麼全部成功，要麼全部失敗，這可以確保資料的一致性。
“LOCK TABLE omm2.company1 IN SHARE ROW EXCLUSIVE MODE; ”：對錶omm2.company1進行了排他性鎖定。在這種模式下，其他會話不能對錶進行讀寫操作，直到這個事務結束，這樣可以防止在刪除操作過程中其他事務對錶產生干擾。
“DELETE FROM omm2.company1 WHERE name ='Allen';”：從表omm1.company1中刪除了所有名為'Allen'的記錄。
“COMMIT;”:提交前面的事務。在事務中執行的任何更改（在本例中是刪除操作）在提交後才會永久儲存在資料庫中。
三、小結
GaussDB資料庫的鎖機制是其重要的組成部分，用於支援併發控制和事務隔離。GaussDB實現了不同級別的事務隔離和併發控制，滿足了不同場景下的需求。當然了，使用GaussDB資料庫時也需要注意一些問題，如鎖的管理和資料庫的規劃等。合理的鎖管理和資料庫規劃能夠提高系統的效能和可用性，反之則可能導致資料一致性問題或者系統故障。

——結束

​https://bbs.huaweicloud.com/forum/forum-565-1.html

相關文章