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

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

57.1、有 2 種基本的鎖類型

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

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

57.2八匠、mysql 鎖的粒度（即鎖的級別）

??MySQL 各存儲引擎使用了三種類型（級別）的鎖定機制：行級鎖定絮爷，頁級鎖定和表級鎖定。
??1梨树、表級鎖坑夯， 直接鎖定整張表，在你鎖定期間抡四，其它進程無法對該表進行寫操作柜蜈。如果你是寫鎖，則其它進程則讀也不允許指巡。特點：開銷小淑履，加鎖快；不會出現(xiàn)死鎖藻雪；鎖定粒度最大秘噪，發(fā)生鎖沖突的概率最高，并發(fā)度最低勉耀。
??MyISAM 存儲引擎采用的是表級鎖指煎。
??有 2 種模式：表共享讀鎖和表獨占寫鎖。加讀鎖的命令：lock table 表名 read;去掉鎖的命令：unlock tables瑰排。
??支持并發(fā)插入：支持查詢和插入操作并發(fā)進行（在表尾并發(fā)插入）贯要。
??鎖調(diào)度機制：寫鎖優(yōu)先暖侨。一個進程請求某個 MyISAM 表的讀鎖椭住，同時另一個進程也請求同一表的寫鎖，MySQL 如何處理呢字逗？答案是寫進程先獲得鎖京郑。

??2宅广、行級鎖， 僅對指定的記錄進行加鎖些举，這樣其它進程還是可以對同一個表中的其它記錄進行操作跟狱。特點：開銷大，加鎖慢户魏；會出現(xiàn)死鎖驶臊；鎖定粒度最小，發(fā)生鎖沖突的概率最低叼丑，并發(fā)度也最高关翎。
??InnoDB 存儲引擎既支持行級鎖，也支持表級鎖鸠信，但默認(rèn)情況下是采用行級鎖纵寝。

??3、頁級鎖星立，一次鎖定相鄰的一組記錄爽茴。開銷和加鎖時間界于表鎖和行鎖之間；會出現(xiàn)死鎖绰垂；鎖定粒度界于表鎖和行鎖之間室奏，并發(fā)度一般。

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

57.3、按鎖的機制分：有悲觀鎖和樂觀鎖

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

??樂觀鎖 晴玖，他對世界比較樂觀读存，認(rèn)為別人訪問正在改變的數(shù)據(jù)的概率是很低的，所以直到修改完成呕屎，準(zhǔn)備提交所做的修改到數(shù)據(jù)庫的時候才會將數(shù)據(jù)鎖住让簿， 當(dāng)你讀取以及改變該對象時并不加鎖，完成更改后釋放秀睛。樂觀鎖不能解決臟讀的問題尔当。
??樂觀鎖加鎖的時間要比悲觀鎖短，大大提升了大并發(fā)量下的系統(tǒng)整體性能表現(xiàn)蹂安。
??樂觀鎖的實現(xiàn)方式：
??1椭迎、大多是基于數(shù)據(jù)版本（Version ）記錄機制實現(xiàn)， 需要為每一行數(shù)據(jù)增加一個版本標(biāo)識（也就是每一行數(shù)據(jù)多一個字段 version）田盈，每次更新數(shù)據(jù)都要更新對應(yīng)的版本號+1畜号。
??工作原理：讀出數(shù)據(jù)時，將此版本號一同讀出允瞧，之后更新時简软，對此版本號加一蛮拔。 此時，將提交數(shù)據(jù)的版本信息與數(shù)據(jù)庫表對應(yīng)記錄的當(dāng)前版本信息進行比對替饿，如果提交的數(shù)據(jù)版本號大于數(shù)據(jù)庫表當(dāng)前版本號语泽，則予以更新贸典，否則認(rèn)為是過期數(shù)據(jù)视卢，不得不重新讀取該對象并作出更改。
??假設(shè)數(shù)據(jù)庫中帳戶信息表中有一個version 字段廊驼，當(dāng)前值為 1据过；而當(dāng)前帳戶余額字段（balance）為 $100。
??1）妒挎、操作員 A 此時將其讀出（version=1）绳锅，并從其帳戶余額中扣除 $50（$100-$50）。
??2）酝掩、在操作員 A 操作的過程中鳞芙，操作員 B 也讀入此用戶信息（version=1），并從其帳戶余額中扣除 $20（$100-$20）期虾。
??3） 操作員 A 完成了修改工作原朝，將數(shù)據(jù)版本號加一（version=2），連同帳戶扣除后余額（balance=$50）镶苞，提交至數(shù)據(jù)庫更新喳坠，此時由于提交數(shù)據(jù)版本大于數(shù)據(jù)庫記錄當(dāng)前版本，數(shù)據(jù)被更新茂蚓，數(shù)據(jù)庫記錄 version 更新為 2壕鹉。
??4） 操作員 B 完成了操作，也將版本號加一（version=2）試圖向數(shù)據(jù)庫提交數(shù)據(jù)（balance=$80）聋涨，但此時比對數(shù)據(jù)庫記錄版本時發(fā)現(xiàn)晾浴，操作員 B 提交的數(shù)據(jù)版本號為2，數(shù)據(jù)庫記錄當(dāng)前版本也為 2 牍白，不滿足“提交版本必須大于記錄當(dāng)前版本才能執(zhí)行更新“的樂觀鎖策略怠肋，因此，操作員 B 的提交被駁回淹朋。
??這樣笙各，就避免了操作員 B 用基于 version=1 的舊數(shù)據(jù)修改的結(jié)果覆蓋操作員 A 的操作結(jié)果的可能。
??從上面的例子可以看出础芍，樂觀鎖機制避免了長事務(wù)中的數(shù)據(jù)庫加鎖開銷（操作員 A 和操作員 B 操作過程中杈抢，都沒有對數(shù)據(jù)庫數(shù)據(jù)加鎖），大大提升了大并發(fā)量下的系統(tǒng)整體性能表現(xiàn)仑性。

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

58佳魔、數(shù)據(jù)庫存儲過程

58.1、存儲過程

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

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

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)用存儲過程

public class JdbcTest {
    public static void main(String[] args) {
        // TODO Auto-generated method stub
        Connection cn = null;
        CallableStatement cstmt = null;
        try {
            //這里最好不要這么干岸晦，因為驅(qū)動名寫死在程序中了
            Class.forName("com.mysql.jdbc.Driver");
            //實際項目中，這里應(yīng)用DataSource數(shù)據(jù)帅掘，如果用框架委煤，這個數(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第幾個堂油，不同的數(shù)據(jù)庫不一樣修档，建議不寫
            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();
            }
        }
    }
}