57、數(shù)據(jù)庫(kù)的鎖

??鎖是一種并發(fā)控制技術(shù)，鎖是用來在多個(gè)用戶同時(shí)訪問同一個(gè)數(shù)據(jù)的時(shí)候保護(hù)數(shù)據(jù)的。

57.1、有 2 種基本的鎖類型

??共享(S)鎖： 多個(gè)事務(wù)可封鎖一個(gè)共享頁；任何事務(wù)都不能修改該頁；通常是該頁被讀取完畢，S鎖立即被釋放。在執(zhí)行 select 語句的時(shí)候需要給操作對(duì)象（表或者一些記錄）加上共享鎖，但加鎖之前需要檢查是否有排他鎖，如果沒有，則可以加共享鎖（一個(gè)對(duì)象上可以加 n 個(gè)共享鎖 ），否則不行。共享鎖通常在執(zhí)行完 select 語句之后被釋放，當(dāng)然也有可能是在事務(wù)結(jié)束（包括正常結(jié)束和異常結(jié)束）的時(shí)候被釋放，主要取決于數(shù)據(jù)庫(kù)所設(shè)置的事務(wù)隔離級(jí)別。

??排它(X)鎖： 僅允許一個(gè)事務(wù)封鎖此頁；其他任何事務(wù)必須等到 X 鎖被釋放才能對(duì)該頁進(jìn)行訪問；X 鎖一直到事務(wù)結(jié)束才能被釋放。執(zhí)行 insert、update、delete 語句的時(shí)候需要給操作的對(duì)象加排他鎖，在加排他鎖之前必須確認(rèn)該對(duì)象上沒有其他任何鎖，一旦加上排他鎖之后，就不能再給這個(gè)對(duì)象加其他任何鎖。排他鎖的釋放通常是在事務(wù)結(jié)束的時(shí)候（當(dāng)然也有例外，就是在數(shù)據(jù)庫(kù)事務(wù)隔離級(jí)別被設(shè)置成 Read Uncommitted（讀未提交數(shù)據(jù)）的時(shí)候，這種情況下排他鎖會(huì)在執(zhí)行完更新操作之后就釋放，而不是在事務(wù)結(jié)束的時(shí)候）。

57.2、mysql 鎖的粒度（即鎖的級(jí)別）

??MySQL 各存儲(chǔ)引擎使用了三種類型（級(jí)別）的鎖定機(jī)制：行級(jí)鎖定，頁級(jí)鎖定和表級(jí)鎖定。
??1、表級(jí)鎖， 直接鎖定整張表，在你鎖定期間，其它進(jìn)程無法對(duì)該表進(jìn)行寫操作。如果你是寫鎖，則其它進(jìn)程則讀也不允許。特點(diǎn)：開銷小，加鎖快；不會(huì)出現(xiàn)死鎖；鎖定粒度最大，發(fā)生鎖沖突的概率最高，并發(fā)度最低。
??MyISAM 存儲(chǔ)引擎采用的是表級(jí)鎖。
??有 2 種模式：表共享讀鎖和表獨(dú)占寫鎖。加讀鎖的命令：lock table 表名 read;去掉鎖的命令：unlock tables。
??支持并發(fā)插入：支持查詢和插入操作并發(fā)進(jìn)行（在表尾并發(fā)插入）。
??鎖調(diào)度機(jī)制：寫鎖優(yōu)先。一個(gè)進(jìn)程請(qǐng)求某個(gè) MyISAM 表的讀鎖，同時(shí)另一個(gè)進(jìn)程也請(qǐng)求同一表的寫鎖，MySQL 如何處理呢？答案是寫進(jìn)程先獲得鎖。

