當前位置:首頁 » 數據倉庫 » 資料庫事物鎖索引
擴展閱讀
webinf下怎麼引入js 2023-08-31 21:54:13
堡壘機怎麼打開web 2023-08-31 21:54:11

資料庫事物鎖索引

發布時間: 2023-06-01 13:03:07

『壹』 大表,update,where條件沒有索引,會加多少鎖

正常情況應碰彎該只有銀橡一個鎖 exclusive table lock,這是笑搏悶由lock promotion HWM 決定的, ASE escalates the locks to table lock whenever over 200 rows (DOL) or 200pages (APL) are involved for updates。估計你遇到的是out of locks通常情型之一:one process was holding the lock(s) and it prevented your update process from escalating the row/page locks to table lock. "select" holds exclusive locks under isolation level 2 (repeatable read).只有運用one of the best ASE practices: small batch size. 分批提交update,例如1000條記錄一提交,就可防止out of the locks。

『貳』 mysql 發生死鎖問題請求幫助

這是我見的一個文檔,雖然我看不懂,你看看有沒有幫助
MySQL死鎖問題的相關知識是本文我們主要要介紹的內容,接下來我們就來一一介紹這部分內容,希望能夠對您有所幫助。
1、MySQL常用存儲引擎的鎖機制
MyISAM和MEMORY採用表級鎖(table-level locking)
BDB採用頁面鎖(page-level locking)或表級鎖,默認為頁面鎖
InnoDB支持行級鎖(row-level locking)和表級鎖,默認為行級鎖
2、各種鎖特點
表級鎖:開銷小,加鎖快;不會出現死鎖;鎖定粒度大,發生鎖沖突的概率最高,並發度最低
行級鎖:開銷大,加鎖慢;會出現死鎖;鎖定粒度最小,發生鎖沖突的概率最低,並發度也最高
頁面鎖:開銷和加鎖時間界於表鎖和行鎖之間;會出現死鎖;鎖定粒度界於表鎖和行鎖之間,並發度一般
3、各種鎖的適用場景
表級鎖更適合於以查詢為主,只有少量按索引條件更新數據的應用,如Web應用
行級鎖則更適合於有大量按索引條件並發更新數據,同時又有並發查詢的應用,如一些在線事務處理系統
4、死鎖
是指兩個或兩個以上的進程在執行過程中,因爭奪資源而造成的一種互相等待的現象,若無外力作用,它們都將無法推進下去。
表級鎖不會產生死鎖。所以解決死鎖主要還是針對於最常用的InnoDB。
5、死鎖舉例分析
在MySQL中,行級鎖並不是直接鎖記錄,而是鎖索引。索引分為主鍵索引和非主鍵索引兩種,如果一條sql語句操作了主鍵索引,MySQL就會鎖定這條主鍵索引;如果一條語句操作了非主鍵索引,MySQL會先鎖定該非主鍵索引,再鎖定相關的主鍵索引。
在UPDATE、DELETE操作時,MySQL不僅鎖定WHERE條件掃描過的所有索引記錄,而且會鎖定相鄰的鍵值,即所謂的next-key locking。
例如,一個表db。tab_test,結構如下:
id:主鍵;
state:狀態;
time:時間;
索引:idx_1(state,time)
出現死鎖日誌如下:
?***(1) TRANSACTION:
?TRANSACTION 0 677833455, ACTIVE 0 sec, process no 11393, OSthread id 278546 starting index read
?mysql tables in use 1, locked 1
?LOCK WAIT 3 lock struct(s), heap size 320
?MySQL thread id 83, query id 162348740 dcnet03 dcnet Searching rows for update
?update tab_test set state=1064,time=now() where state=1061 and time < date_sub(now(), INTERVAL 30 minute) (任務1的sql語句)
?***(1) WAITING FOR THIS LOCK TO BE GRANTED: (任務1等待的索引記錄)
?RECORD LOCKS space id 0 page no 849384 n bits 208 index `PRIMARY` of table `db/tab_test` trx id 0 677833455 _mode X locks rec but not gap waiting
?Record lock, heap no 92 PHYSICAL RECORD: n_fields 11; compact format; info bits 0
?0: len 8; hex 800000000097629c; asc b ;; 1: len 6; hex 00002866eaee; asc (f ;; 2: len 7; hex 00000d40040110; asc @ ;; 3: len 8; hex 80000000000050b2; asc P ;; 4: len 8; hex 800000000000502a; asc P*;; 5: len 8; hex 8000000000005426; asc T&;; 6: len 8; hex 800012412c66d29c; asc A,f ;; 7: len 23; hex 8616e642e706870; asc xxx.com/;; 8: len 8; hex 800000000000042b; asc +;; 9: len 4; hex 474bfa2b; asc GK +;; 10: len 8; hex 8000000000004e24; asc N$;;
?*** (2) TRANSACTION:
?TRANSACTION 0 677833454, ACTIVE 0 sec, process no 11397, OS thread id 344086 updating or deleting, thread declared inside InnoDB 499
?mysql tables in use 1, locked 1
?3 lock struct(s), heap size 320, undo log entries 1
?MySQL thread id 84, query id 162348739 dcnet03 dcnet Updating update tab_test set state=1067,time=now () where id in (9921180) (任務2的sql語句)
?*** (2) HOLDS THE LOCK(S): (任務2已獲得的鎖)
?RECORD LOCKS space id 0 page no 849384 n bits 208 index `PRIMARY` of table `db/tab_test` trx id 0 677833454 lock_mode X locks rec but not gap
?Record lock, heap no 92 PHYSICAL RECORD: n_fields 11; compact format; info bits 0
?0: len 8; hex 800000000097629c; asc b ;; 1: len 6; hex 00002866eaee; asc (f ;; 2: len 7; hex 00000d40040110; asc @ ;; 3: len 8; hex 80000000000050b2; asc P ;; 4: len 8; hex 800000000000502a; asc P*;; 5: len 8; hex 8000000000005426; asc T&;; 6: len 8; hex 800012412c66d29c; asc A,f ;; 7: len 23; hex 8616e642e706870; asc uploadfire.com/hand.php;; 8: len 8; hex 800000000000042b; asc +;; 9: len 4; hex 474bfa2b; asc GK +;; 10: len 8; hex 8000000000004e24; asc N$;;
?*** (2) WAITING FOR THIS LOCK TO BE GRANTED: (任務2等待的鎖)
?RECORD LOCKS space id 0 page no 843102 n bits 600 index `idx_1` of table `db/tab_test` trx id 0 677833454 lock_mode X locks rec but not gap waiting
?Record lock, heap no 395 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
?0: len 8; hex 8000000000000425; asc %;; 1: len 8; hex 800012412c66d29c; asc A,f ;; 2: len 8; hex 800000000097629c; asc b ;;
?*** WE ROLL BACK TRANSACTION (1)
?(回滾了任務1,以解除死鎖)
原因分析:
當「update tab_test set state=1064,time=now() where state=1061 and time < date_sub(now(), INTERVAL 30 minute)」執行時,MySQL會使用idx_1索引,因此首先鎖定相關的索引記錄,因為idx_1是非主鍵索引,為執行該語句,MySQL還會鎖定主鍵索引。
假設「update tab_test set state=1067,time=now () where id in (9921180)」幾乎同時執行時,本語句首先鎖定主鍵索引,由於需要更新state的值,所以還需要鎖定idx_1的某些索引記錄。
這樣第一條語句鎖定了idx_1的記錄,等待主鍵索引,而第二條語句則鎖定了主鍵索引記錄,而等待idx_1的記錄,這樣死鎖就產生了。
6、解決辦法
拆分第一條sql,先查出符合條件的主鍵值,再按照主鍵更新記錄:
?select id from tab_test where state=1061 and time < date_sub(now(), INTERVAL 30 minute);
?update tab_test state=1064,time=now() where id in(......);

『叄』 資料庫事務使用方法

一個大型、穩健、成熟的分布式系統的背後,往往會涉及眾多的支撐系統,我們將這些支撐系統稱為分布式系統的基礎設施。除了前面所介紹的分布式協作及配置管理系統ZooKeeper,我們進行系統架構設計所依賴的基礎設施,還包括分布式緩存系統、持久化存儲、分布式消息系統、搜索引擎,以及CDN系統、負載均衡系統、運維自動化系統等,還有後面章節所要介紹的實時計算系統、離線計算系統、分布式文件系統、日誌收集系統、監控系統、數據倉庫等。
分布式緩存主要用於在高並發環境下,減輕資料庫的壓力,提高系統的響應速度和並發吞吐。當大量的讀、寫請求湧向資料庫時,磁碟的處理速度與內存顯然不在一個量級,因此,在資料庫之前加一層緩存,能夠顯著提高系統的響應速度,並降低資料庫的壓力。作為傳統的關系型資料庫,MySQL提供完整的ACID操作,支持豐富的數據類型、強大的關聯查詢、where語句等,能夠非常客易地建立查詢索引,執行復雜的內連接、外連接、求和、排序、分組等操作,並且支持存儲過程、函數等功能,產品成熟度高,功能強大。但是,對於需要應對高並發訪問並且存儲海量數據的場景來說,出於對性能的考慮,不得不放棄很多傳統關系型資料庫原本強大的功能,犧牲了系統的易用性,並且使得系統的設計和管理變得更為復雜。這也使得在過去幾年中,流行著另一種新的存儲解決方案——NoSQL,它與傳統的關系型資料庫最大的差別在於,它不使用SQL作為查詢語言來查找數據,而採用key-value形式進行查找,提供了更高的查詢效率及吞吐,並且能夠更加方便地進行擴展,存儲海量數據,在數千個節點上進行分區,自動進行數據的復制和備份。在分布式系統中,消息作為應用間通信的一種方式,得到了十分廣泛的應用。消息可以被保存在隊列中,直到被接收者取出,由於消息發送者不需要同步等待消息接收者的響應,消息的非同步接收降低了系統集成的耦合度,提升了分布式系統協作的效率,使得系統能夠更快地響應用戶,提供更高的吞吐。
當系統處於峰值壓力時,分布式消息隊列還能夠作為緩沖,削峰填谷,緩解集群的壓力,避免整個系統被壓垮。垂直化的搜索引擎在分布式系統中是一個非常重要的角色,它既能夠滿足用戶對於全文檢索、模糊匹配的需求,解決資料庫like查詢效率低下的問題,又能夠解決分布式環境下,由於採用分庫分表,或者使用NoSQL資料庫,導致無法進行多表關聯或者進行復雜查詢的問題。

『肆』 資料庫鎖表是什麼意思

oracle資料庫 鎖表和死鎖的區別
死鎖指的是a,b兩個事務對同一對象進行dml或ddl操作(即修改表結構或者增刪改數據),出現了相互等待被鎖定的對象的情況,即類似於紅綠燈十字路口紅燈方向堵住路口,綠燈方向卻紅燈車輛擋在路口不能過去,這樣無論紅綠燈如何變化都無法通行。一般像oracle這樣的dbms是有死鎖檢測的,然後把鎖定對象拋出來按照預定規則處理或者讓程序處理。 鎖等待指的是a事務鎖定了操作對象,而b事務也要對其進行dml或ddl操作(即修改表結構或者增刪改數據)時,需要等待a事務完成。這個和死鎖不同,只要a事務完成後,b事務就可以正常進行了。類似於正常的紅綠燈十字路口通行狀態:紅燈方向就是等待鎖釋放的b事務,綠燈方向就是鎖定路口的a事務。待紅綠燈互換,則a事務執行完畢,b事務也就可以正常執行啦。
MySQL鎖表是什麼意思?有什麼用?什麼情況下用?好處?缺點?
白話解說如下:

簡單說,就是lock table,不讓別人動

鎖分共享鎖和排它鎖。

共享鎖時,別人能讀,不能改變數表數據

排它鎖時,別人既不能讀,也不能改表數據

根據以上特點,應該就知道何時使用鎖了。不想讓別人變更數據,對自己產生影響,就加鎖。一定要在不用之後,進行鎖釋放,不然,應歷搭用系統會一直因為讀取數據而報錯。

好處就是,保證數據的原子性,完整性,一致性。 只有加鎖者釋放了鎖,別人才能改變數據。

缺點就是,增加了系統開銷,有可能產生鎖等待,造成資料庫運行異常。這都是不正常的使用鎖帶來的問題。
什麼情況下造成資料庫鎖表? 如何解決?
./question/180766896
兩個SQL的鎖表問題
不同的資料庫,多版本的實現機制不同,上述語句執行情況也就不一樣,下面以oracle為例說明:

1.insert/delete語句可以並發執行,陸慧不會鎖等待

2.並發insert不會鎖等待

3.並發update,如果不是操作同一條記錄,不會鎖等待

=================================================

對真實存在的數據進行並發操作才有可能發生寫沖突,所以樓主供要把握住這點就可以早爛答判斷是否會沖突了。

建議樓主構造簡單數據,開兩個客戶端,在不同的隔離級下去模擬並發操作,理論和實踐相結合,你會理解的更透徹。
oracle 資料庫 為什麼鎖表
簡單地說,鎖是為了保證數據的一致性,鎖不止存在於oracle,其他資料庫一樣有,只不過機制上可能大相徑庭。 至於什麼樣的操作會鎖表,其實鎖的種類很多,你所說的鎖表大概說的是行級鎖——也就是事務鎖吧
如何將資料庫中被鎖表解鎖
在操作資料庫的時候,有時候會由於操作不當引起資料庫表被鎖定,這么我們經常不知所措,不知怎麼給這些表解鎖,在pl/sql Developer工具的的菜單「tools」裡面的「sessions」可以查詢現在存在的會話,但是我們很難找到那個會話被鎖定了,想找到所以被鎖的會話就更難了,下面這叫查詢語句可以查詢出所以被鎖的會話。如下:

SELECT sn.username, m.SID,sn.SERIAL#, m.TYPE,

DECODE (m.lmode,

0, 'None',

1, 'Null',

2, 'Row Share',

3, 'Row Excl.',

4, 'Share',

5, 'S/Row Excl.',

6, 'Exclusive',

lmode, LTRIM (TO_CHAR (lmode, '990'))

) lmode,

DECODE (m.request,

0, 'None',

1, 'Null',

2, 'Row Share',

3, 'Row Excl.',

4, 'Share',

5, 'S/Row Excl.',

6, 'Exclusive',

request, LTRIM (TO_CHAR (m.request, '990'))

) request,

m.id1, m.id2

FROM v$session sn, v$lock m

WHERE (sn.SID = m.SID AND m.request != 0) --存在鎖請求,即被阻塞

OR ( sn.SID = m.SID --不存在鎖請求,但是鎖定的對象被其他會話請求鎖定

AND m.request = 0

AND lmode != 4

AND (id1, id2) IN (

SELECT s.id1, s.id2

FROM v$lock s

WHERE request != 0 AND s.id1 = m.id1

AND s.id2 = m.id2)

)

ORDER BY id1, id2, m.request;

通過以上查詢知道了sid和 SERIAL#就可以開殺了

alter system kill session 'sid,SERIAL#';
怎麼知道資料庫表已經鎖表了
通過查詢結果中的object_id,可以查詢到具體被鎖的對象再給你看看我查到的一些關於鎖的資料:鎖有以下幾種模式: 0:none 1:null 空 2:Row-S 行共享(RS):共享表鎖 3:Row-X 行專用(RX):用於行的修改 4:Share 共享鎖(S):阻止其他DML操作 5:S/Row-X 共享行專用(SRX):阻止其他事務操作 6:exclusive 專用(X):獨立訪問使用數字越大鎖級別越高, 影響的操作越多。一般的查詢語句如select ... from ... ;是小於2的鎖, 有時會在v$locked_object出現。 select ... from ... for update; 是2的鎖。當對話使用for update子串打開一個游標時,所有返回集中的數據行都將處於行級(Row-X)獨占式鎖定,其他對象只能查詢這些數據行,不能進行update、delete或select...for update操作。 insert / update / delete ... ; 是3的鎖。沒有mit之前插入同樣的一條記錄會沒有反應, 因為後一個3的鎖會一直等待上一個3的鎖, 我們必須釋放掉上一個才能繼續工作。創建索引的時候也會產生3,4級別的鎖。 locked_mode為2,3,4不影響DML(insert,delete,update,select)操作, 但DDL(alter,drop等)操作會提示ora-00054錯誤。有主外鍵約束時 update / delete ... ; 可能會產生4,5的鎖。 DDL語句時是6的鎖。以DBA角色, 查看當前資料庫里鎖的情況可以用如下SQL語句: select object_id,session_id,locked_mode from v$locked_object; select t2.username,t2.sid,t2.serial#,t2.logon_time from v$locked_object t1,v$session t2 where t1.session_id=t2.sid order by t2.logon_time; 如果有長期出現的一列,可能是沒有釋放的鎖。我們可以用下面SQL語句殺掉長期沒有釋放非正常的鎖: alter system kill session 'sid,serial#'; 如果出現了鎖的問題, 某個DML操作可能等待很久沒有反應。當你採用的是直接連接資料庫的方式,也不要用OS系統命令 $kill process_num 或者 $kill -9 process_num來終止用戶連接,因為一個用戶進程可能產生一個以上的鎖, 殺OS進程並不能徹底清除鎖的問題。
如何實現資料庫鎖表及解鎖
鎖表:

LOCK TABLES tablename WRITE;LOCK TABLES tablename READ;解鎖

UNLOCK TABLES;
資料庫中如何釋鎖表進程
鎖表查詢的代碼有以下的形式:

select count(*) from v$locked_object;

select * from v$locked_object;

查看哪個表被鎖

select b.owner,b.object_name,a.session_id,a.locked_mode from v$locked_object a,dba_objects b where b.object_id = a.object_id;查看是哪個session引起的

select b.username,b.sid,b.serial#,logon_time from v$locked_object a,v$session b where a.session_id = b.sid order by b.logon_time;殺掉對應進程

執行命令:alter system kill session'1025,41';

其中1025為sid,41為serial#.
怎麼知道資料庫表已經鎖表了
先回答你的問題:

select *from v$locked_object;

可以獲得被鎖的對象的object_id及產生鎖的會話sid。

通過查詢結果中的object_id,可以查詢到具體被鎖的對象

再給你看看我查到的一些關於鎖的資料:

鎖有以下幾種模式:

0:none

1:null 空

2:Row-S 行共享(RS):共享表鎖

3:Row-X 行專用(RX):用於行的修改

4:Share 共享鎖(S):阻止其他DML操作

5:S/Row-X 共享行專用(SRX):阻止其他事務操作

6:exclusive 專用(X):獨立訪問使用

數字越大鎖級別越高, 影響的操作越多。

一般的查詢語句如select ... from ... ;是小於2的鎖, 有時會在v$locked_object出現。

select ... from ... for update; 是2的鎖。

當對話使用for update子串打開一個游標時,

所有返回集中的數據行都將處於行級(Row-X)獨占式鎖定,

其他對象只能查詢這些數據行,不能進行update、delete或select...for update操作。

insert / update / delete ... ; 是3的鎖。

沒有mit之前插入同樣的一條記錄會沒有反應,

因為後一個3的鎖會一直等待上一個3的鎖, 我們必須釋放掉上一個才能繼續工作。

創建索引的時候也會產生3,4級別的鎖。

locked_mode為2,3,4不影響DML(insert,delete,update,select)操作,

但DDL(alter,drop等)操作會提示ora-00054錯誤。

有主外鍵約束時 update / delete ... ; 可能會產生4,5的鎖。

DDL語句時是6的鎖。

以DBA角色, 查看當前資料庫里鎖的情況可以用如下SQL語句:

select object_id,session_id,locked_mode from v$locked_object;

select t2.username,t2.sid,t2.serial#,t2.logon_time

from v$locked_object t1,v$session t2

where t1.session_id=t2.sid order by t2.logon_time;

如果有長期出現的一列,可能是沒有釋放的鎖。

我們可以用下面SQL語句殺掉長期沒有釋放非正常的鎖:

alter system kill session 'sid,serial#';

如果出現了鎖的問題, 某個DML操作可能等待很久沒有反應。

當你採用的是直接連接資料庫的方式,

也不要用OS系統命令 $kill process_num 或者 $kill -9 process_num來終止用戶連接,

因為一個用戶進程可能產生一個以上的鎖, 殺OS進程並不能徹底清除鎖的問題。

記得在資料庫級別用alter system kill session 'sid,serial#';殺掉不正常的鎖。

這里還講了一些:

......>>

『伍』 mysql 多列唯一索引在事務中select for update是不是行鎖

會的,for
update就等於是加上了一個寫鎖,會把表鎖住無法修改

『陸』 mysql死鎖場景整理

本文死鎖場景皆為工作中遇到(或同事遇到)並解決的死鎖場景,寫這篇文章的目的是整理和分享,歡迎指正和補充,本文死鎖悉拿場景包括:

:以下場景隔離級別均為默認的Repeatable Read;

前提 :表 t_user 的 uid 欄位創建了唯一索引,並擁有可更新欄位age。
場景復現

相應業務案例和解決方案
該場景常見於事務中存在for循環更新某條記錄廳桐的情況,死鎖日誌顯示 lock_mode X locks rec but not gap waiting (即行鎖而非間隙鎖),解扮陸坦決方案:

表結構 :

場景復現
首先查詢表中目前存在的記錄:

執行兩個事務的操作:

死鎖原因分析

解決方案

t_user結構改造為:

場景復現操作(幾率不高)

假設存在以下數據

死鎖分析

事務1
① 鎖住zone_id=1對應的間隙鎖: zoneId in (1,2)
② 鎖住索引zone_id=1對應的主鍵索引行鎖id = [1,2]
③ 鎖住uid=1對應的間隙鎖: uid in (1, 2)
④ 鎖住uid=1對應的主鍵索引行鎖: id = [1, 3]
事務2
① 鎖住zone_id=2對應的間隙鎖: zoneId in (1,2)
② 鎖住索引zone_id=2對應的主鍵索引行鎖id = [3,4]
③ 鎖住uid=2對應的間隙鎖: uid in (1, 2)
④ 鎖住uid=2對應的主鍵索引行鎖: id = [2, 4]

解決方案 :創建聯合索引,使執行計劃只會用到一個索引。

測試表結構 :

場景復現操作

解決辦法:盡量避免這種插入又回滾的場景。

避免死鎖的原則:

『柒』 什麼是資料庫索引

第二次回答:
問題補充:能不能具體點,新建一個索引就可以了嗎
基本上可以這么說,不過你也可以修改索引。

記住:
索引其實關鍵目的是為了加快檢索速度而建立的,所以,怎麼用索引是資料庫系統本身的事情,作為資料庫設計或使用者,設計並創建好索引然後體驗加上索引後的查詢變快的感覺就行了。所以,索引怎麼用就變為了「怎麼創建合適的索引」

以下回答是否符合你的要求?你還有什麼問題?

第一次回答:
一、索引是什麼

索引是與表或視圖關聯的磁碟上結構,可以加快從表或視圖中檢索行的速度。索引包含由表或視圖中的一列或多列生成的鍵。這些鍵存儲在一個結構(B 樹)中,使 SQL Server 可以快速有效地查找與鍵值關聯的行。

表或視圖可以包含以下類型的索引:

* 聚集
o 聚集索引根據數據行的鍵值在表或視圖中排序和存儲這些數據行。索引定義中包含聚集索引列。每個表只能有一個聚集索引,因為數據行本身只能按一個順序排序。
o 只有當表包含聚集索引時,表中的數據行才按排序順序存儲。如果表具有聚集索引,則該表稱為聚集表。如果表沒有聚集索引,則其數據行存儲在一個稱為堆的無序結構中。
* 非聚集
o 非聚集索引具有獨立於數據行的結構。非聚集索引包含非聚集索引鍵值,並且每個鍵值項都有指向包含該鍵值的數據行的指針。
o 從非聚集索引中的索引行指向數據行的指針稱為行定位器。行定位器的結構取決於數據頁是存儲在堆中還是聚集表中。對於堆,行定位器是指向行的指針。對於聚集表,行定位器是聚集索引鍵。
o 您可以向非聚集索引的葉級添加非鍵列以跳過現有的索引鍵限制(900 位元組和 16 鍵列),並執行完整范圍內的索引查詢。

聚集索引和非聚集索引都可以是唯一的。這意味著任何兩行都不能有相同的索引鍵值。另外,索引也可以不是唯一的,即多行可以共享同一鍵值。

每當修改了表數據後,都會自動維護表或視圖的索引。

索引和約束

對表列定義了 PRIMARY KEY 約束和 UNIQUE 約束時,會自動創建索引。例如,如果創建了表並將一個特定列標識為主鍵,則 資料庫引擎自動對該列創建 PRIMARY KEY 約束和索引。有關詳細信息,請參閱創建索引(資料庫引擎)。

二、索引有什麼用

與書中的索引一樣,資料庫中的索引使您可以快速找到表或索引視圖中的特定信息。索引包含從表或視圖中一個或多個列生成的鍵,以及映射到指定數據的存儲位置的指針。通過創建設計良好的索引以支持查詢,可以顯著提高資料庫查詢和應用程序的性能。索引可以減少為返回查詢結果集而必須讀取的數據量。索引還可以強製表中的行具有唯一性,從而確保表數據的數據完整性。

設計良好的索引可以減少磁碟 I/O 操作,並且消耗的系統資源也較少,從而可以提高查詢性能。對於包含 SELECT、UPDATE、DELETE 或 MERGE 語句的各種查詢,索引會很有用。例如,在 AdventureWorks 資料庫中執行的查詢 SELECT Title, HireDate FROM HumanResources.Employee WHERE EmployeeID = 250。執行此查詢時,查詢優化器評估可用於檢索數據的每個方法,然後選擇最有效的方法。可能採用的方法包括掃描表和掃描一個或多個索引(如果有)。

掃描表時,查詢優化器讀取表中的所有行,並提取滿足查詢條件的行。掃描表會有許多磁碟 I/O 操作,並佔用大量資源。但是,如果查詢的結果集是占表中較高百分比的行,掃描表會是最為有效的方法。

查詢優化器使用索引時,搜索索引鍵列,查找到查詢所需行的存儲位置,然後從該位置提取匹配行。通常,搜索索引比搜索表要快很多,因為索引與表不同,一般每行包含的列非常少,且行遵循排序順序。

查詢優化器在執行查詢時通常會選擇最有效的方法。但如果沒有索引,則查詢優化器必須掃描表。您的任務是設計並創建最適合您的環境的索引,以便查詢優化器可以從多個有效的索引中選擇。SQL Server 提供的資料庫引擎優化顧問可以幫助分析資料庫環境並選擇適當的索引。

三、索引怎麼用

索引其實關鍵目的是為了加快檢索速度而建立的,所以,怎麼用索引是資料庫系統本身的事情,作為資料庫設計或使用者,設計並創建好索引然後體驗加上索引後的查詢變快的感覺就行了。所以,索引怎麼用就變為了「怎麼創建合適的索引」,以下說明這個問題:

索引設計不佳和缺少索引是提高資料庫和應用程序性能的主要障礙。設計高效的索引對於獲得良好的資料庫和應用程序性能極為重要。為資料庫及其工作負荷選擇正確的索引是一項需要在查詢速度與更新所需開銷之間取得平衡的復雜任務。如果索引較窄,或者說索引關鍵字中只有很少的幾列,則需要的磁碟空間和維護開銷都較少。而另一方面,寬索引可覆蓋更多的查詢。您可能需要試驗若干不同的設計,才能找到最有效的索引。可以添加、修改和刪除索引而不影響資料庫架構或應用程序設計。因此,應試驗多個不同的索引而無需猶豫。

SQL Server 中的查詢優化器可在大多數情況下可靠地選擇最高效的索引。總體索引設計策略應為查詢優化器提供可供選擇的多個索引,並依賴查詢優化器做出正確的決定。這在多種情況下可減少分析時間並獲得良好的性能。若要查看查詢優化器對特定查詢使用的索引,請在 SQL Server Management Studio 中的「查詢」菜單上選擇「包括實際的執行計劃」。

不要總是將索引的使用等同於良好的性能,或者將良好的性能等同於索引的高效使用。如果只要使用索引就能獲得最佳性能,那查詢優化器的工作就簡單了。但事實上,不正確的索引選擇並不能獲得最佳性能。因此,查詢優化器的任務是只在索引或索引組合能提高性能時才選擇它,而在索引檢索有礙性能時則避免使用它。

建議的索引設計策略包括以下任務:

1. 了解資料庫本身的特徵。例如,它是頻繁修改數據的聯機事務處理 (OLTP) 資料庫,還是主要包含只讀數據的決策支持系統 (DSS) 或數據倉庫 (OLAP) 資料庫?
2. 了解最常用的查詢的特徵。例如,了解到最常用的查詢聯接兩個或多個表將有助於決定要使用的最佳索引類型。
3. 了解查詢中使用的列的特徵。例如,某個索引對於含有整數數據類型同時還是唯一的或非空的列是理想索引。篩選索引適用於具有定義完善的數據子集的列。
4. 確定哪些索引選項可在創建或維護索引時提高性能。例如,對現有某個大型表創建聚集索引將會受益於 ONLINE 索引選項。ONLINE 選項允許在創建索引或重新生成索引時繼續對基礎數據執行並發活動。
5. 確定索引的最佳存儲位置。非聚集索引可以與基礎表存儲在同一個文件組中,也可以存儲在不同的文件組中。索引的存儲位置可通過提高磁碟 I/O 性能來提高查詢性能。例如,將非聚集索引存儲在表文件組所在磁碟以外的某個磁碟上的一個文件組中可以提高性能,因為可以同時讀取多個磁碟。
或者,聚集索引和非聚集索引也可以使用跨越多個文件組的分區方案。在維護整個集合的完整性時,使用分區可以快速而有效地訪問或管理數據子集,從而使大型表或索引更易於管理。有關詳細信息,請參閱已分區表和已分區索引。在考慮分區時,應確定是否應對齊索引,即,是按實質上與表相同的方式進行分區,還是單獨分區。

# 設計索引。
索引設計是一項關鍵任務。索引設計包括確定要使用的列,選擇索引類型(例如聚集或非聚集),選擇適當的索引選項,以及確定文件組或分區方案布置。

# 確定最佳的創建方法。按照以下方法創建索引:

* 使用 CREATE TABLE 或 ALTER TABLE 對列定義 PRIMARY KEY 或 UNIQUE 約束
SQL Server 資料庫引擎自動創建唯一索引來強制 PRIMARY KEY 或 UNIQUE 約束的唯一性要求。默認情況下,創建的唯一聚集索引可以強制 PRIMARY KEY 約束,除非表中已存在聚集索引或指定了唯一的非聚集索引。默認情況下,創建的唯一非聚集索引可以強制 UNIQUE 約束,除非已明確指定唯一的聚集索引且表中不存在聚集索引。
還可以指定索引選項和索引位置、文件組或分區方案。
創建為 PRIMARY KEY 或 UNIQUE 約束的一部分的索引將自動給定與約束名稱相同的名稱。

* 使用 CREATE INDEX 語句或 SQL Server Management Studio 對象資源管理器中的「新建索引」對話框創建獨立於約束的索引
必須指定索引的名稱、表以及應用該索引的列。還可以指定索引選項和索引位置、文件組或分區方案。默認情況下,如果未指定聚集或唯一選項,將創建非聚集的非唯一索引。若要創建篩選索引,請使用可選的 WHERE 子句。

# 創建索引。
要考慮的一個重要因素是對空表還是對包含數據的表創建索引。對空表創建索引在創建索引時不會對性能產生任何影響,而向表中添加數據時,會對性能產生影響。
對大型表創建索引時應仔細計劃,這樣才不會影響資料庫性能。對大型表創建索引的首選方法是先創建聚集索引,然後創建任何非聚集索引。在對現有表創建索引時,請考慮將 ONLINE 選項設置為 ON。該選項設置為 ON 時,將不持有長期表鎖以繼續對基礎表的查詢或更新。

簡單的創建索引,可採用如下語句:
CREATE INDEX IX_ProctVendor_VendorID
ON Purchasing.ProctVendor (VendorID, VendorName);
GO

『捌』 使用SQL Server資料庫,如何對數據項加S鎖或X鎖呢

鎖的概述

一. 為什麼要引入鎖

多個用戶同時對資料庫的並發操作時會帶來以下數據不一致的問題:

丟失更新
A,B兩個用戶讀同一數據並進行修改,其中一個用戶的修改結果破壞了另一個修改的結果,比如訂票系統

臟讀
A用戶修改了數據,隨後B用戶又讀出該數據,但A用戶因為某些原因取消了對數據的修改,數據恢復原值,此時B得到的數據就與資料庫內的數據產生了不一致

不可重復讀
A用戶讀取數據,隨後B用戶讀出該數據並修改,此時A用戶再讀取數據時發現前後兩次的值不一致

並發控制的主要方法是封鎖,鎖就是在一段時間內禁止用戶做某些操作以避免產生數據不一致

二 鎖的分類

鎖的類別有兩種分法:

1. 從資料庫系統的角度來看:分為獨占鎖(即排它鎖),共享鎖和更新鎖

MS-SQL Server 使用以下資源鎖模式。

鎖模式 描述
共享 (S) 用於不更改或不更新數據的操作(只讀操作),如 SELECT 語句。
更新 (U) 用於可更新的資源中。防止當多個會話在讀取、鎖定以及隨後可能進行的資源更新時發生常見形式的死鎖。
排它 (X) 用於數據修改操作,例如 INSERT、UPDATE 或 DELETE。確保不會同時同一資源進行多重更新。
意向鎖 用於建立鎖的層次結構。意向鎖的類型為:意向共享 (IS)、意向排它 (IX) 以及與意向排它共享 (SIX)。
架構鎖 在執行依賴於表架構的操作時使用。架構鎖的類型為:架構修改 (Sch-M) 和架構穩定性 (Sch-S)。
大容量更新 (BU) 向表中大容量復制數據並指定了 TABLOCK 提示時使用。

共享鎖
共享 (S) 鎖允許並發事務讀取 (SELECT) 一個資源。資源上存在共享 (S) 鎖時,任何其它事務都不能修改數據。一旦已經讀取數據,便立即釋放資源上的共享 (S) 鎖,除非將事務隔離級別設置為可重復讀或更高級別,或者在事務生存周期內用鎖定提示保留共享 (S) 鎖。

更新鎖
更新 (U) 鎖可以防止通常形式的死鎖。一般更新模式由一個事務組成,此事務讀取記錄,獲取資源(頁或行)的共享 (S) 鎖,然後修改行,此操作要求鎖轉換為排它 (X) 鎖。如果兩個事務獲得了資源上的共享模式鎖,然後試圖同時更新數據,則一個事務嘗試將鎖轉換為排它 (X) 鎖。共享模式到排它鎖的轉換必須等待一段時間,因為一個事務的排它鎖與其它事務的共享模式鎖不兼容;發生鎖等待。第二個事務試圖獲取排它 (X) 鎖以進行更新。由於兩個事務都要轉換為排它 (X) 鎖,並且每個事務都等待另一個事務釋放共享模式鎖,因此發生死鎖。

若要避免這種潛在的死鎖問題,請使用更新 (U) 鎖。一次只有一個事務可以獲得資源的更新 (U) 鎖。如果事務修改資源,則更新 (U) 鎖轉換為排它 (X) 鎖。否則,鎖轉換為共享鎖。

排它鎖
排它 (X) 鎖可以防止並發事務對資源進行訪問。其它事務不能讀取或修改排它 (X) 鎖鎖定的數據。

意向鎖
意向鎖表示 SQL Server 需要在層次結構中的某些底層資源上獲取共享 (S) 鎖或排它 (X) 鎖。例如,放置在表級的共享意向鎖表示事務打算在表中的頁或行上放置共享 (S) 鎖。在表級設置意向鎖可防止另一個事務隨後在包含那一頁的表上獲取排它 (X) 鎖。意向鎖可以提高性能,因為 SQL Server 僅在表級檢查意向鎖來確定事務是否可以安全地獲取該表上的鎖。而無須檢查表中的每行或每頁上的鎖以確定事務是否可以鎖定整個表。

意向鎖包括意向共享 (IS)、意向排它 (IX) 以及與意向排它共享 (SIX)。

鎖模式 描述
意向共享 (IS) 通過在各資源上放置 S 鎖,表明事務的意向是讀取層次結構中的部分(而不是全部)底層資源。
意向排它 (IX) 通過在各資源上放置 X 鎖,表明事務的意向是修改層次結構中的部分(而不是全部)底層資源。IX 是 IS 的超集。
與意向排它共享 (SIX) 通過在各資源上放置 IX 鎖,表明事務的意向是讀取層次結構中的全部底層資源並修改部分(而不是全部)底層資源。允許頂層資源上的並發 IS 鎖。例如,表的 SIX 鎖在表上放置一個 SIX 鎖(允許並發 IS 鎖),在當前所修改頁上放置 IX 鎖(在已修改行上放置 X 鎖)。雖然每個資源在一段時間內只能有一個 SIX 鎖,以防止其它事務對資源進行更新,但是其它事務可以通過獲取表級的 IS 鎖來讀取層次結構中的底層資源。

獨占鎖:只允許進行鎖定操作的程序使用,其他任何對他的操作均不會被接受。執行數據更新命令時,SQL Server會自動使用獨占鎖。當對象上有其他鎖存在時,無法對其加獨占鎖。
共享鎖:共享鎖鎖定的資源可以被其他用戶讀取,但其他用戶無法修改它,在執行Select時,SQL Server會對對象加共享鎖。
更新鎖:當SQL Server准備更新數據時,它首先對數據對象作更新鎖鎖定,這樣數據將不能被修改,但可以讀取。等到SQL Server確定要進行更新數據操作時,他會自動將更新鎖換為獨占鎖,當對象上有其他鎖存在時,無法對其加更新鎖。

2. 從程序員的角度看:分為樂觀鎖和悲觀鎖。
樂觀鎖:完全依靠資料庫來管理鎖的工作。
悲觀鎖:程序員自己管理數據或對象上的鎖處理。

MS-SQLSERVER 使用鎖在多個同時在資料庫內執行修改的用戶間實現悲觀並發控制

三 鎖的粒度
鎖粒度是被封鎖目標的大小,封鎖粒度小則並發性高,但開銷大,封鎖粒度大則並發性低但開銷小

SQL Server支持的鎖粒度可以分為為行、頁、鍵、鍵范圍、索引、表或資料庫獲取鎖

資源 描述
RID 行標識符。用於單獨鎖定表中的一行。
鍵 索引中的行鎖。用於保護可串列事務中的鍵范圍。
頁 8 千位元組 (KB) 的數據頁或索引頁。
擴展盤區 相鄰的八個數據頁或索引頁構成的一組。
表 包括所有數據和索引在內的整個表。
DB 資料庫。

四 鎖定時間的長短

鎖保持的時間長度為保護所請求級別上的資源所需的時間長度。

用於保護讀取操作的共享鎖的保持時間取決於事務隔離級別。採用 READ COMMITTED 的默認事務隔離級別時,只在讀取頁的期間內控制共享鎖。在掃描中,直到在掃描內的下一頁上獲取鎖時才釋放鎖。如果指定 HOLDLOCK 提示或者將事務隔離級別設置為 REPEATABLE READ 或 SERIALIZABLE,則直到事務結束才釋放鎖。

根據為游標設置的並發選項,游標可以獲取共享模式的滾動鎖以保護提取。當需要滾動鎖時,直到下一次提取或關閉游標(以先發生者為准)時才釋放滾動鎖。但是,如果指定 HOLDLOCK,則直到事務結束才釋放滾動鎖。

用於保護更新的排它鎖將直到事務結束才釋放。
如果一個連接試圖獲取一個鎖,而該鎖與另一個連接所控制的鎖沖突,則試圖獲取鎖的連接將一直阻塞到:

將沖突鎖釋放而且連接獲取了所請求的鎖。

連接的超時間隔已到期。默認情況下沒有超時間隔,但是一些應用程序設置超時間隔以防止無限期等待

五 SQL Server 中鎖的自定義

1 處理死鎖和設置死鎖優先順序

死鎖就是多個用戶申請不同封鎖,由於申請者均擁有一部分封鎖權而又等待其他用戶擁有的部分封鎖而引起的無休止的等待

可以使用SET DEADLOCK_PRIORITY控制在發生死鎖情況時會話的反應方式。如果兩個進程都鎖定數據,並且直到其它進程釋放自己的鎖時,每個進程才能釋放自己的鎖,即發生死鎖情況。

2 處理超時和設置鎖超時持續時間。

@@LOCK_TIMEOUT 返回當前會話的當前鎖超時設置,單位為毫秒

SET LOCK_TIMEOUT 設置允許應用程序設置語句等待阻塞資源的最長時間。當語句等待的時間大於 LOCK_TIMEOUT 設置時,系統將自動取消阻塞的語句,並給應用程序返回"已超過了鎖請求超時時段"的 1222 號錯誤信息

示例
下例將鎖超時期限設置為 1,800 毫秒。
SET LOCK_TIMEOUT 1800

3) 設置事務隔離級別。

4 ) 對 SELECT、INSERT、UPDATE 和 DELETE 語句使用表級鎖定提示。

