本文實(shí)例講述了mysql的事務(wù)，隔離級(jí)別和鎖用法。分享給大家供大家參考，具體如下：

事務(wù)就是一組一起成功或一起失敗的sql語(yǔ)句。事務(wù)還應(yīng)該具備，原子性，一致性，隔離性和持久性。

一、事務(wù)的基本要素 (ACID)

1、原子性：事務(wù)開(kāi)始后，所有的操作，要么全部成功，要么全部失敗，不可能處于中間狀態(tài)，事務(wù)是一個(gè)不可分割的整體，就像原子一樣。

2、一致性：事務(wù)開(kāi)始前和結(jié)束后，數(shù)據(jù)庫(kù)的完整性約束沒(méi)有破壞，A向B轉(zhuǎn)賬，A扣了錢(qián)，但B卻沒(méi)到賬。

3、隔離性：同時(shí)發(fā)生的事務(wù)(并發(fā)事務(wù))不應(yīng)該導(dǎo)致數(shù)據(jù)庫(kù)處于不一致的狀態(tài)中，每個(gè)事務(wù)都獨(dú)立執(zhí)行，不影響其他事務(wù)的存在。

4、持久性：事務(wù)對(duì)數(shù)據(jù)庫(kù)的更改都會(huì)保存在磁盤(pán)上，不會(huì)丟失。

二、事務(wù)的并發(fā)問(wèn)題

1、臟讀：事務(wù)A讀取了事務(wù)B未提交的寫(xiě)入數(shù)據(jù)，讀取到的數(shù)據(jù)就稱(chēng)為臟數(shù)據(jù)

2、不可重復(fù)讀：事務(wù)A多次讀取同一數(shù)據(jù)，但在讀取過(guò)程中，事務(wù)B對(duì)數(shù)據(jù)做了修改，并提交了。導(dǎo)致多次讀取同一數(shù)據(jù)，結(jié)果不一樣。

3、幻讀：事務(wù)A對(duì)表中所有數(shù)據(jù)行進(jìn)行了修改，比如設(shè)置status = 1，但同時(shí)，事務(wù)B往該表插入了一行新數(shù)據(jù)status = 0，對(duì)于操作事務(wù)A的用戶(hù)而言，表中還有一條記錄沒(méi)被修改，就像發(fā)生幻覺(jué)一樣。

三、事務(wù)隔離性的四個(gè)級(jí)別

四、獲取和設(shè)置數(shù)據(jù)庫(kù)隔離級(jí)別

SHOW VARIABLES LIKE '%isolation%';
SHOW GLOBAL VARIABLES LIKE '%isolation%';

使用系統(tǒng)變量查詢(xún)

SELECT @@GLOBAL.tx_isolation;
SELECT @@SESSION.tx_isolation;
SELECT @@tx_isolation;

對(duì)于mysql8而言，使用下面的變量進(jìn)行查詢(xún)

SELECT @@GLOBAL.transaction_isolation;
SELECT @@SESSION.transaction_isolation;
SELECT @@transaction_isolation;

設(shè)置隔離級(jí)別

SET GLOBAL tx_isolation = '隔離級(jí)別';
SET SESSION tx_isolation = '隔離級(jí)別';
SET @@tx_isolation = '隔離級(jí)別';

對(duì)于mysql8而言，使用下面語(yǔ)句進(jìn)行設(shè)置

SET GLOBAL transaction_isolation = '隔離級(jí)別';
SET SESSION transaction_isolation = '隔離級(jí)別';
SET @@transaction_isolation = '隔離級(jí)別';

五、通過(guò)例子說(shuō)明各隔離級(jí)別的情況

先準(zhǔn)備一張表，和一點(diǎn)數(shù)據(jù)。

CREATE TABLE `account` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
 `name` varchar(32) DEFAULT '' COMMENT '名稱(chēng)',
 `money` decimal(11,2) DEFAULT '0.00' COMMENT '金錢(qián)',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `account` (`name`, `money`) VALUES ('A', '500.00');
INSERT INTO `account` (`name`, `money`) VALUES ('B', '100.00');

1、讀取未提交

set transaction_isolation = 'READ-UNCOMMITTED';
set autocommit = 0;

事務(wù)B修改了表中的數(shù)據(jù)，但是未提交，事務(wù)A確讀取到了修改后的數(shù)據(jù)。如果因?yàn)槟承┰?，事?wù)B回滾了，事務(wù)A讀取的這個(gè)數(shù)據(jù)就是臟數(shù)據(jù)。

2、讀已提交

set transaction_isolation = 'READ-COMMITTED';
set autocommit = 0;

事務(wù)B修改數(shù)據(jù)但沒(méi)有提交，那么事務(wù)A仍然獲取的原來(lái)數(shù)據(jù)，解決了臟讀的問(wèn)題。

