事務隔離級別 | 臟讀 | 不可重復讀 | 幻讀 |
讀取未提交 read uncommitted |
√ |
√ |
√ |
讀已提交 read committed |
× |
√ |
√ |
可重復讀取 repeatable read |
× |
× |
√ |
序列化 serializable |
× |
× |
× |
四、獲取和設置數(shù)據(jù)庫隔離級別
SHOW VARIABLES LIKE '%isolation%'; SHOW GLOBAL VARIABLES LIKE '%isolation%';
使用系統(tǒng)變量查詢
SELECT @@GLOBAL.tx_isolation; SELECT @@SESSION.tx_isolation; SELECT @@tx_isolation;
對于mysql8而言,使用下面的變量進行查詢
SELECT @@GLOBAL.transaction_isolation; SELECT @@SESSION.transaction_isolation; SELECT @@transaction_isolation;
設置隔離級別
SET GLOBAL tx_isolation = '隔離級別'; SET SESSION tx_isolation = '隔離級別'; SET @@tx_isolation = '隔離級別';
對于mysql8而言,使用下面語句進行設置
SET GLOBAL transaction_isolation = '隔離級別'; SET SESSION transaction_isolation = '隔離級別'; SET @@transaction_isolation = '隔離級別';
五、通過例子說明各隔離級別的情況
先準備一張表,和一點數(shù)據(jù)。
CREATE TABLE `account` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID', `name` varchar(32) DEFAULT '' COMMENT '名稱', `money` decimal(11,2) DEFAULT '0.00' COMMENT '金錢', 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;
事務B修改了表中的數(shù)據(jù),但是未提交,事務A確讀取到了修改后的數(shù)據(jù)。如果因為某些原因,事務B回滾了,事務A讀取的這個數(shù)據(jù)就是臟數(shù)據(jù)。
2、讀已提交
set transaction_isolation = 'READ-COMMITTED'; set autocommit = 0;
事務B修改數(shù)據(jù)但沒有提交,那么事務A仍然獲取的原來數(shù)據(jù),解決了臟讀的問題。
但是事務B提交,事務A執(zhí)行上一次查詢,結果與上一次查詢不一致,這就產(chǎn)生不可重復讀的問題。
3、可重復讀取
set transaction_isolation = 'REPEATABLE-READ'; set autocommit = 0;
事務B修改了數(shù)據(jù)并提交了,事務A兩次查詢的結果是一致的,解決了不可重復讀的問題。
這個時候,事務A去修改name為A的money數(shù)據(jù)
name為A的money變成了350,而不是400,可重復讀保證了數(shù)據(jù)的一致性。
我們重新在事務A中修改所有賬號的money等于200,同時在事務B中插入一條新的數(shù)據(jù)。
事務A中獲取的仍然是兩條數(shù)據(jù),解決了新增數(shù)據(jù)時,事務A出現(xiàn)的幻讀問題。
4、序列化
set transaction_isolation = 'SERIALIZABLE'; set autocommit = 0;
事務A對表進行查詢,如果沒有提交,則事務B的插入語句一直等待在那里,直到超時或事務A提交。
反之,事務B對表進行插入后,沒有提交,則事務A對表的查詢也一直等待,直到事務B提交。
此時對表的讀寫都會進行鎖表,當然對并發(fā)性能的影響也比較大。
隔離級別越高,越能保證數(shù)據(jù)的完整性和一致性。
六、mysql的鎖
鎖分為兩種類型:
內(nèi)部鎖:mysql服務器內(nèi)部執(zhí)行的內(nèi)部鎖,以管理多個會話對表內(nèi)容的爭用。
外部鎖:mysql為客戶會話提供顯式地獲取表鎖,以阻止其他會話訪問表。
內(nèi)部鎖又會為兩種類型:
1、行級鎖:行級鎖是細粒度的,只有被訪問的行會被鎖定,這允許多個會話同時進行寫訪問。
2、表級鎖:mysql對myisam,memory和merge表使用表級鎖,一次只允許一個會話更新表,這使得這些存儲引擎更適用于以讀取為主的操作。
外部鎖:可以使用 LOCK TABLE 和 UNLOCK TABLE 來控制鎖定。
READ (共享鎖) :多個會話可以從表中讀取數(shù)據(jù)而不需要獲取鎖,此外,多個會話可以在同一表上獲得鎖,當 READ 鎖時,沒有會話可以將數(shù)據(jù)寫入表中。任何寫入操作都將處于等待狀態(tài),直到 READ 鎖被釋放。
WRITE (排他鎖) :當表被 WRITE 鎖定時,除持有該鎖的會話外,其他會話都不能讀取或寫入數(shù)據(jù),除非 WRITE 鎖被釋放。
鎖表的語句:
LOCK TABLES table_name [READ | WRITE];
解鎖表的語句:
UNLOCK TABLES;
鎖定數(shù)據(jù)庫中所有表:
FLUSH TABLES WITH READ LOCK;
更多關于MySQL相關內(nèi)容感興趣的讀者可查看本站專題:《MySQL索引操作技巧匯總》、《MySQL常用函數(shù)大匯總》、《MySQL日志操作技巧大全》、《MySQL事務操作技巧匯總》、《MySQL存儲過程技巧大全》及《MySQL數(shù)據(jù)庫鎖相關技巧匯總》
希望本文所述對大家MySQL數(shù)據(jù)庫計有所幫助。