5) 配置索引的鎖定粒度
可以使用 sp_indexoption 系統存儲過程來設置用於索引的鎖定粒度

六 查看鎖的信息

1 執行 EXEC SP_LOCK 報告有關鎖的信息
2 查詢分析器中按Ctrl+2可以看到鎖的信息

七 使用注意事項

如何避免死鎖
1 使用事務時,盡量縮短事務的邏輯處理過程,及早提交或回滾事務;
2 設置死鎖超時參數為合理范圍,如:3分鍾-10分種;超過時間,自動放棄本次操作,避免進程懸掛;
3 優化程序,檢查並避免死鎖現象出現;
4 .對所有的腳本和SP都要仔細測試,在正是版本之前。
5 所有的SP都要有錯誤處理(通過@error)
6 一般不要修改SQL SERVER事務的默認級別。不推薦強行加鎖

解決問題 如何對行 表 資料庫加鎖

八 幾個有關鎖的問題

1 如何鎖一個表的某一行

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT * FROM table ROWLOCK WHERE id = 1

2 鎖定資料庫的一個表

SELECT * FROM table WITH (HOLDLOCK)

加鎖語句:
sybase:
update 表 set col1=col1 where 1=0 ;
MSSQL:
select col1 from 表 (tablockx) where 1=0 ;
oracle:
LOCK TABLE 表 IN EXCLUSIVE MODE ;
加鎖後其它人不可操作,直到加鎖用戶解鎖,用commit或rollback解鎖

