一. 數(shù)據(jù)讀寫(xiě)流程簡(jiǎn)要
SQL Server作為一個(gè)關(guān)系型數(shù)據(jù)庫(kù),自然也維持了事務(wù)的ACID特性,數(shù)據(jù)庫(kù)的讀寫(xiě)沖突由事務(wù)隔離級(jí)別控制。無(wú)論有沒(méi)有顯示開(kāi)啟事務(wù),事務(wù)都是存在的。流程圖如下:
數(shù)據(jù)讀寫(xiě)流程圖
0. 事務(wù)開(kāi)始
(1) 所有DML語(yǔ)句必然是基于事務(wù)的,如果沒(méi)有顯式開(kāi)啟事務(wù),即手動(dòng)寫(xiě)下BEGIN TRAN,SQL Server則把每條語(yǔ)句作為一個(gè)事務(wù),并自動(dòng)提交事務(wù)。
也就是說(shuō)SQL SERVER 默認(rèn)不開(kāi)啟隱式事務(wù),這點(diǎn)與ORACLE正好相反,ORACLE默認(rèn)開(kāi)啟了隱式事務(wù),每條DML語(yǔ)句或者語(yǔ)句塊,都要手動(dòng)commit才會(huì)提交。
SQL Server里如要改變這個(gè)默認(rèn)行為,可以在會(huì)話里做如下設(shè)置,如果沒(méi)有打開(kāi)隱式事務(wù),SQL Server會(huì)自動(dòng)提交當(dāng)前的DML語(yǔ)句,而打開(kāi)后,需要手動(dòng)COMMIT才會(huì)提交。
-- 開(kāi)啟隱式事務(wù) SET IMPLICIT_TRANSACTIONS ON -- 插入一條記錄 CREATE TABLE TEST_TRAN(ID INT ) INSERT INTO TEST_TRAN VALUES ( 1 ) -- 查看開(kāi)啟的事務(wù) DBCC OPENTRAN()
(2) 如果手動(dòng)開(kāi)啟了一個(gè)事務(wù)(BEGIN TRAN),則和開(kāi)啟隱式事務(wù)(SET IMPLICIT_TRANSACTIONS ON)一樣,需要手動(dòng)提交事務(wù)(COMMIT);
1. 發(fā)起DML
(1) DML通常指的是:INSERT、DELETE、UPDATE;
(2) DDL語(yǔ)句最終是被轉(zhuǎn)化為對(duì)系統(tǒng)表的DML,在SQL SERVER中DDL語(yǔ)句也可以被回滾,比如:CREATE/ALTER/DROP/TRUNCATE,在ORACLE里是不可以的,另外SQL Server中的DCL語(yǔ)句:DENY,REVOKE,也可以被回滾;
2. 數(shù)據(jù)是否在內(nèi)存
(1) 在內(nèi)存中使用HASH算法查找數(shù)據(jù),如果找到數(shù)據(jù)那么記為邏輯讀;
(2) 如果數(shù)據(jù)頁(yè)不在內(nèi)存中,則需要從磁盤(pán)上的數(shù)據(jù)文件中,讀取相應(yīng)的數(shù)據(jù)頁(yè)到內(nèi)存中,即物理讀,物理讀也會(huì)被記數(shù)為邏輯讀,也就是說(shuō)無(wú)論內(nèi)存中有沒(méi)有數(shù)據(jù),邏輯讀是一定有的。
3. 修改數(shù)據(jù)
(1) 在SQL SERVER內(nèi)存的數(shù)據(jù)緩沖區(qū)中將數(shù)據(jù)頁(yè)修改,此時(shí)數(shù)據(jù)頁(yè)稱(chēng)為臟頁(yè)(DIRTY PAGE);
(2) 在SQL SERVER內(nèi)存的日志緩沖區(qū)中記錄REDO LOG,姑且稱(chēng)為臟日志;
4. 事務(wù)結(jié)束
(1) 提交(COMMIT),此時(shí)將當(dāng)前事務(wù)的臟日志刷新到數(shù)據(jù)庫(kù)的日志文件中,并打上事務(wù)結(jié)束標(biāo)記(COMMIT),臟頁(yè)有可能暫未被刷新到數(shù)據(jù)文件;
事務(wù)日志結(jié)構(gòu)如下(可通過(guò)log explorer等類(lèi)似工具查看):
BEGIN TRAN
DML
COMMIT TRAN
(2) 回滾(ROLLBACK),此時(shí)讀REDO LOG得到反向DML操作,反向修改臟頁(yè),正向的DML+反向DML都會(huì)被記錄在數(shù)據(jù)庫(kù)的日志文件中,并打上事務(wù)結(jié)束標(biāo)記(ROLLBACK),同樣,臟頁(yè)有可能暫未被刷新到數(shù)據(jù)文件;
事務(wù)日志結(jié)構(gòu)如下:
BEGIN TRAN
DML
反向DML
ROLLBACK TRAN
不難發(fā)現(xiàn),SQL SERVER的日志容易成為一個(gè)瓶頸(BOTTLENECK),因?yàn)樵趯?xiě)的同時(shí)引入了讀,即引入了競(jìng)爭(zhēng),而ORACLE用UNDO SEGMENT很好地避免了這個(gè)問(wèn)題,REDO LOG永遠(yuǎn)只是在被串行寫(xiě)。
5. 刷新數(shù)據(jù)頁(yè)
(1) SQL Server數(shù)據(jù)庫(kù)遵循預(yù)寫(xiě)日志(WAL:Write-Ahead Logging)原則,因?yàn)殛P(guān)系型數(shù)據(jù)庫(kù)是基于事務(wù)的,而日志正是事務(wù)ACID屬性的保證,也是數(shù)據(jù)恢復(fù)的保證;
(2) 檢查點(diǎn)(CHECKPOINT),檢查點(diǎn)周期性地將臟頁(yè)刷新到數(shù)據(jù)文件中,最終在日志文件打上檢查點(diǎn)標(biāo)記(CHECKPOINT),至此上面事務(wù)中修改的數(shù)據(jù)被正式寫(xiě)到磁盤(pán)上的數(shù)據(jù)文件中。
二. 數(shù)據(jù)讀寫(xiě)流程深入
試想:
(1) 日志是不是一定要在COMMIT后才寫(xiě)到日志文件?如果有個(gè)很長(zhǎng)很大的事務(wù),那么提交日志時(shí),日志從緩沖區(qū)被寫(xiě)入磁盤(pán),豈不是要等很久?
(2) 數(shù)據(jù)是不是一定要在日志提交后,發(fā)生了CHECKPOINT,才寫(xiě)到數(shù)據(jù)文件?如果日志一直沒(méi)提交,那么數(shù)據(jù)緩沖區(qū)豈不是很擁擠?
考慮到這2點(diǎn),SQL Server還會(huì)通過(guò)Log Writer/Lazy Writer不定時(shí)的刷新日志/數(shù)據(jù)到磁盤(pán),至于日志和數(shù)據(jù)的一致性,在啟動(dòng)或者數(shù)據(jù)庫(kù)還原時(shí),SQL Server會(huì)去做檢查,也即是我們常說(shuō)的前滾(REDO)和回滾(UNDO)。
數(shù)據(jù)讀寫(xiě)體系結(jié)構(gòu)圖
0. SQL SERVER MEMORY
(1) SQL SERVER占用服務(wù)器內(nèi)存的一部分,非SQL SERVER占用的內(nèi)存,供操作系統(tǒng)及服務(wù)器上其他應(yīng)用程序使用;
(2) SQL SERVER內(nèi)存對(duì)象可分為兩大類(lèi),圖中僅標(biāo)出Buffer Pool中的數(shù)據(jù)及日志緩存;
1. 事務(wù)結(jié)束
(1) 事務(wù)結(jié)束的前提是日志緩存成功寫(xiě)入到日志文件中,也就是說(shuō)客戶端收到COMMIT/ROLLBACK語(yǔ)句運(yùn)行成功的消息時(shí),日志已被成功寫(xiě)入日志文件(數(shù)據(jù)還不定是否被寫(xiě)入數(shù)據(jù)文件);
(2) 不過(guò),日志緩存并不是一定要等到事務(wù)結(jié)束時(shí)才刷新到日志文件的;
2. LOG WRITER
(1) 當(dāng)遇到長(zhǎng)事務(wù)時(shí),不必等到發(fā)出事務(wù)結(jié)束命令,LOG WRITER也會(huì)周期性地將臟日志刷新到日志文件,以保證用戶發(fā)出COMMIT時(shí)快速響應(yīng)以結(jié)束事務(wù);
(2) 微軟并沒(méi)有公布SQL SERVER 除去COMMIT外,LOG WRITER將臟日志刷新到日志文件的周期,這里可以參考ORACLE的:每3秒,或者日志緩沖區(qū)1/3滿;或者已經(jīng)包含1M的臟日志;
3. LAZY WRITER
(1) LAZY WRITER周期性掃描緩存(默認(rèn)1s),維護(hù)自由頁(yè)面(free page)列表,根據(jù)LRU算法將已刷新到磁盤(pán)的頁(yè)釋放;
(2) 如果是臟頁(yè),則Lazy Writer將臟頁(yè)刷新到磁盤(pán)(這時(shí)事務(wù)可能還未提交),以最終將內(nèi)存頁(yè)釋放并加入自由頁(yè)面列表;
4. CHECKPOINT
(1) CHECKPOINT同LAZY WRITER一樣也會(huì)刷新臟頁(yè)到數(shù)據(jù)文件中(只刷新已提交的事務(wù)數(shù)據(jù)),但不會(huì)維護(hù)內(nèi)存自由頁(yè)面列表;
(2) 可以設(shè)置SP_CONFIGURE ‘RECOVERY INTERVAL’選項(xiàng)來(lái)改變CHECKPOINT發(fā)生的頻率,默認(rèn)為1分鐘一次。
小結(jié)
:可以發(fā)現(xiàn),數(shù)據(jù)和日志被寫(xiě)入數(shù)據(jù)/日志文件,并不是同步的。有可能寫(xiě)入/提交了日志,數(shù)據(jù)沒(méi)有寫(xiě)入磁盤(pán);有可能寫(xiě)入了數(shù)據(jù),事務(wù)未被提交;
(1) 針對(duì)有完整事務(wù)日志,數(shù)據(jù)未被寫(xiě)入磁盤(pán)的情況,啟動(dòng)/還原數(shù)據(jù)庫(kù)時(shí),SQL SERVER做前滾(REDO);
(2) 針對(duì)有數(shù)據(jù)寫(xiě)入數(shù)據(jù)文件,日志未完整提交的事務(wù),啟動(dòng)/還原數(shù)據(jù)庫(kù)時(shí),SQL SERVER做回滾(UNDO)。
?
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索:z360901061

微信掃一掃加我為好友
QQ號(hào)聯(lián)系: 360901061
您的支持是博主寫(xiě)作最大的動(dòng)力,如果您喜歡我的文章,感覺(jué)我的文章對(duì)您有幫助,請(qǐng)用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧,狠狠點(diǎn)擊下面給點(diǎn)支持吧,站長(zhǎng)非常感激您!手機(jī)微信長(zhǎng)按不能支付解決辦法:請(qǐng)將微信支付二維碼保存到相冊(cè),切換到微信,然后點(diǎn)擊微信右上角掃一掃功能,選擇支付二維碼完成支付。
【本文對(duì)您有幫助就好】元
