CK2 模組開發網 - 藏經閣 - 資料庫的鎖定與交易

摘要:網站有預約與修改資料的功能，是重要的、也是必要，而我對於鎖定資料庫，卻是沒有一點概念。
後來請問了谷歌大神，找到這位仁兄，寫的『似乎』（*）非常詳細，但我還是有看沒有懂，所以特別分享到這裡，希望大家能共同研究一下囉！
*註：『似乎』是形容我的認知，而非作者。

標題

設定系統變數concurrent_insert
MySQL文件中的範例
鎖定函數範例
文字框
ROLLBACK範例

鎖定與交易

記得高鐵剛開始營運的時候，曾經發生一個嚴重的系統事件，那就是賣出相同座位號碼的車票，這是一個笑掉程式界大牙的一個嚴重事件，高鐵這麼一個大型的系統，怎麼會出現這樣的問題呢，這不是一個最基本的程式設計師都應該知道的常識嗎?那就是資料的鎖定與交易的問題。
所謂的鎖定與交易，是指當某個資料同時由不同來源進行修改時，應只有一方才能進行修改，而另外一方必須拒絕存取才對，就像車票一樣，原本兩地的人，都看到了同一個座位號碼的車票，結果定下車票時，應該只有一個人可以買到這張車票，而另外一個人則應該由系統提醒此車票已經賣出，才不至於發生兩個人都買到同一個座位的情況。因此，假設，有一張資料表A，正在被u1使用者存取，那麼，u2使用者則應該暫時無法存取該資料表，必須等待u1使用者存取完成，才能提供 u2使用者進行存取，這樣的動作稱為鎖定或交易。

交易只有InnoDB資料庫引擎才能使用，而MyISAM這個MySQL預設的資料庫引擎則只能使用鎖定的動作來達成。

鎖定

鎖定的運作模式：(點圖放大)

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

鎖定語法使用LOCK TABLES，後方加上要鎖定的資料表名稱tbl_name，而一次可以對多個資料表進行鎖定，通常鎖定的處理，必須一次將所有操作的資料表全部鎖定，並於使用完畢之後立即解開鎖定，鎖定資料表的操作如有使用到別名，則在鎖定定的同時，必須將別名也進行鎖定；鎖定的模式分為四種，READ、READ LOCAL、WRITE、LOW_PRIORITY四種模式分別代表：

READ：表示所有程序，包括目前下達指令的程序都只能讀取資料表內容

READ LOCAL：與READ雷同，但允許其他程序在不更動到目前資料的狀態下使用INSERT命令插入新的資料。要注意的是，所謂不更動目前資料的狀態，是指：目前資料表沒有因為刪除資料而產生的空洞，導致新的資料寫入時，填補這些空洞，而產生目前資料的順序有所更動，如果會將資料填入這些空洞，這樣READ LOCAL就無法允許這樣的資料寫入，此項目與系統變數concurrent_insert有關，此變數值如果為0，表示不允許在表尾寫入資料，如果為 1(預設)，允許表中沒有空洞時，一個程序正在讀取資料的狀況下，另一個程序可以在表尾寫入資料，如果為2，則表示，不管有沒有空洞，接允許在表尾寫入資料。

設定系統變數concurrent_insert

mysql>SET GLOBAL concurrent_insert=2;

WRITE：將目前資料表允許讓目前的程序寫入、修改、刪除，但其他程序則無法對其進行任何操作。

LOW_PRIORITY WRITE：與WRITE雷同，差異在於，當資料表被另一個程序鎖定時，目前程序必須等待另一個程序完成解除鎖定動作，如果另一個程序解除鎖定，並重新加上新的鎖定程序，則目前程序會繼續等待另一個程序再次解除鎖定，也就是說，以較低的優先權進行等待的動作，這會延長目前程序的等待時間。

幾個MySQL文件中的範例：

MySQL文件中的範例

mysql> LOCK TABLE t WRITE, t AS t1 WRITE;
mysql> INSERT INTO t SELECT * FROM t;
ERROR 1100: Table 't' was not locked with LOCK TABLES
mysql> INSERT INTO t SELECT * FROM t AS t1;

mysql> LOCK TABLE t READ;
mysql> SELECT * FROM t AS myalias;
ERROR 1100: Table 'myalias' was not locked with LOCK TABLES

mysql> LOCK TABLE t AS myalias READ;
mysql> SELECT * FROM t;
ERROR 1100: Table 't' was not locked with LOCK TABLES
mysql> SELECT * FROM t AS myalias;

解除鎖定

語法：
UNLOCK TABLES;