但是事務(wù)B提交，事務(wù)A執(zhí)行上一次查詢(xún)，結(jié)果與上一次查詢(xún)不一致，這就產(chǎn)生不可重復(fù)讀的問(wèn)題。

3、可重復(fù)讀取

set transaction_isolation = 'REPEATABLE-READ';
set autocommit = 0;

事務(wù)B修改了數(shù)據(jù)并提交了，事務(wù)A兩次查詢(xún)的結(jié)果是一致的，解決了不可重復(fù)讀的問(wèn)題。

這個(gè)時(shí)候，事務(wù)A去修改name為A的money數(shù)據(jù)

name為A的money變成了350，而不是400，可重復(fù)讀保證了數(shù)據(jù)的一致性。

我們重新在事務(wù)A中修改所有賬號(hào)的money等于200，同時(shí)在事務(wù)B中插入一條新的數(shù)據(jù)。

事務(wù)A中獲取的仍然是兩條數(shù)據(jù)，解決了新增數(shù)據(jù)時(shí)，事務(wù)A出現(xiàn)的幻讀問(wèn)題。

4、序列化

set transaction_isolation = 'SERIALIZABLE';
set autocommit = 0;

事務(wù)A對(duì)表進(jìn)行查詢(xún)，如果沒(méi)有提交，則事務(wù)B的插入語(yǔ)句一直等待在那里，直到超時(shí)或事務(wù)A提交。

反之，事務(wù)B對(duì)表進(jìn)行插入后，沒(méi)有提交，則事務(wù)A對(duì)表的查詢(xún)也一直等待，直到事務(wù)B提交。

此時(shí)對(duì)表的讀寫(xiě)都會(huì)進(jìn)行鎖表，當(dāng)然對(duì)并發(fā)性能的影響也比較大。

隔離級(jí)別越高，越能保證數(shù)據(jù)的完整性和一致性。

六、mysql的鎖

鎖分為兩種類(lèi)型：

內(nèi)部鎖：mysql服務(wù)器內(nèi)部執(zhí)行的內(nèi)部鎖，以管理多個(gè)會(huì)話(huà)對(duì)表內(nèi)容的爭(zhēng)用。

外部鎖：mysql為客戶(hù)會(huì)話(huà)提供顯式地獲取表鎖，以阻止其他會(huì)話(huà)訪(fǎng)問(wèn)表。

內(nèi)部鎖又會(huì)為兩種類(lèi)型：

1、行級(jí)鎖：行級(jí)鎖是細(xì)粒度的，只有被訪(fǎng)問(wèn)的行會(huì)被鎖定，這允許多個(gè)會(huì)話(huà)同時(shí)進(jìn)行寫(xiě)訪(fǎng)問(wèn)。

2、表級(jí)鎖：mysql對(duì)myisam，memory和merge表使用表級(jí)鎖，一次只允許一個(gè)會(huì)話(huà)更新表，這使得這些存儲(chǔ)引擎更適用于以讀取為主的操作。

外部鎖：可以使用 LOCK TABLE 和 UNLOCK TABLE 來(lái)控制鎖定。

READ (共享鎖) ：多個(gè)會(huì)話(huà)可以從表中讀取數(shù)據(jù)而不需要獲取鎖，此外，多個(gè)會(huì)話(huà)可以在同一表上獲得鎖，當(dāng) READ 鎖時(shí)，沒(méi)有會(huì)話(huà)可以將數(shù)據(jù)寫(xiě)入表中。任何寫(xiě)入操作都將處于等待狀態(tài)，直到 READ 鎖被釋放。

WRITE (排他鎖) ：當(dāng)表被 WRITE 鎖定時(shí)，除持有該鎖的會(huì)話(huà)外，其他會(huì)話(huà)都不能讀取或?qū)懭霐?shù)據(jù)，除非 WRITE 鎖被釋放。

鎖表的語(yǔ)句：

LOCK TABLES table_name [READ | WRITE];

解鎖表的語(yǔ)句：

UNLOCK TABLES;

鎖定數(shù)據(jù)庫(kù)中所有表：

FLUSH TABLES WITH READ LOCK;

更多關(guān)于MySQL相關(guān)內(nèi)容感興趣的讀者可查看本站專(zhuān)題：《MySQL索引操作技巧匯總》、《MySQL常用函數(shù)大匯總》、《MySQL日志操作技巧大全》、《MySQL事務(wù)操作技巧匯總》、《MySQL存儲(chǔ)過(guò)程技巧大全》及《MySQL數(shù)據(jù)庫(kù)鎖相關(guān)技巧匯總》

希望本文所述對(duì)大家MySQL數(shù)據(jù)庫(kù)計(jì)有所幫助。

最新評(píng)論