幾個例子幫助大家加深印象
設table1(A,B,C)
A B C
a1 b1 c1
a2 b2 c2
a3 b3 c3

1)排它鎖
新建兩個連接
在第一個連接中執行以下語句
begin tran
update table1
set A='aa'
where B='b2'
waitfor delay '00:00:30' --等待30秒
commit tran
在第二個連接中執行以下語句
begin tran
select * from table1
where B='b2'
commit tran

若同時執行上述兩個語句,則select查詢必須等待update執行完畢才能執行即要等待30秒

2)共享鎖
在第一個連接中執行以下語句
begin tran
select * from table1 holdlock -holdlock人為加鎖
where B='b2'
waitfor delay '00:00:30' --等待30秒
commit tran

在第二個連接中執行以下語句
begin tran
select A,C from table1
where B='b2'
update table1
set A='aa'
where B='b2'
commit tran

若同時執行上述兩個語句,則第二個連接中的select查詢可以執行
而update必須等待第一個事務釋放共享鎖轉為排它鎖後才能執行 即要等待30秒

3)死鎖
增設table2(D,E)
D E
d1 e1
d2 e2
在第一個連接中執行以下語句
begin tran
update table1
set A='aa'
where B='b2'
waitfor delay '00:00:30'
update table2
set D='d5'
where E='e1'
commit tran

