1、任務1 認識數據庫中的事務機制項目9 MySQL數據庫中的事務機制與鎖機制任務2認識事務的ACID特性任務3 認識MySQL數據庫中的鎖機制事務的回滾任務1 認識數據庫中的事務機制事務的提交事務的開啟MySQL數據庫的存儲引擎與事務MySQL的自動提交功能事務的開啟MySQL數據庫中事務通常是采用start transaction 語句作為開始。從事務的開啟到事務的結束,所有的操作是一個完整的過程,要么全部執行成功,要么全部撤銷從而恢復到事務的開始之前。任務1 認識數據庫中的事務機制事務的提交事務在使用start transaction 語句開啟之后采用commit語句提交,如果沒有commi

2、t語句,MySQL數據庫系統判定這個開啟的事務沒有結束。當commit語句被執行時,這個事務包含的所有操作將作為一個整體被系統執行,結果保存進數據庫的物理磁盤內。任務1 認識數據庫中的事務機制事務的回滾回滾(Rollback)表示當事務中的一項或者多項操作失敗導致事務無法繼續運行時,系統將該事務已運行的所有操作全部撤銷,回到事務所有操作開啟之前的狀態(MySQL數據庫采用savepoint語句可以指定回滾的位置)任務1 認識數據庫中的事務機制MySQL數據庫的存儲引擎與事務MySQL數據庫的存儲引擎主要分為兩種,分別是InnoDB存儲引擎和MyISAM 存儲引擎兩種,其中InnoDB存儲引擎是

3、事務安全的，采用較多。任務1 認識數據庫中的事務機制MySQL的自動提交功能MySQL的默認模式是自動提交模式,該模式是InnoDB存儲引擎的特有模式,即在執行DML語句時,會立刻采用隱性事務,將執行結果提交數據庫系統。也可以通過設置autocommit參數的值修改這一默認模式,當autocommit=1 時為自動提交模式;當autocommit=0時,為非自動提交模式。修改autocommit參數需要使用commit語句進行提交。任務1 認識數據庫中的事務機制任務1.1 了解事務機制的必要性 在各種軟件系統中,管理員帳號是經常需要添加和刪除的,往往在這些操作中會出現人為的失誤,特別是誤刪除操

4、作,產生嚴重的后果。在數據庫中,使用事務去處理添加或者刪除工作,會使操作的安全性得到大大的提高。,當沒有使用事務機制時,admin表中的數據在執行刪除操作后,就永久丟了。而在使用start transaction 開啟事務后,刪除的命令雖然被執行,但是繼續輸入rollback 命令,依然可以恢復數據。任務1 認識數據庫中的事務機制子任務1.2 了解MySQL的自動提交功能1.自動提交模式 該模式下設置autocommit =1,MySQL在執行DML語句時, 會立刻采用隱性事務,將執行結果提交數據庫系統。2.非自動提交模式 該模式下設置autocommit =0,MySQL不會將執行結果立刻提

5、交數據庫系統。當系統為非自動提交模式時,每條DML語句需要在commit語句提交后,才能真正地執行完畢。任務1 認識數據庫中的事務機制子任務1.3 了解事務的提交 事務采用start transaction 開啟事務后,利用commit語句提交結果,并查看commit語句的作用(確保admin表采用了InnoDB存儲引擎,如果不是,則隔離級別使用MySQL默認值)。 先要使用start transaction 開啟事務。在學習事務的使用方法時,通常需要利用兩個session來驗證,所以必須開啟兩個命令窗口。對于commit的作用,可以采用插入新數據的方式驗證。任務1 認識數據庫中的事務機制子任

6、務1.4 了解事務的回滾 1.驗證回滾,則先要使用start transaction 開啟事務。在觀察事務的使用方法時,通常需要利用兩個session來驗證。對于回滾的作用,可以采用插入新數據的方式驗證。 2.回滾的操作中,savepoint語句可以指定回滾的位置。任務1 認識數據庫中的事務機制子任務2.1 了解事務的ACID 特性 事務有四大特征，簡稱ACID特性。1.原子性(Atomicity)2.一致性(Consistency)3.隔離性(Isolation)4.持久性(Durability)任務2 認識事務的ACID 特性子任務2.1 認識事務的隔離級別與并發問題 Mysql數據庫事務