??2、行級(jí)鎖， 僅對(duì)指定的記錄進(jìn)行加鎖，這樣其它進(jìn)程還是可以對(duì)同一個(gè)表中的其它記錄進(jìn)行操作。特點(diǎn)：開銷大，加鎖慢；會(huì)出現(xiàn)死鎖；鎖定粒度最小，發(fā)生鎖沖突的概率最低，并發(fā)度也最高。
??InnoDB 存儲(chǔ)引擎既支持行級(jí)鎖，也支持表級(jí)鎖，但默認(rèn)情況下是采用行級(jí)鎖。

??3、頁級(jí)鎖，一次鎖定相鄰的一組記錄。開銷和加鎖時(shí)間界于表鎖和行鎖之間；會(huì)出現(xiàn)死鎖；鎖定粒度界于表鎖和行鎖之間，并發(fā)度一般。

??最常用的處理多用戶并發(fā)訪問的方法是加鎖。當(dāng)一個(gè)用戶鎖住數(shù)據(jù)庫(kù)中的某個(gè)對(duì)象時(shí)，其他用戶就不能再訪問該對(duì)象。加鎖對(duì)并發(fā)訪問的影響體現(xiàn)在鎖的粒度上。比如，(表鎖)放在一個(gè)表上的鎖限制對(duì)整個(gè)表的并發(fā)訪問；（頁鎖）放在數(shù)據(jù)頁上的鎖限制了對(duì)整個(gè)數(shù)據(jù)頁的訪問；（行鎖）放在行上的鎖只限制對(duì)該行的并發(fā)訪問。

57.3、按鎖的機(jī)制分：有悲觀鎖和樂觀鎖

??悲觀鎖 ，鎖如其名，他對(duì)世界是悲觀的，他認(rèn)為別人訪問正在改變的數(shù)據(jù)的概率是很高的，所以從數(shù)據(jù)開始更改時(shí)就將數(shù)據(jù)鎖住，直到更改完成才釋放。
??一個(gè)典型的倚賴數(shù)據(jù)庫(kù)的悲觀鎖調(diào)用：
??select * from account where name=”Erica” for update
??這條 sql 語句鎖定了 account 表中所有符合檢索條件（name=”Erica”）的記錄。 本次事務(wù)提交之前（事務(wù)提交時(shí)會(huì)釋放事務(wù)過程中的鎖），外界無法修改這些記錄。該語句用來鎖定特定的行（如果有 where 子句，就是滿足 where條件的那些行）。當(dāng)這些行被鎖定后，其他會(huì)話可以選擇這些行，但不能更改或刪除這些行，直到該語句的事務(wù)被 commit 語句或 rollback 語句結(jié)束為止。需要注意的是，select ....for update 要放到 mysql的事務(wù)中，即 begin 和commit 中，否則不起作用。
??悲觀鎖可能會(huì)造成加鎖的時(shí)間很長(zhǎng)，并發(fā)性不好，特別是長(zhǎng)事務(wù)，影響系統(tǒng)的整體性能。
??悲觀鎖的實(shí)現(xiàn)方式：
??悲觀鎖，也是基于數(shù)據(jù)庫(kù)的鎖機(jī)制實(shí)現(xiàn)。 傳統(tǒng)的關(guān)系型數(shù)據(jù)庫(kù)里邊就用到了很多這種鎖機(jī)制，比如行鎖，表鎖等，讀鎖，寫鎖等，都是在做操作之前先上鎖。