在第二個連接中執行以下語句
begin tran
update table2
set D='d5'
where E='e1'
waitfor delay '00:00:10'
update table1
set A='aa'
where B='b2'
commit tran

同時執行,系統會檢測出死鎖,並中止進程

補充一點:
Sql Server2000支持的表級鎖定提示

HOLDLOCK 持有共享鎖,直到整個事務完成,應該在被鎖對象不需要時立即釋放,等於SERIALIZABLE事務隔離級別

NOLOCK 語句執行時不發出共享鎖,允許臟讀 ,等於 READ UNCOMMITTED事務隔離級別

PAGLOCK 在使用一個表鎖的地方用多個頁鎖

READPAST 讓sql server跳過任何鎖定行,執行事務,適用於READ UNCOMMITTED事務隔離級別只跳過RID鎖,不跳過頁,區域和表鎖

ROWLOCK 強制使用行鎖

TABLOCKX 強制使用獨占表級鎖,這個鎖在事務期間阻止任何其他事務使用這個表

UPLOCK 強制在讀表時使用更新而不用共享鎖

應用程序鎖:
應用程序鎖就是客戶端代碼生成的鎖,而不是sql server本身生成的鎖

處理應用程序鎖的兩個過程

sp_getapplock 鎖定應用程序資源

sp_releaseapplock 為應用程序資源解鎖

