MySQL詳解(14)----------交易處理,mysql----------

來源:互聯網
上載者:User

MySQL詳解(14)----------交易處理,mysql----------

前言:前一篇文章關於交易處理的博文沒有寫清楚,讀起來很晦澀,很難理解,所以有整理了一些資料,協助理解,見諒!

 

關於MySQL交易處理學習記

START TRANSACTION

COMMIT

ROLLBACK

文法

 START TRANSACTION    | 

BEGIN [WORK]     

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

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

SET AUTOCOMMIT = {0 | 1}

 

START TRANSACTION或BEGIN語句可以開始一項新的事務。

 

COMMIT可以提交當前事務,是變更成為永久變更。

 

ROLLBACK可以 復原當前事務,取消其變更。

 

SET AUTOCOMMIT語句可以禁用或啟用預設的autocommit模式,用於當前串連。

自選的WORK關鍵詞被支援,用於COMMIT和RELEASE,與CHAIN和RELEASE子句。

 

CHAIN和RELEASE可以被用於對事務完成進行附加控制。

 

Completion_type系統變數的值決定了預設完成的性質。

 

AND CHAIN子句會在當前事務結束時,立刻啟動一個新事務,並且新事務與剛結束的事務有相同的隔離等級。

 

RELEASE子句在終止了當前事務後,會讓伺服器斷開與當前用戶端的串連。包含NO關鍵詞可以抑制CHAIN或RELEASE完成。

如果completion_type系統變數被設定為一定的值,使連鎖或釋放完成可以預設進行,此時NO關鍵詞有用。

 

預設情況下,MySQL採用autocommit模式運行。這意味著,當您執行一個用於更新(修改)表的語句之後,MySQL立刻把更新儲存到磁碟中。

 

如果您正在使用一個事務安全型的儲存引擎(如InnoDB, BDB或NDB簇),則您可以使用以下語句禁用autocommit模式:

SET AUTOCOMMIT=0; 通過把AUTOCOMMIT變數設定為零,禁用autocommit模式之後,您必須使用COMMIT把變更儲存到磁碟中,或著如果您想要忽略從事務開始進行以來做出的變更,使用ROLLBACK。

 

如果您想要對於一個單一系列的語句禁用autocommit模式,則您可以使用START TRANSACTION語句:

 

START TRANSACTION;

SELECT @A:=SUM(salary) FROM table1 WHERE type=1;

UPDATE table2 SET summary=@A WHERE type=1; COMMIT;

使用START TRANSACTION,autocommit仍然被禁用,直到您使用COMMIT或ROLLBACK結束事務為止。

然後autocommit模式恢複到原來的狀態。

 

BEGIN和BEGIN WORK被作為START TRANSACTION的別名受到支援,用於對事務進行初始化。

START TRANSACTION是標準的SQL文法,並且是啟動一個ad-hoc事務的推薦方法。

BEGIN語句與BEGIN關鍵詞的使用不同。BEGIN關鍵詞可以啟動一個BEGIN...END複合陳述式。後者不會開始一項事務。

 

您也可以按照如下方法開始一項事務:

START TRANSACTION WITH CONSISTENT SNAPSHOT;

WITH CONSISTENT SNAPSHOT子句用於啟動一個一致的讀取,用於具有此類功能的儲存引擎。

 

目前,該子句只適用於InnoDB。該子句的效果與發布一個START TRANSACTION,後面跟一個來自任何InnoDB表的SELECT的效果一樣。

 

開始一項事務會造成一個隱含的UNLOCK TABLES被執行。

為了獲得最好的結果,事務應只使用由單一事務儲存引擎管理的表執行。否則,會出現以下問題:

 

如果您使用的表來自多個事務安全型儲存引擎(例如InnoDB和BDB),並且事務隔離等級不是SERIALIZABLE,則有可能當一個事務提交時,其它進行中中的、使用同樣的表的事務將只會發生由第一個事務產生的變更。

也就是,用混合引擎不能保證事務的原子性,並會造成不一致。(如果混合引擎事務不經常有,則您可以根據需要使用

SET TRANSACTION ISOLATION LEVEL把隔離等級設定到SERIALIZABLE。)

 