使用方法很簡單，下達上方命令，將會解除所有目前程序鎖定的資料表，請記得在處理鎖定之後，馬上解除鎖定。

鎖定操作的注意事項：

單一處理指令行，並不會被其他的程序給中斷，而導致資料出現不同步的情況，因此鎖定不需要針對單一指令行而進行
當多行指令處理單一或多資料表時，可能會在其中一個指令執行完成時，被其他指令給佔用正在處理的資料，而導致資料不同步，處理多行指令時，必須鎖定資料表
InnoDB不應該使用LOCK與UNLOCK指令，而應改用交易方式處理資料不同步的問題；主要原因是因為舊版MySQL可能會導致鎖定時，中斷目前正在執行的交易程序，而導致資料發生錯誤。
READ LOCAL的鎖定方式，另一個程序可以寫入不影響目前資料的新資料的條件：
- concurrent_insert=1，且資料沒有空洞
- concurrent_insert=2
- 在READ LOCAL被設定之後才出現此程序，如果再READ LOCAL產生之前，此程序已經產生了，那麼資料表被另一個程序給READ LOCAL時，同等於READ，因此無法寫入任何資料

鎖定函數

GET_LOCK('str',timeout)
RELEASE_LOCK('str')

此兩個函數分別代表鎖定與釋放鎖定，這兩個函數實際上並非鎖定資料表，而是設定一個鎖定名稱str，將這名稱設定一個timeout時間，單位為秒，表示如果要鎖定此str的鎖定名稱，等待超過指定秒數，則不在等待，如果要真正取得該鎖定名稱的鎖定，必須由另一個正在使用的程序進行釋放，目前程序才能進行鎖定

回傳值：

目前程序鎖定呼叫GET_LOCK('str',timeout)，傳回1
目前程序鎖定呼叫RELEASE_LOCK('str')，傳回1，並進行釋放，再次呼叫，則傳回NULL(因為已經被釋放)
另一個程序鎖定此名稱，呼叫GET_LOCK('str',timeout)，等待timeout時間後，傳回0
另一個程序鎖定此名稱，呼叫RELEASE_LOCK('str')，傳回0
另一個程序已經是放，呼叫RELEASE_LOCK('str')，傳回NULL

使用方法：

在操作時使用GET_LOCK，當作條件，如果有人鎖定此名稱，timeout之後傳回0，則目前操作不執行，如果取得1，則執行目前操作，並進行鎖定；也可以反向檢察RELEASE_LOCK('str')是否為NULL，如果為NULL，則允續寫入或更改。
取得鎖定之後，操作完畢必須進行釋放，釋放方式，不需另外執行操作，只需要使用DO指令呼叫函數即可
ex:DO RELEASE_LOCK('str');

鎖定函數範例

mysql>UPDATE t1 set name='test' WHERE id=20 and GET_LOCK('str',1)=1;

注意：使用此種方式，每個會影響到資料的修改的語法，皆必須採用此兩種函數判斷是否可以修改，如果沒有全部採用此種方式，那也有可能導致資料不同步的情況

交易

交易在MySQL的資料庫引擎中只有Innodb支援，MyISAM只能採用LOCK TABLE方式，書籍上有提到ACID的規則理論，Innodb的交易支援此規則：

Atomicity(原子性)：
不可分割，交易過程的指令必須全部完成，或者全部不執行，即使遇到當機，也必須維持這兩種情況。

Consistency(一致性)：
交易的進行在結束後，資料庫的內容必須保持一致，如果交易過程發生牴觸限制規則(像是值域不符、外鍵不符等)，則交易必須終止，並回朔到沒有交易之前的情況(ROLLBACK)

Isolation(獨立性)：
每個交易都是獨立執行，且可以同時進行，互相不干擾彼此的運作，但要維持資料的一致並且交易個別獨立，效能會大大的降低，因此在ANSI-92/SQL標準規範中規定了四種層級，Innodb預設為REPEATABLE READ

Durability(耐用性)：
交易結果必須正確保留，交易過程如果發生當機，那麼資料必須正確的保留下來，等到MySQL server重新啟動，再回寫資料庫中

語法：

START TRANSACTION [WITH CONSISTENT SNAPSHOT] | BEGIN [WORK]
COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
SET autocommit = {0 | 1}

在交易上語法有四種，並非分開使用，這些語法是配合使用的

交易流程：啟動交易->執行資料處理->結束交易
交易過程必須遵守ACID原則

文字框

USE test_t
INSERT INTO test_trans VALUES(1,10);
SELECT * FROM test_trans;
START TRANSACTION
UPDATE test_trans SET colB=11 WHERE colA=1;
SELECT * FROM test_trans;
COMMIT
SELECT * FROM test_trans;

START TRANSACTION 用來啟動交易機制，而COMMIT用來寫入交易結果並結束交易，在交易期間，更新資料colB變更為11，在交易過程查詢是否變更，會查到colB為11；在此時如果有另外一個程序查詢colA=1的這筆資料，會得到colB為 10，而不是11，原因是交易尚未結束，因此資料還沒進行變更，必須執行到COMMIT時，資料才會變更。

交易中有使用到的資料，其他的程序無法進行變更，如果其他程序要更動該資料，那麼會自動進入等帶狀態，直到交易結束，才能進行。

START TRANSACTION後方可以加上WITH CONSISTENT SNAPSHOT語句，主要目的在於對整個資料庫中的innodb引擎的資料表，拍下一個快照，交易中使用該快照進行查詢；因此，某些資料表可能因為其他程序進行修改，但在交易中查到的資料會是交易開始時，所得到的快照值。

另外在交易開始時，如果尚未使用到更動資料的語法前，另一個程序還是可以對資料做任意的更動的(包含新增、修改、刪除三種動作)，但如果交易的程序開始更動資料表中的資料時，那這些動作就必須進入等待的狀態

對於交易過程中修改後的資料，不同於另一個行程查詢的資料(上方有提到)，如要讓查詢的資料也可以與交易過程的資料同步，這樣可以在SELECT語法的結尾加上LOCK IN SHARE MODE語句，這語句是用於交易過程之外的程序，這會讓查詢的動作必須等待交易完成才取得結果。如此便能讓查詢的結果能夠同步

獨佔鎖定，這是在交易中使用的語句，在SELECT語法結尾處加上FOR UPDATE，上面提到，當交易過程中，如果只是查詢動作，其他程序仍然可以修改資料，但如果查詢動作是用來更新資料用，那麼勢必會導致查詢出來的資料可能被另一個程序給修改了，因此在交易過程中如果要防止這樣的狀況發生，可以在查詢後方加上FOR UPDATE，被查出的所有資料將會被鎖定，這樣的鎖定方式稱為獨佔鎖定。在交易過程中INSERT UPDATE 與DELETE語法將會讓資料直接進入獨佔鎖定狀態，如上方所說明的狀況，因此另一個程序無法寫入、刪除或修改資料。

關於COMMIT與ROLLBACK的差異，COMMIT是允許寫入並結束交易，而ROLLBACK則是取消交易並結束交易，而後方可以使用特殊的語法AND CHAIN，也可以使用AND RELEASE，在CHAIN與RELEASE前方可以加上NO，代表不使用；預設上，如果不指定AND項目，那就代表AND NO CHAIN NO RELEASE，AND CHAIN的意思是指，交易結束，並繼續另一個交易，而不需重新啟動交易，AND RELEASE則是交易結束，並中斷目前連線

ROLLBACK範例

BEGIN WORK;
UPDATE test_inno2 SET colB=222 WHERE colA=2;
ROLLBACK AND CHAIN;
--接下來可以直接執行另一個交易
UPDATE test_inno2 SET colB=100 WHERE colA=3;
ROLLBACK AND RELEASE;
--結束交易並中斷連線

交易獨立層級：

SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL
{ READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE }

SET後方GLOBAL與SESSION可以省略，當省略時，只會影響到下次交易；而指定GLOBAL是指定此設定影響所有新連線，但不影響目前目前連線；SESSION則是設定影響目前連線

四種交易獨立等級：

READ UNCOMMITTED：對於交易過程更新的資料，在交易外進行SELECT查詢時，會查詢到交易中已經修改後的資料，但仍然無法對交易使用中的資料進行修改。

READ COMMITTED：SELECT語法只會取得COMMIT之後的結果，也就是尚未確認寫入的結果是無法讀取到的，這要小心使用，就是在交易過程中，如果有兩個SELECT查詢，一個SELECT遇到另外一個交易COMMIT，這樣會導致兩個SELECT查出的結果不同。

REPEATABLE READ：交易中的SELECT不會考慮其他交易的修改動作，同一交易內所查到的資料會相同，也就是一個交易流程中SELECT所查到的資料會是相同的資料，交易本身與其他交易進行隔絕。

SERIALIZABLE ：與REPEATABLE READ相同，但差異在於SELECT語法會自動採用LOCK IN SHARE MODE，也就是交易外的程序必須等待交易結束之後才能取用交易所使用到的資料