7、設置有4個隔離級別,根據級別的嚴格程度,由低到高依次為:讀未提交(Read uncommitted)讀提交(Read committed)可重復讀取(Repeatable read)串行化(Serializable)任務2 認識事務的ACID 特性子任務2.2 認識事務的隔離級別與并發問題1.讀未提交(Read uncommitted) 該級別屬于未授權讀取,當第一個事務開始對某行數據的進行修改,第二個事務可以讀取此行數據,但是不允許進行修改。可以使用“排他鎖”實現此隔離級別,后續內容會詳細介紹“排它鎖”。這個級別雖然禁止了多個事務并發修改某行數據,解決了“更新丟失”,但是依然會出現第二個事務

8、讀取到了第一個事務未提交的數據,導致“臟讀”。任務2 認識事務的ACID 特性子任務2.2 認識事務的隔離級別與并發問題2.讀提交(Read committed) 該級別屬于授權讀取,當第一個事務讀取某行數據時,也允許第二個事務繼續讀取該行數據,但是如果對該行數據有未提交的寫操作,則第一個事務將禁止其他事務訪問該行數據。 該隔離級別解決了“臟讀”的問題,但是卻沒有解決“不可重復讀”的問題。任務2 認識事務的ACID 特性子任務2.2 認識事務的隔離級別與并發問題3.可重復讀(Repeatable read) 可重復讀是表示在同一個事務之內,多次讀取同一行數據,其讀取結果是相同的。該隔離級別被稱

9、為“可重復讀”。這樣避免了不可重復讀取和臟讀,但是仍然不能避免幻讀。任務2 認識事務的ACID 特性子任務2.2 認識事務的隔離級別與并發問題4.串行化(Serializable) 該級別是最嚴格的事務隔離。事務的串行化,表示所有事務不能并發執行,必須依次執行。在該級別下,所有事務都順序執行,不僅可以避免臟讀、不可重復讀,還避免了幻讀。串行化雖然解決了所有問題,但是代價也花費最高,它會導致數據庫的性能很低,在實際應用中幾乎很少使用。任務2 認識事務的ACID 特性子任務2.3 設置事務的隔離級別1.讀未提交 將隔離級別設置為“讀未提交”: mysql set tx_isolation=READ

10、-UNCOMMITTED任務2 認識事務的ACID 特性子任務2.3 設置事務的隔離級別2.讀提交 將隔離級別設置為“讀提交”: mysql set tx_isolation=read-committed;任務2 認識事務的ACID 特性子任務2.3 設置事務的隔離級別3.可重復讀 設置隔離級別為“可重復讀” mysql set tx_isolation=repeatable-read;任務2 認識事務的ACID 特性子任務2.3 設置事務的隔離級別4.串行化 設置隔離級別為“串行化” mysql set tx_isolation=serializable;任務2 認識事務的ACID 特性鎖機

11、制定義任務3 認識MySQL數據庫中的鎖機制MySQL數據庫的鎖機制間隙鎖死鎖子任務3.1 了解鎖機制的必要性 事務是Mysql數據庫的核心內容,而事務的隔離性主要由鎖機制來實現。事務在對某個數據庫對象進行操作時,向系統發出加鎖的請求,加鎖后事務對該對象就有了一定范圍的控制權。在這個范圍內,該事務不釋放鎖之前,其他的事務不能對該對象進行修改的操作,這就是鎖機制。任務3 認識MySQL數據庫中的鎖機制子任務3.2 了解MySQL 數據庫的鎖機制 MySQL數據庫是不同的存儲引擎支持不同的鎖機制。MySQL數據庫的存儲引擎分為InnoDB存儲引擎、BDB存儲引擎和MyISAM 存儲引擎三種,本章只

12、對InnoDB存儲引擎和MyISAM 存儲引擎進行介紹任務3 認識MySQL數據庫中的鎖機制子任務3.2 了解MySQL 數據庫的鎖機制 一、MyISAM 存儲引擎: MyISAM 只支持表級鎖,表級鎖有兩種,一種是表共享讀鎖(Table Read Lock),一種是表獨占寫鎖(Table Write Lock)。MyISAM 無論是讀請求鎖還是寫請求鎖,都在隊列中等待,先到先申請。但是寫請求鎖的優先級比讀請求鎖高,所以寫請求鎖往往優先執行。任務3 認識MySQL數據庫中的鎖機制子任務3.2 了解MySQL 數據庫的鎖機制 二、InnoDB存儲引擎: 1.共享鎖(S)：允許獲得共享鎖的事務讀取