??樂觀鎖 ，他對(duì)世界比較樂觀，認(rèn)為別人訪問正在改變的數(shù)據(jù)的概率是很低的，所以直到修改完成，準(zhǔn)備提交所做的修改到數(shù)據(jù)庫(kù)的時(shí)候才會(huì)將數(shù)據(jù)鎖住， 當(dāng)你讀取以及改變?cè)搶?duì)象時(shí)并不加鎖，完成更改后釋放。樂觀鎖不能解決臟讀的問題。
??樂觀鎖加鎖的時(shí)間要比悲觀鎖短，大大提升了大并發(fā)量下的系統(tǒng)整體性能表現(xiàn)。
??樂觀鎖的實(shí)現(xiàn)方式：
??1、大多是基于數(shù)據(jù)版本（Version ）記錄機(jī)制實(shí)現(xiàn)， 需要為每一行數(shù)據(jù)增加一個(gè)版本標(biāo)識(shí)（也就是每一行數(shù)據(jù)多一個(gè)字段 version），每次更新數(shù)據(jù)都要更新對(duì)應(yīng)的版本號(hào)+1。
??工作原理：讀出數(shù)據(jù)時(shí)，將此版本號(hào)一同讀出，之后更新時(shí)，對(duì)此版本號(hào)加一。 此時(shí)，將提交數(shù)據(jù)的版本信息與數(shù)據(jù)庫(kù)表對(duì)應(yīng)記錄的當(dāng)前版本信息進(jìn)行比對(duì)，如果提交的數(shù)據(jù)版本號(hào)大于數(shù)據(jù)庫(kù)表當(dāng)前版本號(hào)，則予以更新，否則認(rèn)為是過期數(shù)據(jù)，不得不重新讀取該對(duì)象并作出更改。
??假設(shè)數(shù)據(jù)庫(kù)中帳戶信息表中有一個(gè)version 字段，當(dāng)前值為 1；而當(dāng)前帳戶余額字段（balance）為 $100。
??1）、操作員 A 此時(shí)將其讀出（version=1），并從其帳戶余額中扣除 $50（$100-$50）。
??2）、在操作員 A 操作的過程中，操作員 B 也讀入此用戶信息（version=1），并從其帳戶余額中扣除 $20（$100-$20）。
??3） 操作員 A 完成了修改工作，將數(shù)據(jù)版本號(hào)加一（version=2），連同帳戶扣除后余額（balance=$50），提交至數(shù)據(jù)庫(kù)更新，此時(shí)由于提交數(shù)據(jù)版本大于數(shù)據(jù)庫(kù)記錄當(dāng)前版本，數(shù)據(jù)被更新，數(shù)據(jù)庫(kù)記錄 version 更新為 2。
??4） 操作員 B 完成了操作，也將版本號(hào)加一（version=2）試圖向數(shù)據(jù)庫(kù)提交數(shù)據(jù)（balance=$80），但此時(shí)比對(duì)數(shù)據(jù)庫(kù)記錄版本時(shí)發(fā)現(xiàn)，操作員 B 提交的數(shù)據(jù)版本號(hào)為2，數(shù)據(jù)庫(kù)記錄當(dāng)前版本也為 2 ，不滿足“提交版本必須大于記錄當(dāng)前版本才能執(zhí)行更新“的樂觀鎖策略，因此，操作員 B 的提交被駁回。
??這樣，就避免了操作員 B 用基于 version=1 的舊數(shù)據(jù)修改的結(jié)果覆蓋操作員 A 的操作結(jié)果的可能。
??從上面的例子可以看出，樂觀鎖機(jī)制避免了長(zhǎng)事務(wù)中的數(shù)據(jù)庫(kù)加鎖開銷（操作員 A 和操作員 B 操作過程中，都沒有對(duì)數(shù)據(jù)庫(kù)數(shù)據(jù)加鎖），大大提升了大并發(fā)量下的系統(tǒng)整體性能表現(xiàn)。

??2、使用時(shí)間戳來實(shí)現(xiàn)
??同樣是在需要樂觀鎖控制的 table 中增加一個(gè)字段，名稱無所謂，字段類型使用時(shí)間戳（timestamp），和上面的 version 類似，也是在更新提交的時(shí)候檢查當(dāng)前數(shù)據(jù)庫(kù)中數(shù)據(jù)的時(shí)間戳和自己更新前取到的時(shí)間戳進(jìn)行對(duì)比，如果一致則 OK，否則就是版本沖突。
??悲觀鎖和樂觀鎖的適用場(chǎng)景：
??如果并發(fā)量不大，可以使用悲觀鎖解決并發(fā)問題；但如果系統(tǒng)的并發(fā)量非常大的話，悲觀鎖定會(huì)帶來非常大的性能問題，所以我們就要選擇樂觀鎖定的方法。

