You are currently viewing 事務控制和鎖定語句

事務控制和鎖定語句

LOCK TABLE 和 UNLOCK TABLE

LOCAK TABLE 可以鎖定用於當前執行緒的表,如果表被其他執行緒給鎖定,則當前執行緒會等待,直到可以獲取所有鎖定為止

UNLOCK TABLE 可以釋放當前執行緒所獲得的任何鎖定。當前執行緒執行另一個 LOCK TABLE 時,或是與當與伺服器的連接被關閉時,
所有由當前執行緒鎖定的表會被隱含的解鎖。

語法如下:

LOCK TABLES
        tbl_name [AS alias] { READ [LOCAL] | [LOW_PRIORITY] WRITE }
        [, tbl_name [AS alias] { READ [LOCAL] | [LOW_PRIORITY] WRITE }]....
UNLOCK TABLES

MySQL 交易功能 Transaction 整理

資料庫的交易( Transaction )功能,能確保多個 SQL 指令,全部執行成功,或全部不執行,不會因為一些意外狀況,而只執行一部份指令,造成資料異常。

MySQL 常用的兩個資料表類型:MyISAM、InnoDB,MyISAM 不支援交易功能,所以以下的整理,均是針對 InnoDB 而言。

交易功能4個特性( ACID )

  • Atomicity ( 原子性、不可分割 ):交易內的 SQL 指令,不管在任何情況,都只能是全部執行完成,或全部不執行。
    若是發生無法全部執行完成的狀況,則會回滾 ( rollback )到完全沒執行時的狀態。
  • Consistency ( 一致性 ):交易完成後,必須維持資料的完整性。所有資料必須符合預設的驗證規則、外鍵限制…等。
  • Isolation ( 隔離性 ):多個交易可以獨立、同時執行,不會互相干擾。這一點跟後面會提到的「隔離層級」有關。
  • Durability ( 持久性 ):交易完成後,異動結果須完整的保留。

事務控制

START TRANSACTION | BEGIN [WORK]

COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]

ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]

SET AUTOCOMMIT = { 0 | 1 }
  • START TRANSACTIONBEGIN 語句可以開始一項新的事物。
  • COMMITROLLBACK 用來提交或者回滾事務。
  • CHAINRELEASE 子句分別用來定義在事務提交或者回滾之後的操作,CHAIN 會立刻啟動一個新的事務,RELEASE 則會切斷和使用者端的連接。
  • SET AUTOCOMMIT 可以修改當前連接的提交方式,如果設置了 SET AUTOCOMMIT=0 則設置後的所有事務都需要透過明確命令來進行提交或者回滾。

autocommit

MySQL 的設計理念是在於,遇到 COMMIT; 語法時,才會將資料從記憶體寫入硬碟,並且預設會對所有 SQL 敘述後自動在其後加上 COMMIT;,在 MySQL 稱這項預設行為為自動提交( auto commit )。

若是要在當前連線全域關閉這個自動動提交( auto commit ),可以透過 SET autocommit=0; 敘述,當 autocommit 設置為 0 時,就會停用自動提交。若將這功能停用時,就必須自己加入 COMMIT; 以將之前執行成功且尚未提交過的敘述存入硬碟。

BEGIN; 不同,就算遇到 COMMIT;,自動提交仍會保持停用,直到將 autocommit 設置為 1 重啟這功能為止。

MySQL autocommit 設置示例:

SET autocommit=0;

UPDATE accounts SET deposit = deposit - 100.00 WHERE name = 'A';
UPDATE accounts SET deposit = deposit + 100.00 WHERE name = 'B';
COMMIT; -- COMMIT 並不會讓 autocommit 重新啟用

UPDATE accounts SET deposit = deposit - 100.00 WHERE name = 'C';
UPDATE accounts SET deposit = deposit + 100.00 WHERE name = 'D';
ROLLBACK; -- ROLLBACK 也不會讓 autocommit 重新啟用

SET autocommit=1; -- 只有將 autocommit 設置為 1 時,才會重啟自動提交的功能。

除了自動提交外,在 MySQL 參考手冊的 13.3.3 Statements That Cause an Implicit Commit 有提到,當遇到修改資料表的敘述、資料定義語言( DDL )、或是 transactions 控制流程和資料表鎖定相關的敘述時,在執行該敘述前,都會自動執行 COMMIT;,也就是所謂的隱性提交( implicitly commit )。

MySQL 隱性提交示例:

SET autocommit=0; -- 先停用自動提交

INSERT INTO log (content) VALUES ('A');

BEGIN; -- 執行此行敘述前,會進行隱性提交,導致前面的 SQL 敘述被存入硬碟。
INSERT INTO log (content) VALUES ('B');
COMMIT;