13、數據,阻止其他事務對該數據集添加排他鎖。使用SELECT.IN SHARE MODE 獲得共享鎖。 2.排他鎖(X)：允許獲得排他鎖的事務刪除和修改數據,阻止其他事務對該數據集添加共享鎖和排他鎖。使用 SELECT.FOR UPDATE 方式獲得排他鎖。任務3 認識MySQL數據庫中的鎖機制子任務3.3 使用MyISAM 表的表級鎖 MyISAM 在執行查詢語句select時,會直接給所有要查詢的表加上讀鎖。如果是執行增(insert)、刪(delete)、改(update)時,會直接給所有涉及的表加上寫鎖。為了驗證效果,采用顯式加鎖的方式來實現這個任務。任務3 認識MySQL數據庫中的鎖機制

14、子任務3.4使用InnoDB 表的行級鎖1.表級鎖 session1開啟一個事務并查詢所有記錄,session2開啟一個事務并查詢所有記錄。步驟2中,因為department表沒有索引,直接在Dept_id=1上加排他鎖。步驟3中,發現不是Dept_id=1范圍的記錄也無法查看,因為department表被加了表級鎖。任務3 認識MySQL數據庫中的鎖機制子任務3.4使用InnoDB 表的行級鎖2.行級鎖(課本表9-28、表9-29) 先將Dept_id字段加上索引。步驟1中,session1和session2都開啟一個事務,查詢出所有記錄。步驟2中,session1給Dept_id =1加上

15、排他鎖,session2給Dept_id =2加上排他鎖,所有操作正常執行,這就是行級鎖的添加方法。任務3 認識MySQL數據庫中的鎖機制子任務3.4使用InnoDB 表的行級鎖3.行級鎖使用相同索引(課本表9-30、表9-31) 步驟1中,步驟1中,session1和session2都開啟一個事務。步驟2中,session1給Dept_id =2和 Dept_name=計算機的索引加排他鎖。步驟3中,session2給Dept_id =2和 Dept_name=電子加排他鎖,因為Dept_id =2已經被session1加了排他鎖,所以等待session1的鎖釋放才能執行。步驟4中,sess

16、ion1提交事務,排他鎖釋放,session2獲得鎖。任務3 認識MySQL數據庫中的鎖機制子任務3.4使用InnoDB 表的行級鎖4.多索引鎖定(表9-32、表9-33、表9-34)步驟1中,session1和session2都開啟一個事務。步驟2中,session1給Dept_name=計算機加上排他鎖,正常執行。步驟3中,session2給Dept_name=電子加上排他鎖,因為結果集和Dept_name=計算機沒有交集,所以正常執行。步驟4中,session2給Dept_id=2加上排他鎖,由于結果集的Dept_name=計算機已經被session1加了排他鎖,所以等待session1

17、的鎖釋放。步驟5中,session1提交事務,鎖釋放,session2獲得鎖。任務3 認識MySQL數據庫中的鎖機制子任務3.5 了解間隙鎖避免幻讀現象1、幻讀 在一個事務(同一個read view)在前后兩次查詢同一范圍的時候，后一次查詢看到了前一次查詢沒有看到的行。任務3 認識MySQL數據庫中的鎖機制子任務3.5 了解間隙鎖避免幻讀現象2、間隙鎖 在InnoDB存儲引擎中,當事務請求鎖時,InnoDB會給符合條件的已有數據的索引項上加鎖,但是也會在符合查詢條件的范圍內,將不存在的記錄上加鎖,這些不存在的記錄,就稱之為“間隙”,也叫GAP。因為這個“間隙”被加了鎖,所以這個機制叫“間隙鎖”。任務3 認識MySQL數據庫中的鎖機制子任務3.6 了解死鎖與鎖等待1、死鎖 假如T1和T2同時執行，首先T1會占有行級鎖，然后T2會排隊等待T1釋放行級鎖，而事物T1的第二條表鎖更新語句會等待事物2釋放行級鎖，這就出現T1等T2，T2等T1的情況，然后就死鎖了。T1一直沒commit，那么T2就會一直等T1，但是T1第二條語句想執行，就要等T2commit，這是死鎖出現的情況。任務3 認識MySQL數