MySQL InnoDB之事務與鎖詳解
引題：為何引入事務？
1>.數(shù)據(jù)完整性
2>.數(shù)據(jù)安全性
3>.充分利用系統(tǒng)資源，提高系統(tǒng)并發(fā)處理的能力
1. 事務的特征
事務具有四個特性：原子性（Atomiocity）、一致性（Consistency）、隔離性（Isolation）和持久性（Durability），這四個特性簡稱ACID特性。
1.1原子性
事務是數(shù)據(jù)庫的邏輯工作單位，事務中包括的所有操作要么都做，要么都不做。
1.2 一致性
事務執(zhí)行的結(jié)果必須是使數(shù)據(jù)庫從一個一致性的狀態(tài)變到另外一個一致性狀態(tài)。
1.3 隔離性
一個事務的執(zhí)行不能被其他事務干擾。即一個事務內(nèi)部的操作及使用的數(shù)據(jù)對其他
事務是隔離的，并發(fā)執(zhí)行的各個事務之間互相不干擾。
1.4 持久性
一個事務一旦成功提交，對數(shù)據(jù)庫中數(shù)據(jù)的修改就是持久性的。接下來其他的其他
操作或故障不應該對其執(zhí)行結(jié)果有任何影響。
2. MySQL的InnoDB引擎中事物與鎖
2.1 SELECT …… LOCK IN SHARE MODE
會話事務中查找的數(shù)據(jù)，加上一個共享鎖。若會話事務中查找的數(shù)據(jù)已經(jīng)被其他會話事務加上獨占鎖的話，共享鎖會等待其結(jié)束再加，若等待時間過長就會顯示事務需要的鎖等待超時。
2.2 SELECT ….. FOR UPDATE
會話事務中查找的數(shù)據(jù)，加上一個讀更新瑣，其他會話事務將無法再加其他鎖，必須等待其結(jié)束。
2.3 INSERT、UPDATE、DELETE
會話事務會對DML語句操作的數(shù)據(jù)加上一個獨占鎖，其他會話的事務都將會等待其釋放獨占鎖。
2.4 gap and next key lock（間隙鎖）
InnoDB引擎會自動給會話事務中的共享鎖、更新瑣以及獨占鎖，需要加到一個區(qū)間值域的時候，再加上個間隙鎖（或稱范圍鎖），對不存在的數(shù)據(jù)也鎖住，防止出現(xiàn)幻寫。
備注：
以上2.1，2.2，2.3，2.4中描述的情況，跟MySQL所設置的事務隔離級別也有關系。
3.四種事務隔離模式
3.1 READ UNCOMMITED
SELECT的時候允許臟讀，即SELECT會讀取其他事務修改而還沒有提交的數(shù)據(jù)。
3.2 READ COMMITED
SELECT的時候無法重復讀，即同一個事務中兩次執(zhí)行同樣的查詢語句，若在第一次與第二次查詢之間時間段，其他事務又剛好修改了其查詢的數(shù)據(jù)且提交了，則兩次讀到的數(shù)據(jù)不一致。
3.3 REPEATABLE READ
SELECT的時候可以重復讀，即同一個事務中兩次執(zhí)行同樣的查詢語句，得到的數(shù)據(jù)始終都是一致的。
3.4 SERIALIZABLE
與可重復讀的唯一區(qū)別是，默認把普通的SELECT語句改成SELECT …. LOCK IN SHARE MODE。即為查詢語句涉及到的數(shù)據(jù)加上共享瑣，阻塞其他事務修改真實數(shù)據(jù)。
4. 驗證事務與鎖定示例
接下來，我們將以MySQL中的InnoDB引擎，解釋其如何實現(xiàn)ACID特性，不同隔離級別下事務與事務之間的影響。示例表結(jié)構：
CREATE TABLE `account ` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`VACCOUNT_ID` varchar(32) NOT NULL,
`GMT_CREATE` datetime NOT NULL,
PRIMARY KEY (`ID`),
KEY `idx_VACCOUNT_PARAMETER_VACCOUNTID ` (`VACCOUNT_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE utf8_general_ci;
然后向表account中寫入10W條創(chuàng)建日期分布合理的帳號數(shù)據(jù)，以方便測試之用。
| tx_isolation：SET GLOBAL tx_isolation='read-uncommitted' | ||||
| ID | 事務1 | 事務1輸出 | 事務2 | 事務2輸出 |
| 1 | START TRANSACTION; | |||
| 2 | SELECT VACCOUNT_ID from account where ID =1001; | caimao101510 | ||
| START TRANSACTION; | ||||
| 3 | UPDATE account set VACCOUNT_ID='uncommitted' where ID =1001; | |||
| 4 | SELECT VACCOUNT_ID from account where ID =1001; | uncommitted | ||
| 5 | SELECT VACCOUNT_ID from account where ID =1001; | uncommitted | ||
| 6 | ROLLBACK; | |||
| 7 | SELECT VACCOUNT_ID from account where ID =1001; | caimao101510 | ||
| 8 | COMMIT; |
| tx_isolation：SET GLOBAL tx_isolation='read-committed' | ||||
| ID | 事務1 | 事務1輸出 | 事務2 | 事務2輸出 |
| 1 | START TRANSACTION; | |||
| 2 | SELECT VACCOUNT_ID from account where ID =1001; | caimao101510 | ||
| 3 | START TRANSACTION; | |||
| 4 | UPDATE account set VACCOUNT_ID='uncommitted' where ID =1001; | |||
| 5 | SELECT VACCOUNT_ID from account where ID =1001; | uncommitted | ||
| 6 | SELECT VACCOUNT_ID from account where ID =1001; | caimao101510 | ||
| 7 | COMMIT; | |||
| 8 | SELECT VACCOUNT_ID from account where ID =1001; | uncommitted | ||
| 9 | COMMIT; |
| tx_isolation：SET GLOBAL tx_isolation='REPEATABLE-READ' | ||||
| ID | 事務1 | 事務1輸出 | 事務2 | 事務2輸出 |
| 1 | START TRANSACTION; | |||
| 2 | SELECT VACCOUNT_ID from account where ID =1001; | caimao101510 | ||
| 3 | START TRANSACTION; | |||
| 4 | UPDATE account set VACCOUNT_ID='uncommitted' where ID =1001; | |||
| 5 | SELECT VACCOUNT_ID from account where ID =1001; | uncommitted | ||
| 6 | SELECT VACCOUNT_ID from account where ID =1001; | caimao101510 | ||
| 7 | COMMIT; | |||
| 8 | SELECT VACCOUNT_ID from account where ID =1001; | caimao101510 | ||
| 9 | COMMIT; |
| tx_isolation：SET GLOBAL tx_isolation='SERIALIZABLE' | ||||
| ID | 事務1 | 事務1輸出 | 事務2 | 事務2輸出 |
| 1 | START TRANSACTION; | |||
| 2 | SELECT VACCOUNT_ID from account where ID =1001; | caimao101510 | ||
| 3 | START TRANSACTION; | |||
| 4 | UPDATE account set VACCOUNT_ID='uncommitted' where ID =1001; | STATE: Updating | ||
| 5 | SELECT VACCOUNT_ID from account where ID =1001; | caimao101510 | ||
| 事務2超時 | ||||
| 6 | COMMIT; | |||
| 7 | START TRANSACTION; | |||
| 8 | UPDATE account set VACCOUNT_ID='uncommitted' where ID =1001; | |||
| 9 | START TRANSACTION; | |||
| 10 | SELECT VACCOUNT_ID from account where ID =1001; | STATE：statistics | ||
| 11 | 事務2超時 | |||
| 12 | commit; |
| tx_isolation：SET GLOBAL tx_isolation='REPEATABLE-READ' | ||||
| ID | 事務1 | 事務1輸出 | 事務2 | 事務2輸出 |
| 1 | START TRANSACTION; | |||
| 2 | select max(ID) FROM account; | 124999 | ||
| 3 | START TRANSACTION; | |||
| 4 | UPDATE account set gmt_create=date_add(gmt_create,interval +1 day) WHERE ID >=124999; | |||
| 5 | insert into account(VACCOUNT_ID,gmt_create) values(‘eugene',now()); | STATE:update | ||
| 6 | 事務2超時 | |||
| 7 | START TRANSACTION; | |||
| 8 | SELECT * FROM account WHERE ID =124998; | 2007-10-20 13:47 | ||
| 9 | UPDATE account set gmt_create=date_add(gmt_create,interval +1 day) WHERE ID =124998; | 執(zhí)行成功 | ||
| 10 | SELECT * FROM account WHERE ID =124998; | 2007-10-21 13:47 | ||
| 11 | COMMIT; | |||
| 12 | COMMIT; | |||
| 1 | START TRANSACTION; | |||
| 2 | UPDATE account set gmt_create=date_add(gmt_create,interval -1 day) WHERE gmt_create >'2009-07-01′; | |||
| 3 | START TRANSACTION; | |||
| 4 | SELECT * FROM account WHERE gmt_create>'2009-07-10′ LIMIT 1; | 2009-10-2 13:47 | ||
| 5 | SELECT * FROM account WHERE gmt_create>'2009-07-10′ LIMIT 1; | 2009-10-1 13:47 | STATE:update | |
| 6 | insert into account(VACCOUNT_ID,gmt_create) values(‘gmt_create_test',now()); | |||
| 7 | 事務2超時 | |||
| 8 | COMMIT; | |||
| 9 | SELECT * FROM account WHERE gmt_create>'2009-07-10′ LIMIT 1; | 2009-10-1 13:47 | ||
| 無索引條件更新事務 | ||||
| 1 | START TRANSACTION; | |||
| UPDATE account set gmt_create=date_add(gmt_create,interval -1 day) WHERE gmt_create >'2009-07-01′ AND gmt_create <'2009-07-10′; | ||||
| START TRANSACTION; | ||||
| insert into account(VACCOUNT_ID,gmt_create) values(‘gmt_create_interval',now()); | ||||
| 事務2超時 | ||||
| COMMIT; |
相關文章
mysql 5.7.17 zip安裝配置教程 mysql啟動失敗的解決方法
這篇文章主要為大家詳細介紹了mysql 5.7.17 zip安裝配置教程，以及mysql啟動失敗的解決方法，具有一定的參考價值，感興趣的小伙伴們可以參考一下2019-06-06
CentOS系統(tǒng)下如何設置mysql每天自動備份
備份是容災的基礎，是指為防止系統(tǒng)出現(xiàn)操作失誤或系統(tǒng)故障導致數(shù)據(jù)丟失，而將全部或部分數(shù)據(jù)集合從應用主機的硬盤或陣列復制到其它的存儲介質(zhì)的過程。本文將詳細介紹在CentOS系統(tǒng)下如何設置mysql每天自動備份，有需要的朋友們下面來一起看看吧。2016-10-10
MySQL8.0實現(xiàn)窗口函數(shù)計算同比環(huán)比
本文主要介紹了MySQL8.0實現(xiàn)窗口函數(shù)計算同比環(huán)比，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2023-06-06
mysql啟動報錯MySQL server PID file could not be found
這篇文章主要介紹了mysql啟動報錯MySQL server PID file could not be found,具有一定的參考價值，感興趣的小伙伴們可以參考一下2016-11-11
使用mysqldump對MySQL的數(shù)據(jù)進行備份的操作教程
這篇文章主要介紹了使用mysqldump對MySQL的數(shù)據(jù)進行備份的操作教程,示例環(huán)境基于CentOS操作系統(tǒng),需要的朋友可以參考下2015-12-12
這篇文章主要介紹了Mysql 索引該如何設計與優(yōu)化，幫助大家更好的理解和學習使用MySQL，感興趣的朋友可以了解下2021-03-03
Mysql中Insert into xxx on duplicate key update問題
在看代碼的過程中碰到了這一用法，不太理解，google了一下。它的意義其實是如果在insert語句末尾制定了on duplicate key update語句的話，則當插入行會導致一個unique索引或者primary key中出現(xiàn)重復值，則執(zhí)行update中的語句，否則才插入新行2012-08-08
MySQL中between...and的使用對索引的影響說明
這篇文章主要介紹了MySQL中between...and的使用對索引的影響說明，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2023-07-07
MySQL是最流行的關系型數(shù)據(jù)庫管理系統(tǒng)，在WEB應用方面MySQL是最好的應用軟件之一。在本篇中，會讓大家快速掌握MySQL的基本操作，并輕松使用MySQL數(shù)據(jù)庫2022-09-09
mybatis-plus如何使用sql的date_format()函數(shù)查詢數(shù)據(jù)
這篇文章主要給大家介紹了關于mybatis-plus如何使用sql的date_format()函數(shù)查詢數(shù)據(jù)的相關資料,文中通過實例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友可以參考下2023-02-02