58、數(shù)據(jù)庫(kù)存儲(chǔ)過程

58.1、存儲(chǔ)過程

??存儲(chǔ)過程：就是一些編譯好了的 sql 語句，這些 SQL 語句代碼像一個(gè)方法一樣實(shí)現(xiàn)一些功能（對(duì)單表或多表的增刪改查），然后再給這個(gè)代碼塊取一個(gè)名字，在用到這個(gè)功能的時(shí)候調(diào)用他就行了。
??優(yōu)點(diǎn)：
??1、存儲(chǔ)過程因?yàn)?SQL 語句已經(jīng)預(yù)編譯過了，因此運(yùn)行的速度比較快。
??2、存儲(chǔ)過程在服務(wù)器端運(yùn)行，減少客戶端的壓力。
??3、允許模塊化程序設(shè)計(jì)，就是說只需要?jiǎng)?chuàng)建一次過程，以后在程序中就可以調(diào)用該過程任意次，類似方法的復(fù)用。
??4、減少網(wǎng)絡(luò)流量，客戶端調(diào)用存儲(chǔ)過程只需要傳存儲(chǔ)過程名和相關(guān)參數(shù)即可，與傳輸 SQL 語句相比自然數(shù)據(jù)量少了很多。
??5、增強(qiáng)了使用的安全性，充分利用系統(tǒng)管理員可以對(duì)執(zhí)行的某一個(gè)存儲(chǔ)過程進(jìn)行權(quán)限限制，從而能夠?qū)崿F(xiàn)對(duì)某些數(shù)據(jù)訪問的限制，避免非授權(quán)用戶對(duì)數(shù)據(jù)的訪問，保證數(shù)據(jù)的安全。程序員直接調(diào)用存儲(chǔ)過程，根本不知道表結(jié)構(gòu)是什么，有什么字段，沒有直接暴露表名以及字段名給程序員。
??缺點(diǎn)：
??調(diào)試麻煩（至少?zèng)]有像開發(fā)程序那樣容易），可移植性不靈活（因?yàn)榇鎯?chǔ)過程是依賴于具體的數(shù)據(jù)庫(kù)）。

58.2、定義與調(diào)用存儲(chǔ)過程

create procedure insert_Student (_name varchar(50), _age int, out _id int)
begin
insert into student value(null,_name,_age);
select max(stuId) into _id from student;
end;

call insert_Student('wfz',23,@id);
select @id;

調(diào)用存儲(chǔ)過程

public class JdbcTest {
    public static void main(String[] args) {
        // TODO Auto-generated method stub
        Connection cn = null;
        CallableStatement cstmt = null;
        try {
            //這里最好不要這么干，因?yàn)轵?qū)動(dòng)名寫死在程序中了
            Class.forName("com.mysql.jdbc.Driver");
            //實(shí)際項(xiàng)目中，這里應(yīng)用DataSource數(shù)據(jù)，如果用框架，這個(gè)數(shù)據(jù)源不需要我們編碼創(chuàng)建
            cn = DriverManager.getConnection("jdbc:mysql:///test","root","root");
            cstmt = cn.prepareCall("{call insert_Student(?,?,?)}");
            cstmt.registerOutParameter(3,Types.INTEGER);
            cstmt.setString(1, "wangwu");
            cstmt.setInt(2, 25);
            cstmt.execute();
            //get第幾個(gè)，不同的數(shù)據(jù)庫(kù)不一樣，建議不寫
            System.out.println(cstmt.getString(3));

        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();

        } finally {
            try {
                if(cstmt != null)
                    cstmt.close();
                if(cn != null)
                    cn.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }
}