注意: 鎖定資料庫的一個表的區別

SELECT * FROM table WITH (HOLDLOCK) 其他事務可以讀取表,但不能更新刪除

SELECT * FROM table WITH (TABLOCKX) 其他事務不能讀取表,更新和刪除

『玖』 資料庫事務的四個隔離級別,mysql在哪一個級別

術式之後皆為邏輯,一切皆為需求和實現。希望此文能從需求、現狀和解決方式的角度幫大家理解隔離級別。


隔離級別的產生

在串型執行的條件下,數據修改的順序是固定的、可預期的結果,但是並發執行的情況下,數據的修改是不可預期的,也不固定,為了實現數據修改在並發執行的情況下得到一個固定、可預期的結果,由此產生了隔離級別。

所以隔離級別的作用是用來平衡資料庫並發訪問與數據一致性的方法。


事務的4種隔離級別

READ UNCOMMITTED 行豎 未提交讀,可以讀取未提交的數據。READ COMMITTED 已提交讀,對於鎖定讀(select with for update 或者 for share)、update 和 delete 語句, InnoDB 僅鎖定索引記錄,而不鎖定它們之間的間隙,因此允許在鎖定的記錄旁邊自由插入新記錄。 Gap locking 僅用於外鍵約束檢查和重復鍵檢查。REPEATABLE READ 可重復讀,事務中的一致性讀取讀取的是事務第一次讀取所建立的快照。SERIALIZABLE 序列化

