① innodb 為什麼給索引加鎖
mysql 不同的存儲引擎表示對應的不同的鎖機制，如MyISAM和MEMORY存儲引擎採用的是表級鎖（table-level locking）；BDB存儲引擎採用的是頁面鎖（page-level locking），但也支持表級鎖；InnoDB存儲引擎既支持行級鎖（row-level locking），也支持表級鎖，但默認情況下是採用行級鎖。
② 關於MySQL中的表鎖和行鎖
mysql行鎖和表鎖
鎖是計算機協調多個進程或純線程並發訪問某一資源的機制。在資料庫中，除傳統的計算資源（CPU、RAM、I/O）的爭用以外，數據也是一種供許多用戶共享的資源。如何保證數據並發訪問的一致性、有效性是所在有資料庫必須解決的一個問題，鎖沖突也是影響資料庫並發訪問性能的一個重要因素。從這個角度來說，鎖對資料庫而言顯得尤其重要，也更加復雜。
概述
相對其他資料庫而言，MySQL的鎖機制比較簡單，其最顯著的特點是不同的存儲引擎支持不同的鎖機制。
MySQL大致可歸納為以下3種鎖：
表級鎖：開銷小，加鎖快；不會出現死鎖；鎖定粒度大，發生鎖沖突的概率最高，並發度最低。
行級鎖：開銷大，加鎖慢；會出現死鎖；鎖定粒度最小，發生鎖沖突的概率最低，並發度也最高。
頁面鎖：開銷和加鎖時間界於表鎖和行鎖之間；會出現死鎖；鎖定粒度界於表鎖和行鎖之間，並發度一般
MySQL表級鎖的鎖模式（MyISAM)
MySQL表級鎖有兩種模式：表共享鎖（Table Read Lock）和表獨占寫鎖（Table Write Lock）。
對MyISAM的讀操作，不會阻塞其他用戶對同一表請求，但會阻塞對同一表的寫請求；
對MyISAM的寫操作，則會阻塞其他用戶對同一表的讀和寫操作；
MyISAM表的讀操作和寫操作之間，以及寫操作之間是串列的。
上面的例子在LOCK TABLES時加了『local』選項，其作用就是在滿足MyISAM表並發插入條件的情況下，允許其他用戶在表尾插入記錄
在用LOCKTABLES給表顯式加表鎖是時，必須同時取得所有涉及表的鎖，並且MySQL支持鎖升級。也就是說，在執行LOCK TABLES後，只能訪問顯式加鎖的這些表，不能訪問未加鎖的表；同時，如果加的是讀鎖，那麼只能執行查詢操作，而不能執行更新操作。其實，在自動加鎖的情況下也基本如此，MySQL問題一次獲得SQL語句所需要的全部鎖。這也正是MyISAM表不會出現死鎖（Deadlock Free）的原因
當concurrent_insert設置為0時，不允許並發插入。
當concurrent_insert設置為1時，如果MyISAM允許在一個讀表的同時，另一個進程從表尾插入記錄。這也是MySQL的默認設置。
當concurrent_insert設置為2時，無論MyISAM表中有沒有空洞，都允許在表尾插入記錄，都允許在表尾並發插入記錄。
通過指定啟動參數low-priority-updates，使MyISAM引擎默認給予讀請求以優先的權利。
通過執行命令SET LOW_PRIORITY_UPDATES=1，使該連接發出的更新請求優先順序降低。
通過指定INSERT、UPDATE、DELETE語句的LOW_PRIORITY屬性，降低該語句的優先順序。
原性性（Actomicity）：事務是一個原子操作單元，其對數據的修改，要麼全都執行，要麼全都不執行。
一致性（Consistent）：在事務開始和完成時，數據都必須保持一致狀態。這意味著所有相關的數據規則都必須應用於事務的修改，以操持完整性；事務結束時，所有的內部數據結構（如B樹索引或雙向鏈表）也都必須是正確的。
隔離性（Isolation）：資料庫系統提供一定的隔離機制，保證事務在不受外部並發操作影響的「獨立」環境執行。這意味著事務處理過程中的中間狀態對外部是不可見的，反之亦然。
持久性（Durable）：事務完成之後，它對於數據的修改是永久性的，即使出現系統故障也能夠保持。
更新丟失（Lost Update）：當兩個或多個事務選擇同一行，然後基於最初選定的值更新該行時，由於每個事務都不知道其他事務的存在，就會發生丟失更新問題——最後的更新覆蓋了其他事務所做的更新。例如，兩個編輯人員製作了同一文檔的電子副本。每個編輯人員獨立地更改其副本，然後保存更改後的副本，這樣就覆蓋了原始文檔。最後保存其更改保存其更改副本的編輯人員覆蓋另一個編輯人員所做的修改。如果在一個編輯人員完成並提交事務之前，另一個編輯人員不能訪問同一文件，則可避免此問題
臟讀（Dirty Reads）：一個事務正在對一條記錄做修改，在這個事務並提交前，這條記錄的數據就處於不一致狀態；這時，另一個事務也來讀取同一條記錄，如果不加控制，第二個事務讀取了這些「臟」的數據，並據此做進一步的處理，就會產生未提交的數據依賴關系。這種現象被形象地叫做「臟讀」。
不可重復讀（Non-Repeatable Reads）：一個事務在讀取某些數據已經發生了改變、或某些記錄已經被刪除了！這種現象叫做「不可重復讀」。
幻讀（Phantom Reads）：一個事務按相同的查詢條件重新讀取以前檢索過的數據，卻發現其他事務插入了滿足其查詢條件的新數據，這種現象就稱為「幻讀」。
最低級別，只能保證不讀取物理上損壞的數據 是 是 是
共享鎖（s）：允許一個事務去讀一行，阻止其他事務獲得相同數據集的排他鎖。
排他鎖（X）：允許獲取排他鎖的事務更新數據，阻止其他事務取得相同的數據集共享讀鎖和排他寫鎖。
第一種情況是：事務需要更新大部分或全部數據，表又比較大，如果使用默認的行鎖，不僅這個事務執行效率低，而且可能造成其他事務長時間鎖等待和鎖沖突，這種情況下可以考慮使用表鎖來提高該事務的執行速度。
第二種情況是：事務涉及多個表，比較復雜，很可能引起死鎖，造成大量事務回滾。這種情況也可以考慮一次性鎖定事務涉及的表，從而避免死鎖、減少資料庫因事務回滾帶來的開銷。
盡量使用較低的隔離級別
精心設計索引，並盡量使用索引訪問數據，使加鎖更精確，從而減少鎖沖突的機會。
選擇合理的事務大小，小事務發生鎖沖突的幾率也更小。
給記錄集顯示加鎖時，最好一次性請求足夠級別的鎖。比如要修改數據的話，最好直接申請排他鎖，而不是先申請共享鎖，修改時再請求排他鎖，這樣容易產生死鎖。
不同的程序訪問一組表時，應盡量約定以相同的順序訪問各表，對一個表而言，盡可能以固定的順序存取表中的行。這樣可以大減少死鎖的機會。
盡量用相等條件訪問數據，這樣可以避免間隙鎖對並發插入的影響。
不要申請超過實際需要的鎖級別；除非必須，查詢時不要顯示加鎖。
對於一些特定的事務，可以使用表鎖來提高處理速度或減少死鎖的可能
當一個線程獲得對一個表的寫鎖後，只有持有鎖線程可以對表進行更新操作。其他線程的讀、寫操作都會等待，直到鎖被釋放為止。
MySQL表級鎖的鎖模式
MySQL的表鎖有兩種模式：表共享讀鎖（Table Read Lock）和表獨占寫鎖（Table Write Lock）。鎖模式的兼容如下表
MySQL中的表鎖兼容性
當前鎖模式/是否兼容/請求鎖模式
讀鎖 是 是 否
寫鎖 是 否 否
可見，對MyISAM表的讀操作，不會阻塞其他用戶對同一表的讀請求，但會阻塞對同一表的寫請求；對MyISAM表的寫操作，則會阻塞其他用戶對同一表的讀和寫請求；MyISAM表的讀和寫操作之間，以及寫和寫操作之間是串列的！（當一線程獲得對一個表的寫鎖後，只有持有鎖的線程可以對表進行更新操作。其他線程的讀、寫操作都會等待，直到鎖被釋放為止。）
如何加表鎖
MyISAM在執行查詢語句（SELECT）前，會自動給涉及的所有表加讀鎖，在執行更新操作（UPDATE、DELETE、INSERT等）前，會自動給涉及的表加寫鎖，這個過程並不需要用戶干預，因此用戶一般不需要直接用LOCK TABLE命令給MyISAM表顯式加鎖。在本書的示例中，顯式加鎖基本上都是為了方便而已，並非必須如此。
給MyISAM表顯示加鎖，一般是為了一定程度模擬事務操作，實現對某一時間點多個表的一致性讀取。
要特別說明以下兩點內容。
一個session使用LOCK TABLE 命令給表film_text加了讀鎖，這個session可以查詢鎖定表中的記錄，但更新或訪問其他表都會提示錯誤；同時，另外一個session可以查詢表中的記錄，但更新就會出現鎖等待。
當使用LOCK TABLE時，不僅需要一次鎖定用到的所有表，而且，同一個表在SQL語句中出現多少次，就要通過與SQL語句中相同的別名鎖多少次，否則也會出錯！
並發鎖
在一定條件下，MyISAM也支持查詢和操作的並發進行。
MyISAM存儲引擎有一個系統變數concurrent_insert，專門用以控制其並發插入的行為，其值分別可以為0、1或2。
可以利用MyISAM存儲引擎的並發插入特性，來解決應用中對同一表查詢和插入鎖爭用。例如，將concurrent_insert系統變數為2，總是允許並發插入；同時，通過定期在系統空閑時段執行OPTIONMIZE TABLE語句來整理空間碎片，收到因刪除記錄而產生的中間空洞。
MyISAM的鎖調度
前面講過，MyISAM存儲引擎的讀和寫鎖是互斥，讀操作是串列的。那麼，一個進程請求某個MyISAM表的讀鎖，同時另一個進程也請求同一表的寫鎖，MySQL如何處理呢？答案是寫進程先獲得鎖。不僅如此，即使讀進程先請求先到鎖等待隊列，寫請求後到，寫鎖也會插到讀請求之前！這是因為MySQL認為寫請求一般比讀請求重要。這也正是MyISAM表不太適合於有大量更新操作和查詢操作應用的原因，因為，大量的更新操作會造成查詢操作很難獲得讀鎖，從而可能永遠阻塞。這種情況有時可能會變得非常糟糕！幸好我們可以通過一些設置來調節MyISAM的調度行為。
雖然上面3種方法都是要麼更新優先，要麼查詢優先的方法，但還是可以用其來解決查詢相對重要的應用（如用戶登錄系統）中，讀鎖等待嚴重的問題。
另外，MySQL也提供了一種折中的辦法來調節讀寫沖突，即給系統參數max_write_lock_count設置一個合適的值，當一個表的讀鎖達到這個值後，MySQL變暫時將寫請求的優先順序降低，給讀進程一定獲得鎖的機會。
上面已經討論了寫優先調度機制和解決辦法。這里還要強調一點：一些需要長時間運行的查詢操作，也會使寫進程「餓死」！因此，應用中應盡量避免出現長時間運行的查詢操作，不要總想用一條SELECT語句來解決問題。因為這種看似巧妙的SQL語句，往往比較復雜，執行時間較長，在可能的情況下可以通過使用中間表等措施對SQL語句做一定的「分解」，使每一步查詢都能在較短時間完成，從而減少鎖沖突。如果復雜查詢不可避免，應盡量安排在資料庫空閑時段執行，比如一些定期統計可以安排在夜間執行。
InnoDB鎖問題
InnoDB與MyISAM的最大不同有兩點：一是支持事務（TRANSACTION）；二是採用了行級鎖。
行級鎖和表級鎖本來就有許多不同之處，另外，事務的引入也帶來了一些新問題。
1.事務（Transaction）及其ACID屬性
事務是由一組SQL語句組成的邏輯處理單元，事務具有4屬性，通常稱為事務的ACID屬性。
2.並發事務帶來的問題
相對於串列處理來說，並發事務處理能大大增加資料庫資源的利用率，提高資料庫系統的事務吞吐量，從而可以支持可以支持更多的用戶。但並發事務處理也會帶來一些問題，主要包括以下幾種情況。
3.事務隔離級別
在並發事務處理帶來的問題中，「更新丟失」通常應該是完全避免的。但防止更新丟失，並不能單靠資料庫事務控制器來解決，需要應用程序對要更新的數據加必要的鎖來解決，因此，防止更新丟失應該是應用的責任。
「臟讀」、「不可重復讀」和「幻讀」，其實都是資料庫讀一致性問題，必須由資料庫提供一定的事務隔離機制來解決。資料庫實現事務隔離的方式，基本可以分為以下兩種。
一種是在讀取數據前，對其加鎖，阻止其他事務對數據進行修改。
另一種是不用加任何鎖，通過一定機制生成一個數據請求時間點的一致性數據快照（Snapshot），並用這個快照來提供一定級別（語句級或事務級）的一致性讀取。從用戶的角度，好像是資料庫可以提供同一數據的多個版本，因此，這種技術叫做數據多版本並發控制（MultiVersion Concurrency Control，簡稱MVCC或MCC），也經常稱為多版本資料庫。
資料庫的事務隔離級別越嚴格，並發副作用越小，但付出的代價也就越大，因為事務隔離實質上就是使事務在一定程度上「串列化」進行，這顯然與「並發」是矛盾的，同時，不同的應用對讀一致性和事務隔離程度的要求也是不同的，比如許多應用對「不可重復讀」和「幻讀」並不敏感，可能更關心數據並發訪問的能力。
為了解決「隔離」與「並發」的矛盾，ISO/ANSI SQL92定義了4個事務隔離級別，每個級別的隔離程度不同，允許出現的副作用也不同，應用可以根據自己業務邏輯要求，通過選擇不同的隔離級別來平衡＂隔離＂與＂並發＂的矛盾
事務4種隔離級別比較
隔離級別/讀數據一致性及允許的並發副作用 讀數據一致性 臟讀 不可重復讀 幻讀
未提交讀（Read uncommitted）
已提交度（Read committed） 語句級 否 是 是
可重復讀（Repeatable read） 事務級 否 否 是
可序列化（Serializable） 最高級別，事務級 否 否 否
最後要說明的是：各具體資料庫並不一定完全實現了上述4個隔離級別，例如，Oracle只提供Read committed和Serializable兩個標准級別，另外還自己定義的Read only隔離級別：SQL Server除支持上述ISO/ANSI SQL92定義的4個級別外，還支持一個叫做＂快照＂的隔離級別，但嚴格來說它是一個用MVCC實現的Serializable隔離級別。MySQL支持全部4個隔離級別，但在具體實現時，有一些特點，比如在一些隔離級下是採用MVCC一致性讀，但某些情況又不是。
獲取InonoD行鎖爭用情況
可以通過檢查InnoDB_row_lock狀態變數來分析系統上的行鎖的爭奪情況：
如果發現爭用比較嚴重，如Innodb_row_lock_waits和Innodb_row_lock_time_avg的值比較高，還可以通過設置InnoDB Monitors來進一步觀察發生鎖沖突的表、數據行等，並分析鎖爭用的原因。
InnoDB的行鎖模式及加鎖方法
InnoDB實現了以下兩種類型的行鎖。
另外，為了允許行鎖和表鎖共存，實現多粒度鎖機制，InnoDB還有兩種內部使用的意向鎖（Intention Locks），這兩種意向鎖都是表鎖。
意向共享鎖（IS）：事務打算給數據行共享鎖，事務在給一個數據行加共享鎖前必須先取得該表的IS鎖。
意向排他鎖（IX）：事務打算給數據行加排他鎖，事務在給一個數據行加排他鎖前必須先取得該表的IX鎖。
InnoDB行鎖模式兼容性列表
如果一個事務請求的鎖模式與當前的鎖兼容，InnoDB就請求的鎖授予該事務；反之，如果兩者兩者不兼容，該事務就要等待鎖釋放。
意向鎖是InnoDB自動加的，不需用戶干預。對於UPDATE、DELETE和INSERT語句，InnoDB會自動給涉及及數據集加排他鎖（X）；對於普通SELECT語句，InnoDB會自動給涉及數據集加排他鎖（X）；對於普通SELECT語句，InnoDB不會任何鎖；事務可以通過以下語句顯示給記錄集加共享鎖或排鎖。
共享鎖（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE
排他鎖（X）：SELECT * FROM table_name WHERE ...FOR UPDATE
用SELECT .. IN SHARE MODE獲得共享鎖，主要用在需要數據依存關系時確認某行記錄是否存在，並確保沒有人對這個記錄進行UPDATE或者DELETE操作。但是如果當前事務也需要對該記錄進行更新操作，則很有可能造成死鎖，對於鎖定行記錄後需要進行更新操作的應用，應該使用SELECT ... FOR UPDATE方式獲取排他鎖。
InnoDB行鎖實現方式
InnoDB行鎖是通過索引上的索引項來實現的，這一點MySQL與Oracle不同，後者是通過在數據中對相應數據行加鎖來實現的。InnoDB這種行鎖實現特點意味者：只有通過索引條件檢索數據，InnoDB才會使用行級鎖，否則，InnoDB將使用表鎖！
在實際應用中，要特別注意InnoDB行鎖的這一特性，不然的話，可能導致大量的鎖沖突，從而影響並發性能。
什麼時候使用表鎖
對於InnoDB表，在絕大部分情況下都應該使用行級鎖，因為事務和行鎖往往是我們之所以選擇InnoDB表的理由。但在個另特殊事務中，也可以考慮使用表級鎖。
當然，應用中這兩種事務不能太多，否則，就應該考慮使用MyISAM表。
在InnoDB下 ，使用表鎖要注意以下兩點。
（1）使用LOCK TALBES雖然可以給InnoDB加表級鎖，但必須說明的是，表鎖不是由InnoDB存儲引擎層管理的，而是由其上一層MySQL Server負責的，僅當autocommit=0、innodb_table_lock=1（默認設置）時，InnoDB層才能知道MySQL加的表鎖，MySQL Server才能感知InnoDB加的行鎖，這種情況下，InnoDB才能自動識別涉及表級鎖的死鎖；否則，InnoDB將無法自動檢測並處理這種死鎖。
（2）在用LOCAK TABLES對InnoDB鎖時要注意，要將AUTOCOMMIT設為0，否則MySQL不會給表加鎖；事務結束前，不要用UNLOCAK TABLES釋放表鎖，因為UNLOCK TABLES會隱含地提交事務；COMMIT或ROLLBACK產不能釋放用LOCAK TABLES加的表級鎖，必須用UNLOCK TABLES釋放表鎖，正確的方式見如下語句。
關於死鎖
MyISAM表鎖是deadlock free的，這是因為MyISAM總是一次性獲得所需的全部鎖，要麼全部滿足，要麼等待，因此不會出現死鎖。但是在InnoDB中，除單個SQL組成的事務外，鎖是逐步獲得的，這就決定了InnoDB發生死鎖是可能的。
發生死鎖後，InnoDB一般都能自動檢測到，並使一個事務釋放鎖並退回，另一個事務獲得鎖，繼續完成事務。但在涉及外部鎖，或涉及鎖的情況下，InnoDB並不能完全自動檢測到死鎖，這需要通過設置鎖等待超時參數innodb_lock_wait_timeout來解決。需要說明的是，這個參數並不是只用來解決死鎖問題，在並發訪問比較高的情況下，如果大量事務因無法立即獲取所需的鎖而掛起，會佔用大量計算機資源，造成嚴重性能問題，甚至拖垮資料庫。我們通過設置合適的鎖等待超時閾值，可以避免這種情況發生。
通常來說，死鎖都是應用設計的問題，通過調整業務流程、資料庫對象設計、事務大小、以及訪問資料庫的SQL語句，絕大部分都可以避免。下面就通過實例來介紹幾種死鎖的常用方法。
（1）在應用中，如果不同的程序會並發存取多個表，應盡量約定以相同的順序為訪問表，這樣可以大大降低產生死鎖的機會。如果兩個session訪問兩個表的順序不同，發生死鎖的機會就非常高！但如果以相同的順序來訪問，死鎖就可能避免。
（2）在程序以批量方式處理數據的時候，如果事先對數據排序，保證每個線程按固定的順序來處理記錄，也可以大大降低死鎖的可能。
（3）在事務中，如果要更新記錄，應該直接申請足夠級別的鎖，即排他鎖，而不應該先申請共享鎖，更新時再申請排他鎖，甚至死鎖。
（4）在REPEATEABLE-READ隔離級別下，如果兩個線程同時對相同條件記錄用SELECT...ROR UPDATE加排他鎖，在沒有符合該記錄情況下，兩個線程都會加鎖成功。程序發現記錄尚不存在，就試圖插入一條新記錄，如果兩個線程都這么做，就會出現死鎖。這種情況下，將隔離級別改成READ COMMITTED，就可以避免問題。
（5）當隔離級別為READ COMMITED時，如果兩個線程都先執行SELECT...FOR UPDATE，判斷是否存在符合條件的記錄，如果沒有，就插入記錄。此時，只有一個線程能插入成功，另一個線程會出現鎖等待，當第1個線程提交後，第2個線程會因主鍵重出錯，但雖然這個線程出錯了，卻會獲得一個排他鎖！這時如果有第3個線程又來申請排他鎖，也會出現死鎖。對於這種情況，可以直接做插入操作，然後再捕獲主鍵重異常，或者在遇到主鍵重錯誤時，總是執行ROLLBACK釋放獲得的排他鎖。
盡管通過上面的設計和優化等措施，可以大減少死鎖，但死鎖很難完全避免。因此，在程序設計中總是捕獲並處理死鎖異常是一個很好的編程習慣。
如果出現死鎖，可以用SHOW INNODB STATUS命令來確定最後一個死鎖產生的原因和改進措施。
總結
對於MyISAM的表鎖，主要有以下幾點
（1）共享讀鎖（S）之間是兼容的，但共享讀鎖（S）和排他寫鎖（X）之間，以及排他寫鎖之間（X）是互斥的，也就是說讀和寫是串列的。
（2）在一定條件下，MyISAM允許查詢和插入並發執行，我們可以利用這一點來解決應用中對同一表和插入的鎖爭用問題。
（3）MyISAM默認的鎖調度機制是寫優先，這並不一定適合所有應用，用戶可以通過設置LOW_PRIPORITY_UPDATES參數，或在INSERT、UPDATE、DELETE語句中指定LOW_PRIORITY選項來調節讀寫鎖的爭用。
（4）由於表鎖的鎖定粒度大，讀寫之間又是串列的，因此，如果更新操作較多，MyISAM表可能會出現嚴重的鎖等待，可以考慮採用InnoDB表來減少鎖沖突。
對於InnoDB表，主要有以下幾點
（1）InnoDB的行銷是基於索引實現的，如果不通過索引訪問數據，InnoDB會使用表鎖。
（2）InnoDB間隙鎖機制，以及InnoDB使用間隙鎖的原因。
（3）在不同的隔離級別下，InnoDB的鎖機制和一致性讀策略不同。
（4）MySQL的恢復和復制對InnoDB鎖機制和一致性讀策略也有較大影響。
（5）鎖沖突甚至死鎖很難完全避免。
在了解InnoDB的鎖特性後，用戶可以通過設計和SQL調整等措施減少鎖沖突和死鎖，包括：
③ 用sql語句，怎麼解決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(......);
④ 為什麼索引能快速查找數據行及索引也涉及鎖
1、索引之所以能快速查找數據，就是因為比如B樹索引就是利用二叉樹（這里確切的說是B樹）[這種數據結構及在此基礎上的演算法]能進行快速高效查找的特點。故而Oracle設計出了索引這種數據對象。
注釋：
任何索引的目的是減少對數據塊的訪問，那如何減少對數據塊的訪問，就是利用二叉樹演算法。
2、當更新點陣圖所在的列時，由於要在不同的索引條目之間修改bit位，比如將第一條記錄從01變為02，則必須將01所在的索引條目的第一個bit位改為0，再將02所在的索引條目的第一個bit位改為1。因此，在更新索引條目的過程中，會鎖定點陣圖索引里多個索引條目。也就是在此情況下同時只能有一個用戶能夠更新表T，從而降低了並發性。（在非此情況下還是可以多個用戶同時夠更新表T的）（索引也會用到鎖的機制）。
例如，下面的例子：
表t_bitmap_test的內容如下：
SQL>
select
*
from
t_bitmap_test;
ID
BITCOL
----------
----------
1
3
2
2
3
1
4
3
5
3
對表t_bitmap_test上的欄位bitcol建立索引idx_t_bitmap_test
：
SQL>
create
bitmap
index
idx_t_bitmap_test
on
t_bitmap_test(bitcol);
索引已創建。
在會話A上,
SQL>
update
t_bitmap_test
set
bitcol=2
where
id=5;
行已更改
接著在會話B上,
SQL>
update
t_bitmap_test
set
bitcol=1
where
id=2;
此時，會話B上的update語句被阻塞了。
解釋：
因為會話A上的update語句執行時，是將表上的bitcol=3改為bitcol=2，所以會在索引鍵值為bitcol=2和bitcol=3的索引條目上同時加上行級鎖。
當會話B上的update語句執行時，是將表上的bitcol=2改為bitcol=1，所以先要在索引鍵值為bitcol=2和bitcol=1的索引條目上同時加上行級鎖，才能執行update語句。
而由於會話A上的update語句還未提交，故它在索引鍵值為bitcol=2的索引條目上加的行級鎖還在。而行級鎖都是X（排他)鎖模式的，即不兼容其他鎖模式的鎖，所以會話B上（的update語句）對應的伺服器進程在索引鍵值為bitcol=2的索引條目上加不了行級鎖。因此，會話B上的update語句執行被阻塞了。
注意：實驗時，一定要是對加了索引的列進行修改，而且在會話A上該列值由e改為f，在會話B上該列值由e改為d（f改為t），即兩個會話都會要在一個共同的索引條目上加鎖的情況。
⑤ 怎麼理解資料庫的鎖 一般鎖分別哪幾種
資料庫是一個多用戶使用的共享資源。當多個用戶並發地存取數據時，在資料庫中就會產生多個事務同時存取同一數據的情況。若對並發操作不加控制就可能會讀取和存儲不正確的數據，破壞資料庫的一致性。
加鎖是實現資料庫並發控制的一個非常重要的技術。當事務在對某個數據對象進行操作前，先向系統發出請求，對其加鎖。加鎖後事務就對該數據對象有了一定的控制，在該事務釋放鎖之前，其他的事務不能對此數據對象進行更新操作。
在資料庫中有兩種基本的鎖類型：排它鎖（Exclusive Locks，即X鎖）和共享鎖（Share Locks，即S鎖）。當數據對象被加上排它鎖時，其他的事務不能對它讀取和修改。加了共享鎖的數據對象可以被其他事務讀取，但不能修改。資料庫利用這兩種基本的鎖類型來對資料庫的事務進行並發控制。
(5)資料庫索引存儲鎖擴展閱讀：
排它鎖和共享鎖的不同之處：
1、共享鎖（S鎖）：如果事務T對數據A加上共享鎖後，則其他事務只能對A再加共享鎖，不能加排他鎖。獲准共享鎖的事務只能讀數據，不能修改數據。
排他鎖（X鎖）：如果事務T對數據A加上排他鎖後，則其他事務不能再對A加任任何類型的封鎖。獲准排他鎖的事務既能讀數據，又能修改數據。
2、共享鎖下其它用戶可以並發讀取，查詢數據。但不能修改，增加，刪除數據，資源共享。
3、共享鎖又稱為讀鎖（Share lock,簡記為S鎖），若事務T對數據對象A加上S鎖，則其它事務只能再對A加S鎖，而不能加X鎖，直到T釋放A上的S鎖。
⑥ 資料庫為什麼需要鎖機制有哪些鎖機制
資料庫鎖的產生原因:
資料庫和操作系統一樣，是一個多用戶使用的共享資源。當多個用戶並發地存取數據 時，在資料庫中就會產生多個事務同時存取同一數據的情況。若對並發操作不加控制就可能會讀取和存儲不正確的數據，破壞資料庫的一致性。加鎖是實現資料庫並 發控制的一個非常重要的技術。在實際應用中經常會遇到的與鎖相關的異常情況，當兩個事務需要一組有沖突的鎖，而不能將事務繼續下去的話，就會出現死鎖，嚴 重影響應用的正常執行。
在資料庫中有兩種基本的鎖類型：排它鎖（Exclusive Locks，即X鎖）和共享鎖（Share Locks，即S鎖）。當數據對象被加上排它鎖時，其他的事務不能對它讀取和修改。加了共享鎖的數據對象可以被其他事務讀取，但不能修改。資料庫利用這兩 種基本的鎖類型來對資料庫的事務進行並發控制。
⑦ 什麼是資料庫索引
第二次回答：
問題補充：能不能具體點，新建一個索引就可以了嗎
基本上可以這么說，不過你也可以修改索引。
記住：
索引其實關鍵目的是為了加快檢索速度而建立的，所以，怎麼用索引是資料庫系統本身的事情，作為資料庫設計或使用者，設計並創建好索引然後體驗加上索引後的查詢變快的感覺就行了。所以，索引怎麼用就變為了「怎麼創建合適的索引」
以下回答是否符合你的要求？你還有什麼問題？
第一次回答：
一、索引是什麼
索引是與表或視圖關聯的磁碟上結構，可以加快從表或視圖中檢索行的速度。索引包含由表或視圖中的一列或多列生成的鍵。這些鍵存儲在一個結構（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
⑧ Oracle資料庫鎖的常用類型有哪些
Oracle資料庫的鎖類型
根據保護的對象不同，Oracle資料庫鎖可以分為以下幾大類：DML鎖（data locks，數據鎖），用於保護數據的完整性；DDL鎖（dictionary locks，字典鎖），用於保護資料庫對象的結構，如表、索引等的結構定義；內部鎖和閂（internal locks and latches），保護資料庫的內部結構。
DML鎖的目的在於保證並發情況下的數據完整性，本文主要討論DML鎖。在Oracle資料庫中，DML鎖主要包括TM鎖和TX鎖，其中TM鎖稱為表級鎖，TX鎖稱為事務鎖或行級鎖。
當Oracle執行DML語句時，系統自動在所要操作的表上申請TM類型的鎖。當TM鎖獲得後，系統再自動申請TX類型的鎖，並將實際鎖定的數據行的鎖標志位進行置位。這樣在事務加鎖前檢查TX鎖相容性時就不用再逐行檢查鎖標志，而只需檢查TM鎖模式的相容性即可，大大提高了系統的效率。TM鎖包括了SS、SX、S、X等多種模式，在資料庫中用0－6來表示。不同的SQL操作產生不同類型的TM鎖。如表1所示。
在數據行上只有X鎖（排他鎖）。在 Oracle資料庫中，當一個事務首次發起一個DML語句時就獲得一個TX鎖，該鎖保持到事務被提交或回滾。當兩個或多個會話在表的同一條記錄上執行DML語句時，第一個會話在該條記錄上加鎖，其他的會話處於等待狀態。當第一個會話提交後，TX鎖被釋放，其他會話才可以加鎖。
當Oracle資料庫發生TX鎖等待時，如果不及時處理常常會引起Oracle資料庫掛起，或導致死鎖的發生，產生ORA-60的錯誤。這些現象都會對實際應用產生極大的危害，如長時間未響應，大量事務失敗等。
TX鎖等待的分析
在介紹了有關地Oracle資料庫鎖的種類後，下面討論如何有效地監控和解決鎖等待現象，及在產生死鎖時如何定位死鎖的原因。
監控鎖的相關視圖 數據字典是Oracle資料庫的重要組成部分，用戶可以通過查詢數據字典視圖來獲得資料庫的信息。和鎖相關的數據字典視圖如表2所示。
TX鎖等待的監控和解決在日常工作中，如果發現在執行某條SQL時資料庫長時間沒有響應，很可能是產生了TX鎖等待的現象。為解決這個問題，首先應該找出持鎖的事務，然後再進行相關的處理，如提交事務或強行中斷事務。
死鎖的監控和解決在資料庫中，當兩個或多個會話請求同一個資源時會產生死鎖的現象。死鎖的常見類型是行級鎖死鎖和頁級鎖死鎖，Oracle資料庫中一般使用行級鎖。下面主要討論行級鎖的死鎖現象。
當Oracle檢測到死鎖產生時，中斷並回滾死鎖相關語句的執行，報ORA-00060的錯誤並記錄在資料庫的日誌文件alertSID.log中。同時在user_mp_dest下產生了一個跟蹤文件，詳細描述死鎖的相關信息。
在日常工作中，如果發現在日誌文件中記錄了ora-00060的錯誤信息，則表明產生了死鎖。這時需要找到對應的跟蹤文件，根據跟蹤文件的信息定位產生的原因。
如果查詢結果表明，死鎖是由於bitmap索引引起的，將IND_T_PRODUCT_HIS_STATE索引改為normal索引後，即可解決死鎖的問題。
表1 Oracle的TM鎖類型
鎖模式 鎖描述 解釋 SQL操作
0 none
1 NULL 空 Select
2 SS(Row-S) 行級共享鎖，其他對象只能查詢這些數據行 Select for update、Lock for update、Lock row share
3 SX(Row-X) 行級排它鎖，在提交前不允許做DML操作 Insert、Update、Delete、Lock row share
4 S(Share) 共享鎖 Create index、Lock share
5 SSX(S/Row-X) 共享行級排它鎖 Lock share row exclusive
6 X(Exclusive) 排它鎖 Alter table、Drop able、Drop index、Truncate table 、Lock exclusive
表2 數據字典視圖說明
視圖名 描述 主要欄位說明
v$session 查詢會話的信息和鎖的信息。 sid,serial#：表示會話信息。
program：表示會話的應用程序信息。
row_wait_obj#：表示等待的對象。
和dba_objects中的object_id相對應。
v$session_wait 查詢等待的會話信息。 sid：表示持有鎖的會話信息。
Seconds_in_wait：表示等待持續的時間信息
Event：表示會話等待的事件。
v$lock 列出系統中的所有的鎖。 Sid：表示持有鎖的會話信息。
Type：表示鎖的類型。值包括TM和TX等。
ID1：表示鎖的對象標識。
lmode,request：表示會話等待的鎖模式的信
息。用數字0－6表示，和表1相對應。
dba_locks 對v$lock的格式化視圖。 Session_id：和v$lock中的Sid對應。
Lock_type：和v$lock中的type對應。
Lock_ID1： 和v$lock中的ID1對應。
Mode_held,mode_requested：和v$lock中
的lmode,request相對應。
v$locked_object 只包含DML的鎖信息，包括回滾段和會話信息。 Xisn,xidslot,xidsqn：表示回滾段信息。和
v$transaction相關聯。
Object_id：表示被鎖對象標識。
Session_id：表示持有鎖的會話信息。
Locked_mode：表示會話等待的鎖模式的信
息，和v$lock中的lmode一致。
col owner for a12
col object_name for a16
select b.owner,b.object_name,l.session_id,l.locked_mode
from v$locked_object l, dba_objects b
where b.object_id=l.object_id;
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進程並不能徹底清除鎖的問題
Oracle鎖表(死鎖) 2011-05-03 17:46:41| 分類： Java技術 | 標簽： |字型大小大中小 訂閱 .
資料庫與操作系統一樣，是一個多用戶使用的共享資源。 當多個用戶並發地存取數據時，在資料庫中就會發生多個事務同時存取同一數據地情況。 若對並發操作不加控制就可能會讀取和存儲不正確地數據，破壞資料庫地一致性。 加鎖時實現資料庫並發控制地一個非常重要地技術。 在實際應用中經常會遇到地與鎖相關地異常情況，當兩個事務需要一組有沖突的鎖，而不能將事務繼續下去的話，就會出現死鎖，嚴重影響應用的正常執行。
在資料庫中有兩種基本的鎖類型：排它鎖（Exclusive Locks，即X鎖）和共享鎖（即S鎖）。當數據對象被加上排它鎖時，其他的事務不能不能對它讀取和修改。加了共享鎖的數據對象可以被其他事務讀取，但不能修改。資料庫利用這兩種基本的鎖類型來對資料庫的事務進行並發控制。
死鎖的第一種情況：
一個用戶A訪問表A（鎖住了表A），然後又訪問表B； 另一個用戶B訪問表B（鎖住了表B），然後企圖訪問表A；這時用戶A由於用戶B已經鎖住表B，它必須等待用戶B釋放表B才能繼續，同樣用戶B要等用戶A釋放表A才能繼續，這就死鎖產生了。
解決方法：
這種死鎖比較常見，是由於程序的BUG產生的，除了調整程序的邏輯沒有其它的辦法。仔細分析程序的邏輯，對於資料庫的多表操作時，盡量按照同樣的順序進行處理，盡量避免同時鎖定兩個資源，如操作A和B兩張表時，總是按先A後B的順序處理，必須同時鎖定兩個資源時，要保證在任何時刻都應該按照相同的順序來鎖定資源。
死鎖的第二種情況
用戶A查詢一條記錄，然後修改該條記錄；這時用戶B修改該條記錄，這時用戶A的事務里鎖的性質由查詢的共享鎖企圖上升到獨占鎖，而用戶B里的獨占鎖由於A有共享鎖存在必須等A釋放掉共享鎖，而A由於B的獨占鎖而無法上升到獨占鎖也就不可能釋放共享鎖，於是出現了死鎖。這種死鎖比較隱蔽，但在稍大點的項目種經常發生，如在某項目中，頁面上的按鈕點擊後，沒有使按鈕立刻失效，使得用戶會多次快速點擊同一按鈕，這樣同一段代碼對資料庫同一條記錄進行多次操作，很容易就出現這種死鎖的情況。
解決方法：
1、對於按鈕等控制項，點擊後使其立刻失效，不讓用戶重復點擊，避免對同時對同一條記錄操作。
2、使用樂觀鎖進行控制。樂觀鎖大多是基於數據版本（version）記錄機制實現。即為數據增加一個版本標識，在基於資料庫表的版本解決方案中，一般是通過為資料庫增加一個「version」欄位來實現。讀取處數據時，將此版本號一同讀出，之後更新時，對此版本號加一。此時，將提交的數據的版本數據與資料庫表對應記錄的當前版本信息進行比對，如果提交的數據版本號大於資料庫表當前版本號，則予以更新，否則認為是過期數據。樂觀鎖機制避免了長事務中的資料庫加鎖開銷（用戶A和用戶B操作過程中，都沒有對資料庫加鎖），大大提升了大並發量下的系統整體性表現。 Hibernate在其數據訪問引擎中內置了樂觀鎖實現。需要注意的是，由於樂觀鎖機制是我們的系統中實現，來自外部系統的用戶更新操作不受我們系統的控制，因此可能會造成臟數據被更新到資料庫中。
3、使用悲觀鎖進行控制。悲觀鎖大多數情況下依靠資料庫的鎖機制實現，如Oracle的select.......for update語句，以保證操作最大程度的獨占性。但隨之而來的就是資料庫性能的大量開銷，特別是對長事務而言，這樣的開銷往往無法承受。如一個金融系統，當某個操作員讀取用戶的數據，並在讀出的用戶數據的基礎上進行修改時（如更改用戶帳戶余額），如果採用悲觀鎖機制，也就意味整個操作過程中（從操作員讀出數據、開始修改直至提交修改結果的全過程，甚至還包括操作員中途去煮咖啡的時間），資料庫記錄始終處於加鎖狀態，可以想見，如果面對成百上千個並發，這樣的情況將導致災難性的結果。所以，採用悲觀鎖進行控制時一定要考慮清楚。
死鎖的第三種情況
如果在事務種執行了一條不滿足條件的update語句，則執行全表掃描，把行級鎖上升為表級鎖，多個這樣的事務執行之後，就很容易發生死鎖和阻塞。類似的情況還有當表種的數據量非常龐大而索引建的過少或不合適的時候，使得經常發生全表掃描，最終應用系統會越來越慢，最終發生阻塞或死鎖。
解決方法：
SQL語句中不要使用太復雜的關聯多表的查詢；使用「執行計劃」對SQL語句進行 分析，對於有全表掃描的SQL語句，建立相應的索引進行優化。
***查詢死鎖表以及解鎖表***
通過select * from v$locked_object
可以獲得被鎖的對象的object_id及產生鎖的會話sid，通過查詢結果中的object_id，可以查詢到具體被鎖的對象。
鎖有以下幾種模式:
0：none
1：null 空
2：Row-S 行共享(RS / S鎖)：共享表鎖
3：Row-X 行專用(RX / X鎖)：用於行的修改
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的鎖。
沒有commit之前插入同樣的一條記錄會沒有反應,
因為後一個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語句殺掉長期沒有釋放非正常的鎖：
⑨ Sql server08資料庫執行什麼操作的時候會加S鎖，IX鎖和SIX鎖
一. 為什麼要引入鎖
多個用戶同時對資料庫的並發操作時會帶來以下數據不一致的問題:
丟失更新
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) 其他事務不能讀取表,更新和刪除
⑩ 資料庫索引是什麼，有什麼用，怎麼用
1、資料庫索引是什麼，有什麼用
資料庫索引是對資料庫表中一列或多列的值進行排序的一種結構，使用索引可快速訪問資料庫表中的特定信息。如果想按特定職員的姓來查找他或她，則與在表中搜索所有的行相比，索引有助於更快地獲取信息。
索引的一個主要目的就是加快檢索表中數據的方法，亦即能協助信息搜索者盡快的找到符合限制條件的記錄ID的輔助數據結構。
2、資料庫索引的用法
當表中有大量記錄時，若要對表進行查詢，第一種搜索信息方式是全表搜索，是將所有記錄一一取出，和查詢條件進行一一對比，然後返回滿足條件的記錄，這樣做會消耗大量資料庫系統時間，並造成大量磁碟I/O操作；
第二種就是在表中建立索引，然後在索引中找到符合查詢條件的索引值，最後通過保存在索引中的ROWID（相當於頁碼）快速找到表中對應的記錄。
索引是一個單獨的、物理的資料庫結構，它是某個表中一列或若干列值的集合和相應的指向表中物理標識值的數據頁的邏輯指針清單。
(10)資料庫索引存儲鎖擴展閱讀：
一、索引的原理：
對要查詢的欄位建立索引其實就是把該欄位按照一定的方式排序；建立的索引只對該欄位有用，如果查詢的欄位改變，那麼這個索引也就無效了，比如圖書館的書是按照書名的第一個字母排序的，那麼你想要找作者叫張三的就不能用改索引了；還有就是如果索引太多會降低查詢的速度。
二、資料庫索引的特點：
1、避免進行資料庫全表的掃描，大多數情況，只需要掃描較少的索引頁和數據頁，而不是查詢所有數據頁。而且對於非聚集索引，有時不需要訪問數據頁即可得到數據。
2、聚集索引可以避免數據插入操作，集中於表的最後一個數據頁面。
3、在某些情況下，索引可以避免排序操作。