6.7 MySQL 事務(wù)與鎖定命令
昭通網(wǎng)站制作公司哪家好，找創(chuàng)新互聯(lián)！從網(wǎng)頁設(shè)計(jì)、網(wǎng)站建設(shè)、微信開發(fā)、APP開發(fā)、成都響應(yīng)式網(wǎng)站建設(shè)公司等網(wǎng)站項(xiàng)目制作，到程序開發(fā)，運(yùn)營(yíng)維護(hù)。創(chuàng)新互聯(lián)從2013年成立到現(xiàn)在10年的時(shí)間，我們擁有了豐富的建站經(jīng)驗(yàn)和運(yùn)維經(jīng)驗(yàn)，來保證我們的工作的順利進(jìn)行。專注于網(wǎng)站建設(shè)就選創(chuàng)新互聯(lián)。
6.7.1 BEGIN/COMMIT/ROLLBACK 句法
缺省的，MySQL 運(yùn)行在 autocommit 模式。這就意味著，當(dāng)你執(zhí)行完一個(gè)更新時(shí)，MySQL 將立刻將更新存儲(chǔ)到磁盤上。
如果你使用事務(wù)安全表 (例如 InnoDB、BDB)，通過下面的命令，你可以設(shè)置 MySQL 為非 autocommit 模式：
SET AUTOCOMMIT=0
在此之后，你必須使用 COMMIT 來存儲(chǔ)你的更改到磁盤上，或者使用 ROLLBACK ，如果你希望忽略從你的事務(wù)開始所做的更改。
如果你希望為一系列語句從 AUTOCOMMIT 模式轉(zhuǎn)換，你可以使用 START TRANSACTION 或 BEGIN 或 BEGIN WORK 語句：
START TRANSACTION;
SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
UPDATE table2 SET summmary=@A WHERE type=1;
COMMIT;
START TRANSACTION 在 MySQL 4.0.11 中被加入；這是被推薦的開始一個(gè)特別(ad-hoc)事務(wù)的方式，因?yàn)檫@是 ANSI SQL 句法。
注意，如果你使用的是一個(gè)非事務(wù)安全表，更改會(huì)立刻被存儲(chǔ)，不受 autocommit 模式狀態(tài)的約束。
當(dāng)你更新了一個(gè)非事務(wù)表后，如果你執(zhí)行一個(gè) ROLLBACK，你將得到一個(gè)錯(cuò)誤 (ER_WARNING_NOT_COMPLETE_ROLLBACK) 作為一個(gè)警告。所有事務(wù)安全表將被恢復(fù)，但是非事務(wù)安全表將不會(huì)改變。
如果你使用 START TRANSACTION 或 SET AUTOCOMMIT=0，你應(yīng)該使用 MySQL
二進(jìn)制日志做備份以代替老的更新日志。事務(wù)處理被以一個(gè)大塊形式存儲(chǔ)在二進(jìn)制日志中，在 COMMIT
上面，為了保護(hù)回滾的事務(wù)，而不是被存儲(chǔ)的。查看章節(jié) 4.9.4 二進(jìn)制日志。 如果您使用起動(dòng)事務(wù)處理或集AUTOCOMMIT=0
，您應(yīng)該使用MySQL 二進(jìn)制日志為備份代替更舊的更新日志。 事務(wù)處理存儲(chǔ)在二進(jìn)制登錄一大塊，做，保證, 滾的事務(wù)處理不存儲(chǔ)。 參見部分4
。9.4 二進(jìn)制日志。
下列命令自動(dòng)的結(jié)束一個(gè)事務(wù) (就好像你在執(zhí)行這個(gè)命令之前，做了一個(gè) COMMIT)：
命令 命令 命令
ALTER TABLE BEGIN CREATE INDEX
DROP DATABASE DROP TABLE RENAME TABLE
TRUNCATE
你可以使用 SET TRANSACTION ISOLATION LEVEL ... 改變事務(wù)的隔離級(jí)。查看章節(jié) 6.7.3 SET TRANSACTION 句法。
6.7.2 LOCK TABLES/UNLOCK TABLES 句法
LOCK TABLES tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}
[, tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE} ...]
...
UNLOCK TABLES
LOCK TABLES 為當(dāng)前線程鎖定表。UNLOCK TABLES 釋放當(dāng)前線程擁有的所有鎖定。當(dāng)線程發(fā)出另一個(gè) LOCK TABLES，或當(dāng)與服務(wù)器的連接被關(guān)閉時(shí)，被當(dāng)前線程鎖定的所有表將被自動(dòng)地解鎖。
為了在 MySQL 4.0.2 使用 LOCK TABLES ，你必須擁有一個(gè)全局的 LOCK TABLES 權(quán)限和一個(gè)在相關(guān)表上的
SELECT 權(quán)限。在 MySQL 3.23 中，你對(duì)該表需要有 SELECT、insert、DELETE 和 UPDATE 權(quán)限。
使用 LOCK TABLES 的主要原因是，仿效事務(wù)處理或在更新表時(shí)得到更快的速度。此后會(huì)有更詳細(xì)的描述。
如果一個(gè)線程在一個(gè)表上得到一個(gè) READ 鎖，該線程 (和所有其它線程) 只能從表中讀取。如果一個(gè)線程在一個(gè)表上得到一個(gè) WRITE 鎖，那么只有擁有這個(gè)鎖的線程可以從表中讀取和寫表。其它的線程被阻塞。
READ LOCAL 和 READ 之間的不同就在于，當(dāng)鎖被加載時(shí)，READ LOCAL 允許非沖突(non-conflicting) INSERT 語句執(zhí)行。如果當(dāng)你加載著鎖時(shí)從 MySQL 外部操作數(shù)據(jù)庫文件，這將仍不能被使用。
當(dāng)你使用 LOCK TABLES 是地，你必須鎖定所有你將使用的表，并且必須使用與你的查詢中將使用的別名相同！如果你在一個(gè)查詢中多次使用一個(gè)表(用別名)，你必須為每一個(gè)別名獲得一個(gè)鎖。
WRITE 鎖通過比 READ 鎖有更高的權(quán)限，以確保更新被盡快地處理。這就意味著，如果一個(gè)線程獲得一個(gè) READ
鎖，而同時(shí)另外一個(gè)線程請(qǐng)求一個(gè) WRITE 鎖，并發(fā)的 READ 鎖請(qǐng)求將等待直到 WRITE 線程得到了鎖并釋放了它。你可以使用
LOW_PRIORITY WRITE 鎖，當(dāng)該線程在等待 WRITE 鎖時(shí)，它將允許其它的線程獲得 READ 鎖。 你應(yīng)該只使用
LOW_PRIORITY WRITE 鎖，如果你確信這將是最后一次，當(dāng)沒有線程將擁有 READ 鎖。
LOCK TABLES 工作如下：
以內(nèi)部定義的次序排序所有被鎖定的表 (從用戶立場(chǎng)說，該次序是不明確的)。
如果一個(gè)表被以一個(gè)讀鎖和一個(gè)寫鎖鎖定，將寫鎖放在讀鎖之前。
一次只鎖定一個(gè)表，只到線程得到所有的鎖定。
這個(gè)方案是為了確保，表鎖定死鎖釋放。 對(duì)于這個(gè)模式你仍然有些其它事情需要知道：
如果你對(duì)一個(gè)表使用一個(gè) LOW_PRIORITY WRITE 鎖定，這就意味著，MySQL 將等待這個(gè)鎖，直到?jīng)]有線程請(qǐng)求一個(gè) READ
鎖。當(dāng)線程得到了 WRITE 鎖，并等待獲得鎖定表列表中的下一個(gè)表的鎖定時(shí)，其它所有的線程將等待 WRITE
鎖被釋放。如果這在你的應(yīng)用程序中會(huì)引起一個(gè)嚴(yán)重的問題，你應(yīng)該考慮將你的某些表轉(zhuǎn)換為事務(wù)安全表。
你可以使用 KILL 安全地殺死一個(gè)正在表鎖定的線程。查看章節(jié) 4.5.5 KILL 句法。
注意，你不應(yīng)該 鎖定你正在對(duì)其使用 INSERT DELAYED 的表。這是因?yàn)?，在這種情況下，INSERT 是通過單獨(dú)的線程完成的。
通常，你不需要鎖定任何表，因?yàn)樗袉?UPDATE 語句都是原子的；其它的線程無法干擾當(dāng)前執(zhí)行的 SQL 語句。當(dāng)你無論如何希望鎖定表時(shí)，這里有一些情況：
如果你在一束表上運(yùn)行許多操作，鎖定你將要使用的表，這會(huì)更快一些。當(dāng)然有不利的方面，其它線程將不能更新一個(gè) READ
鎖的表，并且沒有其它線程要以讀取一個(gè) WRITE 鎖的表。 在 LOCK TABLES 下，某些事運(yùn)行得更快一些的原因是，MySQL
將不會(huì)轉(zhuǎn)儲(chǔ)清除被鎖定表鍵高速緩沖，直到 UNLOCK TABLES 被調(diào)用 (通常鍵高速緩沖在每個(gè) SQL 語句后都會(huì)被轉(zhuǎn)儲(chǔ)清除)。這將加速在
MyISAM 表上的插入、更新、刪除。
如果你在 MySQL 中正在使用一個(gè)不支持事務(wù)的存儲(chǔ)引擎，如果你希望能確保沒有其它的線程會(huì)出現(xiàn)在一個(gè) SELECT 和 一個(gè) UPDATE 之間，你必須使用 LOCK TABLES 。下面的示例顯示為了安全地執(zhí)行，這里需要LOCK TABLES ：
mysql LOCK TABLES trans READ, customer WRITE;
mysql SELECT SUM(value) FROM trans WHERE customer_id=some_id;
mysql UPDATE customer SET total_value=sum_from_previous_statement
- WHERE customer_id=some_id;
mysql UNLOCK TABLES;
不使用 LOCK TABLES，將可能發(fā)生在 SELECT 和 UPDATE 語句執(zhí)行期間有另外一個(gè)線程可能在 trans 表中插入一行新記錄。
通過使用遞增更新 (UPDATE customer SET value=value+new_value) 或 LAST_INSERT_ID() 函數(shù)，你可以在很多情況下避免使用 LOCK TABLES。
你也可以使用用戶級(jí)鎖定函數(shù) GET_LOCK() 和 RELEASE_LOCK() 解決一些情況，這些鎖被保存在服務(wù)器上的一個(gè)哈希表中，并以
pthread_mutex_lock() 和 pthread_mutex_unlock() 實(shí)現(xiàn)以獲得高速度。查看章節(jié) 6.3.6.2
輔助功能函數(shù)。
查看章節(jié) 5.3.1 MySQL 如何鎖定表，以獲取關(guān)于鎖定方案的更多信息。
你可以使用 FLUSH TABLES WITH READ LOCK 命令以讀鎖鎖定所有數(shù)據(jù)庫中的所有表。查看章節(jié) 4.5.3 FLUSH 句法。如果你有一個(gè)可以及時(shí)建立文件快照的文件系統(tǒng)，例如 Veritas，這將是得到備份的非常方便方式。
注意：LOCK TABLES 不是事務(wù)安全的，在嘗試鎖定一個(gè)表之前，將自動(dòng)地提交所有的活動(dòng)事務(wù)。
6.7.3 SET TRANSACTION 句法
SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL
{ READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE }
設(shè)置全局的、整個(gè)會(huì)話或下一個(gè)事務(wù)的事務(wù)隔離級(jí)。
缺省行為是設(shè)置下一個(gè)(未啟動(dòng)的)事務(wù)的隔離級(jí)。如果你使用 GLOBAL
關(guān)鍵詞，語句為所有在那個(gè)點(diǎn)上建立的新連接設(shè)置默認(rèn)的全局事務(wù)隔離級(jí)。為了這樣做，你需要有 SUPER 權(quán)限。使用 SESSION
關(guān)鍵詞為當(dāng)前連接所有將來執(zhí)行的事務(wù)設(shè)置默認(rèn)的事務(wù)隔離級(jí)。
你可以使用 --transaction-isolation=... 為 mysqld 設(shè)置默認(rèn)的全局隔離級(jí)。查看章節(jié) 4.1.1 mysqld 命令行選項(xiàng)
MySQL 事務(wù)
什么是事務(wù)？
MySQL 事務(wù)主要用于處理操作量大，復(fù)雜度高的數(shù)據(jù)。比如說，在人員管理系統(tǒng)中，你刪除一個(gè)人員，你既需要?jiǎng)h除人員的基本資料，也要?jiǎng)h除和該人員相關(guān)的信息，如信箱，文章等等，這樣，這些數(shù)據(jù)庫操作語句就構(gòu)成一個(gè)事務(wù)！
在 MySQL 中只有使用了 Innodb 數(shù)據(jù)庫引擎的數(shù)據(jù)庫或表才支持事務(wù)。
事務(wù)處理可以用來維護(hù)數(shù)據(jù)庫的完整性，保證成批的 SQL 語句要么全部執(zhí)行，要么全部不執(zhí)行。
事務(wù)用來管理 insert,update,delete 語句
一般來說，事務(wù)是必須滿足4個(gè)條件（ACID）：原子性（Atomicity，或稱不可分割性）、一致性（Consistency）、隔離性（Isolation，又稱獨(dú)立性）、持久性（Durability）。
原子性：一個(gè)事務(wù)（transaction）中的所有操作，要么全部完成，要么全部不完成，不會(huì)結(jié)束在中間某個(gè)環(huán)節(jié)。事務(wù)在執(zhí)行過程中發(fā)生錯(cuò)誤，會(huì)被回滾（Rollback）到事務(wù)開始前的狀態(tài)，就像這個(gè)事務(wù)從來沒有執(zhí)行過一樣。
一致性：在事務(wù)開始之前和事務(wù)結(jié)束以后，數(shù)據(jù)庫的完整性沒有被破壞。這表示寫入的資料必須完全符合所有的預(yù)設(shè)規(guī)則，這包含資料的精確度、串聯(lián)性以及后續(xù)數(shù)據(jù)庫可以自發(fā)性地完成預(yù)定的工作。
隔離性：數(shù)據(jù)庫允許多個(gè)并發(fā)事務(wù)同時(shí)對(duì)其數(shù)據(jù)進(jìn)行讀寫和修改的能力，隔離性可以防止多個(gè)事務(wù)并發(fā)執(zhí)行時(shí)由于交叉執(zhí)行而導(dǎo)致數(shù)據(jù)的不一致。事務(wù)隔離分為不同級(jí)別，包括讀未提交（Read uncommitted）、讀提交（read committed）、可重復(fù)讀（repeatable read）和串行化（Serializable）。
持久性：事務(wù)處理結(jié)束后，對(duì)數(shù)據(jù)的修改就是永久的，即便系統(tǒng)故障也不會(huì)丟失。
在 MySQL 命令行的默認(rèn)設(shè)置下，事務(wù)都是自動(dòng)提交的，即執(zhí)行 SQL 語句后就會(huì)馬上執(zhí)行 COMMIT 操作。因此要顯式地開啟一個(gè)事務(wù)務(wù)須使用命令 BEGIN 或 START TRANSACTION，或者執(zhí)行命令 SET AUTOCOMMIT=0，用來禁止使用當(dāng)前會(huì)話的自動(dòng)提交。
from 樹懶學(xué)堂 - 一站式數(shù)據(jù)知識(shí)平臺(tái)
當(dāng)多個(gè)用戶訪問同一份數(shù)據(jù)時(shí)，一個(gè)用戶在更改數(shù)據(jù)的過程中，可能有其他用戶同時(shí)發(fā)起更改請(qǐng)求，為保證數(shù)據(jù)庫記錄的更新從一個(gè)一致性狀態(tài)變?yōu)榱硗庖粋€(gè)一致性狀態(tài)，使用事務(wù)處理是非常必要的，事務(wù)具有以下四個(gè)特性：
MySQL 提供了多種事務(wù)型存儲(chǔ)引擎，如 InnoDB 和 BDB 等，而 MyISAM 不支持事務(wù)。為了支持事務(wù)，InnoDB 存儲(chǔ)引擎引入了與事務(wù)處理相關(guān)的 REDO 日志和 UNDO 日志，同時(shí)事務(wù)依賴于 MySQL 提供的鎖機(jī)制
事務(wù)執(zhí)行時(shí)需要將執(zhí)行的事務(wù)日志寫入日志文件，對(duì)應(yīng)的文件為 REDO 日志。當(dāng)每條 SQL 進(jìn)行數(shù)據(jù)更新操作時(shí)，首先將 REDO 日志寫進(jìn)日志緩沖區(qū)。當(dāng)客戶端執(zhí)行 COMMIT 命令提交時(shí)，日志緩沖區(qū)的內(nèi)容將被刷新到磁盤，日志緩沖區(qū)的刷新方式或者時(shí)間間隔可以通過參數(shù) innodb_flush_log_at_trx_commit 控制
REDO 日志對(duì)應(yīng)磁盤上的 ib_logifleN 文件，該文件默認(rèn)為 5MB，建議設(shè)置為 512MB，以便容納較大的事務(wù)。MySQL 崩潰恢復(fù)時(shí)會(huì)重新執(zhí)行 REDO 日志的記錄，恢復(fù)最新數(shù)據(jù)，保證已提交事務(wù)的持久性
與 REDO 日志相反，UNDO 日志主要用于事務(wù)異常時(shí)的數(shù)據(jù)回滾，具體內(nèi)容就是記錄數(shù)據(jù)被修改前的信息到 UNDO 緩沖區(qū)，然后在合適的時(shí)間將內(nèi)容刷新到磁盤
假如由于系統(tǒng)錯(cuò)誤或者 rollback 操作而導(dǎo)致事務(wù)回滾，可以根據(jù) undo 日志回滾到?jīng)]修改前的狀態(tài)，保證未提交事務(wù)的原子性
與 REDO 日志不同的是，磁盤上不存在單獨(dú)的 UNDO 日志文件，所有的 UNDO 日志均存在表空間對(duì)應(yīng)的 .ibd 數(shù)據(jù)文件中，即使 MySQL 服務(wù)啟動(dòng)了獨(dú)立表空間
在 MySQL 中，可以使用 BEGIN 開始事務(wù)，使用 COMMIT 結(jié)束事務(wù)，中間可以使用 ROLLBACK 回滾事務(wù)。MySQL 通過 SET AUTOCOMMIT、START TRANSACTION、COMMIT 和 ROLLBACK 等語句支持本地事務(wù)
MySQL 定義了四種隔離級(jí)別，指定事務(wù)中哪些數(shù)據(jù)改變其他事務(wù)可見、哪些數(shù)據(jù)該表其他事務(wù)不可見。低級(jí)別的隔離級(jí)別可以支持更高的并發(fā)處理，同時(shí)占用的系統(tǒng)資源更少
InnoDB 系統(tǒng)級(jí)事務(wù)隔離級(jí)別可以使用以下語句設(shè)置：
查看系統(tǒng)級(jí)事務(wù)隔離級(jí)別：
InnoDB 會(huì)話級(jí)事務(wù)隔離級(jí)別可以使用以下語句設(shè)置：
查看會(huì)話級(jí)事務(wù)隔離級(jí)別：
在該隔離級(jí)別，所有事務(wù)都可以看到其他未提交事務(wù)的執(zhí)行結(jié)果。讀取未提交的數(shù)據(jù)稱為臟讀（Dirty Read），即是：首先開啟 A 和 B 兩個(gè)事務(wù)，在 B 事務(wù)更新但未提交之前，A 事務(wù)讀取到了更新后的數(shù)據(jù)，但由于 B 事務(wù)回滾，導(dǎo)致 A 事務(wù)出現(xiàn)了臟讀現(xiàn)象
所有事務(wù)只能看見已經(jīng)提交事務(wù)所做的改變，此級(jí)別可以解決臟讀，但也會(huì)導(dǎo)致不可重復(fù)讀（Nonrepeatable Read）：首先開啟 A 和 B 兩個(gè)事務(wù)，A事務(wù)讀取了 B 事務(wù)的數(shù)據(jù)，在 B 事務(wù)更新并提交后，A 事務(wù)又讀取到了更新后的數(shù)據(jù)，此時(shí)就出現(xiàn)了同一 A 事務(wù)中的查詢出現(xiàn)了不同的查詢結(jié)果
MySQL 默認(rèn)的事務(wù)隔離級(jí)別，能確保同一事務(wù)的多個(gè)實(shí)例在并發(fā)讀取數(shù)據(jù)時(shí)看到同樣的數(shù)據(jù)行，理論上會(huì)導(dǎo)致一個(gè)問題，幻讀（Phontom Read）。例如，第一個(gè)事務(wù)對(duì)一個(gè)表中的數(shù)據(jù)做了修改，這種修改會(huì)涉及表中的全部數(shù)據(jù)行，同時(shí)第二個(gè)事務(wù)也修改這個(gè)表中的數(shù)據(jù)，這次的修改是向表中插入一行新數(shù)據(jù)，此時(shí)就會(huì)發(fā)生操作第一個(gè)事務(wù)的用戶發(fā)現(xiàn)表中還有沒有修改的數(shù)據(jù)行
InnoDB 通過多版本并發(fā)控制機(jī)制（MVCC）解決了該問題：InnoDB 通過為每個(gè)數(shù)據(jù)行增加兩個(gè)隱含值的方式來實(shí)現(xiàn)，這兩個(gè)隱含值記錄了行的創(chuàng)建時(shí)間、過期時(shí)間以及每一行存儲(chǔ)時(shí)間發(fā)生時(shí)的系統(tǒng)版本號(hào)，每個(gè)查詢根據(jù)事務(wù)的版本號(hào)來查詢結(jié)果
通過強(qiáng)制事務(wù)排序，使其不可能相互沖突，從而解決幻讀問題。簡(jiǎn)而言之，就是在每個(gè)讀的數(shù)據(jù)行上加上共享鎖實(shí)現(xiàn)，這個(gè)級(jí)別會(huì)導(dǎo)致大量的超時(shí)現(xiàn)象和鎖競(jìng)爭(zhēng)，一般不推薦使用
為了解決數(shù)據(jù)庫并發(fā)控制問題，如走到同一時(shí)刻客戶端對(duì)同一張表做更新或者查詢操作，需要對(duì)并發(fā)操作進(jìn)行控制，因此產(chǎn)生了鎖
共享鎖的粒度是行或者元組（多個(gè)行），一個(gè)事務(wù)獲取了共享鎖以后，可以對(duì)鎖定范圍內(nèi)的數(shù)據(jù)執(zhí)行讀操作
排他鎖的粒度與共享鎖相同，一個(gè)事務(wù)獲取排他鎖以后，可以對(duì)鎖定范圍內(nèi)的數(shù)據(jù)執(zhí)行寫操作
有兩個(gè)事務(wù) A 和 B，如果事務(wù) A 獲取了一個(gè)元組的共享鎖，事務(wù) B 還可以立即獲取這個(gè)元組的共享鎖，但不能獲取這個(gè)元組的排他鎖，必須等到事務(wù) A 釋放共享鎖之后。如果事務(wù) A 獲取了一個(gè)元組的排他鎖，事務(wù) B 不能立即獲取這個(gè)元組的共享鎖，也不能立即獲取這個(gè)元組的排他鎖，必須等到 A 釋放排他鎖之后
意向鎖是一種表鎖，鎖定的粒度是整張表，分為意向共享鎖和意向排他鎖。意向共享鎖表示一個(gè)事務(wù)有意對(duì)數(shù)據(jù)上共享鎖或者排他鎖。有意表示事務(wù)想執(zhí)行操作但還沒真正執(zhí)行
鎖的粒度主要分為表鎖和行鎖
表鎖的開銷最小，同時(shí)允許的并發(fā)量也是最小。MyISAM 存儲(chǔ)引擎使用該鎖機(jī)制。當(dāng)要寫入數(shù)據(jù)時(shí)，整個(gè)表記錄被鎖，此時(shí)其他讀/寫動(dòng)作一律等待。一些特定的動(dòng)作，如 ALTER TABLE 執(zhí)行時(shí)使用的也是表鎖
行鎖可以支持最大的并發(fā)，InnoDB 存儲(chǔ)引擎使用該鎖機(jī)制。如果要支持并發(fā)讀/寫，建議采用 InnoDB 存儲(chǔ)引擎
重慶分公司
重慶分公司