在了解了 4 種隔離級別的需求後,在採用鎖控制隔離級別的基礎上,我們需要了解加鎖的對象(數據本身&間隙),以及了解整個數據范圍的全集組成。


數據范圍全集組成

SQL 語句根據條件判斷不需要掃描的數據范圍(不加鎖);

SQL 語句根據條件掃描到的可能需要加鎖的數據范圍;

以單個數據范圍為例,數據范圍全集包含:(數據范圍不一定是連續的值,也可能是間隔的值組成)

1. 數據已經填充了整個數據范圍:(被完全填充的數據范圍,不存在數據間隙)

  • 整形,對值具有唯一約束條件的數據范圍 1~5 ,

    已有數據1、2、3、4、5,此時數據范圍已被完全填充;

  • 整形,對值具有唯一約束條件的數據范圍 1 和 5 ,

    已有數據1、5,此時數據范圍已被完全填充;

  • 2. 數據填充了部分數據范圍:(未被完全填充的數據范圍,是存在數據間隙)

  • 整形的數據范圍 1~5 ,

    已有數據 1、2、3、4、5,但是因為沒有唯一約束,

    所以數據范圍可以繼續被 1~5 的數據重復填充;

  • 整形,具有唯一約束條件的數據范圍 1~5 ,

    已有數據 2,5,此時數據范圍未被完全填充,還可以填充 1、3、4 ;

  • 3. 數據范圍內沒有任何數據(存在間隙)

    如下:

  • 整形的數據范圍 1~5 ,數據范圍內當前沒有任何數據。

  • 在了解了數據全集的組成後,我們再來看看事務並發時,會帶來的問題。

    無控制的並發所帶來的問題

    並發事務如果不加以控制的話會帶來一些問題,主要包括以下幾種情況。

    1. 范圍內已有數據更改導致的:

  • 更新丟失:當多個事務選擇了同一行,然後基於最初選定的值更新該行時,

    由於每個事物不知道其陸塌他事務的存在,最後的更新就會覆蓋其他事務所做的更新;

  • 臟讀: 一個事務正在對一條記錄做修改,這個事務完成並提交前,這條記錄就處於不一致狀態。

    這時,另外一個事務也來讀取同一條記錄,如果不加控制,

    第二個事務讀取了這些「臟」數據,並據此做了進一步的處理,就會產生提交的數據依賴關系。

    這種現象就叫「臟讀」。

  • 2. 范圍內數據量發生了變化導致:

  • 不可重復讀:一個事早帶圓務在讀取某些數據後的某個時間,再次讀取以前讀過的數據,

    卻發現其讀出的數據已經發生了改變,或者某些記錄已經被刪除了。

    這種現象就叫「不可重復讀」。

  • 幻讀:一個事務按相同的查詢條件重新讀取以前檢索過的數據,

    卻發現其他事務插入了滿足其查詢條件的新數據,這種現象稱為「幻讀」。

    可以簡單的認為滿足條件的數據量變化了。

  • 因為無控制的並發會帶來一系列的問題,這些問題會導致無法滿足我們所需要的結果。因此我們需要控制並發,以實現我們所期望的結果(隔離級別)。

    MySQL 隔離級別的實現

    InnoDB 通過加鎖的策略來支持這些隔離級別。

    行鎖包含:

  • Record Locks

    索引記錄鎖,索引記錄鎖始終鎖定索引記錄,即使表中未定義索引,

    這種情況下,InnoDB 創建一個隱藏的聚簇索引,並使用該索引進行記錄鎖定。

  • Gap Locks

    間隙鎖是索引記錄之間的間隙上的鎖,或者對第一條記錄之前或者最後一條記錄之後的鎖。

    間隙鎖是性能和並發之間權衡的一部分。

    對於無間隙的數據范圍不需要間隙鎖,因為沒有間隙。

  • Next-Key Locks

    索引記錄上的記錄鎖和索引記錄之前的 gap lock 的組合。

    假設索引包含 10、11、13 和 20。

    可能的next-key locks包括以下間隔,其中圓括弧表示不包含間隔端點,方括弧表示包含端點:

  • (負無窮大, 10] (10, 11] (11, 13] (13, 20] (20, 正無窮大) 對於最後一個間隔,next-key將會鎖定索引中最大值的上方,


  • 左右滑動進行查看

    "上確界"偽記錄的值高於索引中任何實際值。

    上確界不是一個真正的索引記錄,因此,實際上,這個 next-key 只鎖定最大索引值之後的間隙。

    基於此,當獲取的數據范圍中,數據已填充了所有的數據范圍,那麼此時是不存在間隙的,也就不需要 gap lock。

    對於數據范圍內存在間隙的,需要根據隔離級別確認是否對間隙加鎖。

    默認的 REPEATABLE READ 隔離級別,為了保證可重復讀,除了對數據本身加鎖以外,還需要對數據間隙加鎖。

    READ COMMITTED 已提交讀,不匹配行的記錄鎖在 MySQL 評估了 where 條件後釋放。

    對於 update 語句,InnoDB 執行 "semi-consistent" 讀取,這樣它會將最新提交的版本返回到 MySQL,

    以便 MySQL 可以確定該行是否與 update 的 where 條件相匹配。

    總結&延展:

    唯一索引存在唯一約束,所以變更後的數據若違反了唯一約束的原則,則會失敗。

    當 where 條件使用二級索引篩選數據時,會對二級索引命中的條目和對應的聚簇索引都加鎖;所以其他事務變更命中加鎖的聚簇索引時,都會等待鎖。

    行鎖的增加是一行一行增加的,所以可能導致並發情況下死鎖的發生。

    例如,

    在 session A 對符合條件的某聚簇索引加鎖時,可能 session B 已持有該聚簇索引的 Record Locks,而 session B 正在等待 session A 已持有的某聚簇索引的 Record Locks。

    session A 和 session B 是通過兩個不相乾的二級索引定位到的聚簇索引。

    session A 通過索引 idA,session B通過索引 idB 。

    當 where 條件獲取的數據無間隙時,無論隔離級別為 rc 或 rr,都不會存在間隙鎖。

    比如通過唯一索引獲取到了已完全填充的數據范圍,此時不需要間隙鎖。

    間隙鎖的目的在於阻止數據插入間隙,所以無論是通過 insert 或 update 變更導致的間隙內數據的存在,都會被阻止。

    rc 隔離級別模式下,查詢和索引掃描將禁用 gap locking,此時 gap locking 僅用於外鍵約束檢查和重復鍵檢查(主要是唯一性檢查)。

    rr 模式下,為了防止幻讀,會加上 Gap Locks。

    事務中,SQL 開始則加鎖,事務結束才釋放鎖。

    就鎖類型而言,應該有優化鎖,鎖升級等,例如rr模式未使用索引查詢的情況下,是否可以直接升級為表鎖。

    就鎖的應用場景而言,在回放場景中,如果確定事務可並發,則可以考慮不加鎖,加快回放速度。

    鎖只是並發控制的一種粒度,只是一個很小的部分:

    從不同場景下是否需要控制並發,(已知無交集且有序的數據的變更,MySQL 的 MTS 相同前置事務的多事務並發回放)

    並發控制的粒度,(鎖是一種邏輯粒度,可能還存在物理層和其他邏輯粒度或方式)

    相同粒度下的優化,(鎖本身存在優化,如IX、IS類型的優化鎖)

    粒度載入的安全&性能(如獲取行鎖前,先獲取頁鎖,頁鎖在執行獲取行鎖操作後即釋放,無論是否獲取成功)等多個層次去思考並發這玩意。

『拾』 MySQL從入門到精通(九) MySQL鎖,各種鎖

鎖是計算機協調多個進程或線程並發訪問某一資源的機制,在資料庫中,除傳統的計算資源(CPU、RAM、I/O)爭用外,數據也是一種供許多用戶共享的資源,如何保證數據並發訪問的一致性,有效性是所有資料庫必須解決的一個問題,鎖沖突也是影響資料庫並發訪問性能的一個重要因素,從這個角度來說,鎖對資料庫而言是尤其重要,也更加復雜。MySQL中的鎖,按照鎖的粒度分為:1、全局鎖,就鎖定資料庫中的所有表。2、表級鎖,每次操作鎖住整張表。3、行級鎖,每次操作鎖住對應的行數據。

全局鎖就是對整個資料庫實例加鎖,加鎖後整個實例就處於只讀狀態,後續的DML的寫語句,DDL語句,已經更新操作的事務提交語句都將阻塞。其典型的使用場景就是做全庫的邏輯備份,對所有的表進行鎖定,從而獲取一致性視圖,保證數據的完整性。但是對資料庫加全局鎖是有弊端的,如在主庫上備份,那麼在備份期間都不能執行更新,業務會受影響,第二如果是在從庫上備份,那麼在備份期間從庫不能執行主庫同步過來的二進制日誌,會導致主從延遲。

解決辦法是在innodb引擎中,備份時加上--single-transaction參數來完成不加鎖的一致性數據備份。

添加全局鎖: flush tables with read lock; 解鎖 unlock tables。

表級鎖,每次操作會鎖住整張表.鎖定粒度大,發送鎖沖突的概率最高,並發讀最低,應用在myisam、innodb、BOB等存儲引擎中。表級鎖分為: 表鎖、元數據鎖(meta data lock, MDL)和意向鎖。

表鎖又分為: 表共享讀鎖 read lock、表獨占寫鎖write lock

語法: 1、加鎖 lock tables 表名 ... read/write

2、釋放鎖 unlock tables 或者關閉客戶端連接

注意: 讀鎖不會阻塞其它客戶端的讀,但是會阻塞其它客戶端的寫,寫鎖既會阻塞其它客戶端的讀,又會阻塞其它客戶端的寫。大家可以拿一張表來測試看看。

元數據鎖,在加鎖過程中是系統自動控制的,無需顯示使用,在訪問一張表的時候會自動加上,MDL鎖主要作用是維護表元數據的數據一致性,在表上有活動事務的時候,不可以對元數據進行寫入操作。為了避免DML和DDL沖突,保證讀寫的正確性。

在MySQL5.5中引入了MDL,當對一張表進行增刪改查的時候,加MDL讀鎖(共享);當對表結構進行變更操作時,加MDL寫鎖(排他).

查看元數據鎖:

select object_type,object_schema,object_name,lock_type,lock_ration from performance_schema_metadata_locks;

意向鎖,為了避免DML在執行時,加的行鎖與表鎖的沖突,在innodb中引入了意向鎖,使得表鎖不用檢查每行數據是否加鎖,使用意向鎖來減少表鎖的檢查。意向鎖分為,意向共享鎖is由語句select ... lock in share mode添加。意向排他鎖ix,由insert,update,delete,select。。。for update 添加。

select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_lock;

行級鎖,每次操作鎖住對應的行數據,鎖定粒度最小,發生鎖沖突的概率最高,並發讀最高,應用在innodb存儲引擎中。

innodb的數據是基於索引組織的,行鎖是通過對索引上的索引項加鎖來實現的,而不是對記錄加的鎖,對於行級鎖,主要分為以下三類:

1、行鎖或者叫record lock記錄鎖,鎖定單個行記錄的鎖,防止其他事物對次行進行update和delete操作,在RC,RR隔離級別下都支持。

2、間隙鎖Gap lock,鎖定索引記錄間隙(不含該記錄),確保索引記錄間隙不變,防止其他事物在這個間隙進行insert操作,產生幻讀,在RR隔離級別下都支持。

3、臨鍵鎖Next-key-lock,行鎖和間隙鎖組合,同時鎖住數據,並鎖住數據前面的間隙Gap,在RR隔離級別下支持。

innodb實現了以下兩種類型的行鎖

1、共享鎖 S: 允許一個事務去讀一行,阻止其他事務獲得相同數據集的排他鎖。

2、排他鎖 X: 允許獲取排他鎖的事務更新數據,阻止其他事務獲得相同數據集的共享鎖和排他鎖。

insert 語句 排他鎖 自動添加的

update語句 排他鎖 自動添加

delete 語句 排他鎖 自動添加

select 正常查詢語句 不加鎖 。。。

select 。。。lock in share mode 共享鎖 需要手動在select 之後加lock in share mode

select 。。。for update 排他鎖 需要手動在select之後添加for update

默認情況下,innodb在repeatable read事務隔離級別運行,innodb使用next-key鎖進行搜索和索引掃描,以防止幻讀。

間隙鎖唯一目的是防止其它事務插入間隙,間隙鎖可以共存,一個事務採用的間隙鎖不會阻止另一個事務在同一間隙上採用的間隙鎖。