如果您在事務中使用非事務安全型表,則對這些表的任何變更被立刻儲存,不論autocommit模式的狀態如何。

如果您在更新了事務中一個事務表之後,發布一個ROLLBACK語句,則會出現一個ER_WARNING_NOT_COMPLETE_ROLLBACK警告。

對事務安全型表的變更被 復原,但是對非事務安全型表沒有變更。

每個事務被儲存在一個組塊中的二進位日誌中,在COMMIT之上。被復原的事務不被計入日誌。(例外情況:對非事務表的更改不會被 復原。如果一個被復原的事務包括對非事務表的更改,則整個事務使用一個在末端的ROLLBACK語句計入日誌,以確保對這些表的更改進行複製。)

 

您可以使用SET TRANSACTION ISOLATION LEVEL更改事務的隔離等級。

 

復原可以慢速運行。在使用者沒有明確要求時,也可以進行復原(例如,當錯誤發生時)。因此,在明確地和隱含的(ROLLBACK SQL命令)復原時,SHOW PROCESSLIST會在Stage列中顯示Rolling back,用於串連。

 


    交易處理和並發性

1.1.        基礎知識和相關概念

1 )全部的表類型都可以使用鎖,但是只有 InnoDB 和 BDB 才有內建的事務功能。

2 )使用 begin 開始事務,使用 commit 結束事務,中間可以使用 rollback 復原事務。

3 )在預設情況下, InnoDB 表支援一致讀。

SQL 標準中定義了 4 個隔離等級: read uncommited , read commited , repeatable read , serializable 。

read uncommited 即髒讀,一個事務修改了一行,另一個事務也可以讀到該行。

如果第一個事務執行了復原,那麼第二個事務讀取的就是從來沒有正式出現過的值。 ?

read commited 即一致讀,試圖通過唯讀取提交的值的方式來解決髒讀的問題,但是這又引起了不可重複讀取的問題。

一個事務執行一個查詢,讀取了大量的資料行。在它結束讀取之前,另一個事務可能完成了對資料行的更改。當第一個事務試圖再次執行同一個查詢,伺服器就會返回不同的結果。

repeatable read 即可重複讀,在一個事務對資料行執行讀取或寫入操作時鎖定了這些資料行。

但是這種方式又引發了幻想讀的問題。

因為只能鎖定讀取或寫入的行,不能阻止另一個事務插入資料,後期執行同樣的查詢會產生更多的結果。

serializable 模式中,事務被強製為依次執行。這是 SQL 標準建議的預設行為。

4 )如果多個事務更新了同一行,就可以通過復原其中一個事務來解除死結。

5 ) MySQL 允許利用 set transaction 來設定隔離等級。

6 )事務只用於 insert 和 update 語句來更新資料表,不能用於對錶結構的更改。執行一條更改表結構或 begin 則會立即提交當前的事務。

7 )所有表類型都支援表級鎖,但是 MyISAM 只支援表級鎖。

8 )有兩種類型的表級鎖:讀鎖和寫鎖。

讀鎖是共用鎖定,支援並發讀,寫操作被鎖。

寫鎖是獨佔鎖,上鎖期間其他線程不能讀表或寫表。

8 )如果要支援並發讀寫,建議採用 InnoDB 表,因為它是採用行級鎖,可以獲得更多的更新效能。

9 )很多時候,可以通過經驗來評估什麼樣的鎖對應用程式更合適,不過通常很難說一個鎖比別的更好,這全都要依據應用程式來決定,不同的地方可能需要不同的鎖。當前 MySQL 已經支援 ISAM, MyISAM, MEMORY (HEAP) 類型表的表級鎖了, BDB 表支援頁級鎖, InnoDB 表支援行級鎖。

10 ) MySQL 的表級鎖都是寫鎖優先,而且是採用排隊機制,這樣不會出現死結的情況。對於 InnoDB 和 BDB 儲存引擎來說,是可能產生死結的。這是因為 InnoDB 會自動捕獲行鎖, BDB 會在執行 SQL 陳述式時捕獲頁鎖的,而不是在事務的開始就這麼做。

1.2.        不同鎖的優缺點及選擇

行級鎖的優點及選擇 :

1 )在很多線程請求不同記錄時減少衝突鎖。