INSERT INTO log (content) VALUES ('C');

SET autocommit=1; -- 執行此行敘述前,會進行隱性提交,導致前面的 SQL 敘述被存入硬碟。

這也導致了,MySQL 不能與 PostgreSQL 一般,進行 transactions 的嵌套( nested ),因為若在 transactions 裡又輸入 BEGIN; 時,這之前的 SQL 敘述就會先被 COMMIT; 存入硬碟中。以下面程式碼為例,在 MySQL 中,First 是會被存入資料表的,PostgreSQL 則不會。

transactions 嵌套示例:

CREATE TABLE log (
    content varchar(255)
);

BEGIN;
    INSERT INTO log (content) VALUES ('First');

    BEGIN;
        INSERT INTO log (content) VALUES ('Second');
        ROLLBACK;
    COMMIT;

    ROLLBACK;
COMMIT;

LOCK TABLE 與 START TRANSACTION

如果在鎖表期間,用 start transaction 命令開始一個新的事務,則會造成一個隱含的 unlock tables 被執行

Session_1Session_2
從表中查詢 actor_id=200 的紀錄,結果為空
mysql> select * from actor where actor_id=200;
EMpty_set
從表中查詢 actor_id=200 的紀錄,結果為空
mysql> select * from actor where actor_id=200;
EMpty_set
對表 actor 加鎖
mysql> lock table actor write;
從表中查詢 actor_id=200 的紀錄,結果為空
mysql> select * from actor where actor_id=200;
等待
插入一條紀錄
mysql> insert into actor (actor_id, name) values (200, 'Tom');
等待
回滾剛才的紀錄
mysql> rollback;
等待
重新開始一個新的事務
mysql> start transaction;
等待
Session_1 開始一個事務,表被釋放
+----------+-----------+
| actor_id | last_name |
+----------+-----------+
| 200 | Tom |
+----------+-----------+
對 lock 方式加的鎖,不能透過 rollback 進行回滾

正常的情形下,只會對提交的事務記錄到二進制的日誌中( bin log ),但是如果一個事務中包含非事務類型的表,那麼回滾操作也會被紀錄到二進制的日誌中,以確保非事務類型的表的更新可以被複製到主從式資料庫( Slave )中。

SAVEPOINT

在事務中透過定義 SAVEPOINT,指定回滾事務的一個部分,如果定義了相同名字的 SAVEPOINT,則後定義的會覆蓋前面的。

對於不再需要使用的 SAVEPOINT,可以透過 RELEASE SAVEPOINT 來進行刪除。

Session_1Session_2
從表中查詢 actor_id=201 的紀錄,結果為空
mysql> select * from actor where actor_id=201;
EMpty_set
從表中查詢 actor_id=201 的紀錄,結果為空
mysql> select * from actor where actor_id=201;
EMpty_set
開始一個新的事務,並插入一條紀錄
mysql> start transaction;
mysql> insert into actor (actor_id, name) values (201, 'Beck');
可以查到紀錄
+----------+-----------+
| actor_id | last_name |
+----------+-----------+
| 201 | Beck |
+----------+-----------+
無法查到紀錄
mysql> select * from actor where actor_id=201;
EMpty_set
定義 savepoint,名稱為 test
mysql> SAVEPOINT test

繼續插入紀錄
mysql> insert into actor (actor_id, name) values (202, 'Angle');

可以查到兩條紀錄
+----------+-----------+
| actor_id | last_name |
+----------+-----------+
| 201 | Beck |
| 202 | Angle |
+----------+-----------+
仍然無法查到紀錄
mysql> select * from actor where actor_id=201;
EMpty_set
回滾到剛才定義的 savepoint
mysql> rollback to savepoint test;
只能查到第一條紀錄
+----------+-----------+
| actor_id | last_name |
+----------+-----------+
| 201 | Beck |
+----------+-----------+
仍然無法查到紀錄
mysql> select * from actor where actor_id=201;
EMpty_set
commit 提交
mysql> commit
只能查到第一條紀錄
+----------+-----------+
| actor_id | last_name |
+----------+-----------+
| 201 | Beck |
+----------+-----------+
只能查到第一條紀錄
+----------+-----------+
| actor_id | last_name |
+----------+-----------+
| 201 | Beck |
+----------+-----------+

分散式事務的使用

Beck Yeh

熱愛學習於 Linux 與 程式設計 在網站中分享各式各樣學習到的新知識

發佈留言

這個網站採用 Akismet 服務減少垃圾留言。進一步了解 Akismet 如何處理網站訪客的留言資料