2 )交易回復時減少改變資料。

3 )使長時間對單獨的一行記錄加鎖成為可能。

行級鎖的缺點 :

1 )比頁級鎖和表級鎖消耗更多的記憶體。

2 )當在大量表中使用時,比頁級鎖和表級鎖更慢,因為他需要請求更多的所資源。

3 )當需要頻繁對大部分資料做 GROUP BY 操作或者需要頻繁掃描整個表時,就明顯的比其它鎖更糟糕。

4 )使用更高層的鎖的話,就能更方便的支援各種不同的類型應用程式,因為這種鎖的開銷比行級鎖小多了。

5 )可以用應用程式級鎖來代替行級鎖,例如 MySQL 中的 GET_LOCK() 和 RELEASE_LOCK() 。但它們是勸告鎖(原文: These are advisory locks ),因此只能用於安全可信的應用程式中。

6 )對於 InnoDB 和 BDB 表, MySQL 只有在指定用 LOCK TABLES 鎖表時才使用表級鎖。在這兩種表中,建議最好不要使用 LOCK TABLES ,因為 InnoDB 自動採用行級鎖, BDB 用頁級鎖來保證事務的隔離。

表鎖的優點及選擇:

1 )很多操作都是讀表。

2 )在嚴格條件的索引上讀取和更新,當更新或者刪除可以用單獨的索引來讀取得到時: UPDATE tbl_name SET column=value WHERE unique_key_col=key_value;DELETE FROM tbl_name WHERE unique_key_col=key_value;

3 ) SELECT 和 INSERT 語句並發的執行,但是只有很少的 UPDATE 和 DELETE 語句。

4 )很多的掃描表和對全表的 GROUP BY 操作,但是沒有任何寫表。

表鎖的缺點:

1 )一個用戶端提交了一個需要長時間啟動並執行 SELECT 操作。

2 )其他用戶端對同一個表提交了 UPDATE 操作,這個用戶端就要等到 SELECT 完成了才能開始執行。

3 )其他用戶端也對同一個表提交了 SELECT 請求。由於 UPDATE 的優先順序高於 SELECT ,所以 SELECT 就會先等到 UPDATE 完成了之後才開始執行,它也在等待第一個 SELECT 操作。

1.3.        如何避免鎖的資源競爭

1 )讓 SELECT 速度盡量快,這可能需要建立一些摘要表。

2 )啟動 mysqld 時使用參數 --low-priority-updates 。這就會讓更新操作的優先順序低於 SELECT 。

這種情況下,在上面的假設中,第二個 SELECT 就會在 INSERT 之前執行了,而且也無需等待第一個 SELECT 了。

3 )可以執行 SET LOW_PRIORITY_UPDATES=1 命令,指定所有的更新操作都放到一個指定的連結中去完成。

4 )用 LOW_PRIORITY 屬性來降低 INSERT , UPDATE , DELETE 的優先順序。

5 )用 HIGH_PRIORITY 來提高 SELECT 語句的優先順序。

6 )從 MySQL 3.23.7 開始,可以在啟動 mysqld 時指定系統變數 max_write_lock_count 為一個比較低的值,它能強制臨時地提高表的插入數達到一個特定值後的所有 SELECT 操作的優先順序。它允許在 WRITE 鎖達到一定數量後有 READ 鎖。

7 )當 INSERT 和 SELECT 一起使用出現問題時,可以轉而採用 MyISAM 表,它支援並發的 SELECT 和 INSERT 操作。

8 )當在同一個表上同時有插入和刪除操作時, INSERT DELAYED 可能會很有用。

9 )當 SELECT 和 DELETE 一起使用出現問題時, DELETE 的 LIMIT 參數可能會很有用。

10 )執行 SELECT 時使用 SQL_BUFFER_RESULT 有助於減短鎖表的期間。

11 )可以修改原始碼 `mysys/thr_lock.c' ,只用一個所隊列。這種情況下,寫鎖和讀鎖的優先順序就一樣了,這對一些應用可能有協助。

 

 

著作權聲明:歡迎轉轉載,希望轉載的同時添加原文地址,謝謝合作,學習快樂!

相關文章

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。

Tags Index: