1、在mysql數(shù)據(jù)庫(kù)中如何鎖定一行數(shù)據(jù)，保證不被其他的操作影響。
成都創(chuàng)新互聯(lián)自2013年起，先為陸豐等服務(wù)建站，陸豐等地企業(yè)，進(jìn)行企業(yè)商務(wù)咨詢服務(wù)。為陸豐企業(yè)網(wǎng)站制作PC+手機(jī)+微官網(wǎng)三網(wǎng)同步一站式服務(wù)解決您的所有建站問(wèn)題。
2、從對(duì)數(shù)據(jù)的操作類型分為讀鎖和寫鎖。從對(duì)數(shù)據(jù)操作的粒度來(lái)分：表鎖和行鎖。
3、現(xiàn)在我們建立一個(gè)表來(lái)演示數(shù)據(jù)庫(kù)的行鎖講解。
4、行鎖基本演示如下圖所示。
5、如果兩個(gè)會(huì)話操作的是不同的行，就不會(huì)互相阻塞了。
方法1：用mysql命令鎖住表.
public?void?test()?{??
String?sql?=?"lock?tables?aa1?write";??
//?或String?sql?=?"lock?tables?aa1?read";???
//?如果想鎖多個(gè)表?lock?tables?aa1?read?,aa2?write?,?.....???
String?sql1?=?"select?*?from?aa1?";??
String?sql2?=?"unlock?tables";??
try?{??
this.pstmt?=?conn.prepareStatement(sql);??
this.pstmt1?=?conn.prepareStatement(sql1);??
this.pstmt2?=?conn.prepareStatement(sql2);??
pstmt.executeQuery();??
pstmt1.executeQuery();??
pstmt2.executeQuery();??
}?catch?(Exception?e)?{??
System.out.println("異常"?+?e.getMessage());??
}??
}
對(duì)于read lock 和 write lock官方說(shuō)明：
1.如果一個(gè)線程獲得一個(gè)表的READ鎖定，該線程（和所有其它線程）只能從該表中讀取。
如果一個(gè)線程獲得一個(gè)表的WRITE鎖定，只有保持鎖定的線程可以對(duì)表進(jìn)行寫入。
其它的線程被阻止，直到鎖定被釋放時(shí)為止。
2.當(dāng)您使用LOCK TABLES時(shí)，您必須鎖定您打算在查詢中使用的所有的表。
雖然使用LOCKTABLES語(yǔ)句獲得的鎖定仍然有效，但是您不能訪問(wèn)沒有被此語(yǔ)句鎖定的任何的表。
同時(shí)，您不能在一次查詢中多次使用一個(gè)已鎖定的表——使用別名代替，
在此情況下，您必須分別獲得對(duì)每個(gè)別名的鎖定。
對(duì)與read lock 和 write lock個(gè)人說(shuō)明：
1.read lock 和 write lock 是線程級(jí)(表級(jí)別).
2.在同一個(gè)會(huì)話中加了read lock鎖. 只能對(duì)這個(gè)表進(jìn)行讀操作.對(duì)這個(gè)表以外的任何表都無(wú)法進(jìn)行增、刪、改、查的操作.
但是在不同會(huì)話中，只能對(duì)加了read lock的表進(jìn)行讀操作.但可以對(duì)read lock以外的表進(jìn)行增、刪、改、查的操作.
3.在同一個(gè)會(huì)話中加了write lock鎖.只能對(duì)這個(gè)表進(jìn)行讀、寫操作.對(duì)這個(gè)表以外的任何表都無(wú)法進(jìn)行增、刪、改、查的操作.
但是在不同會(huì)話中,無(wú)法對(duì)加了write lock的表進(jìn)行讀、寫操作.但可以對(duì)write lock以外的表進(jìn)行增、刪、改、查的操作.
4.如果表中使用了別名.(SELECT * FROM aa1 AS byname_table)
在對(duì)aa1加鎖時(shí)，必須把別名加上去(lock tables aa1 as byname_table read)
在同一個(gè)會(huì)話中.必須使用別名進(jìn)行查詢.
在不同的會(huì)話中.可以不需要使用別名進(jìn)行查詢.
5.在多個(gè)會(huì)話中可以對(duì)同一個(gè)表進(jìn)行l(wèi)ock read操作.但不能在多個(gè)會(huì)話中對(duì)同一個(gè)表進(jìn)行l(wèi)ock write操作（這些鎖將等待已鎖的表釋放自身的線程鎖）
如果多個(gè)會(huì)話對(duì)同一個(gè)表進(jìn)行l(wèi)ock read操作.那么在這些會(huì)話中,也只能對(duì)以鎖的表進(jìn)行讀操作.
6.如果要你鎖住了一個(gè)表，需要嵌套查詢.你必須使用別名，并且，要鎖定別名.
例如.lock table aa1 read ,aa1 as byname_table read;
select * from aa1 where id in (select * from aa1 as xx??where id=2);
7.解鎖必須用unlock tables;
另:
在JAVA程序中,要想解鎖,需要調(diào)用 unlock tables來(lái)解鎖.
如果沒有調(diào)用unlock tables.
關(guān)閉connection 、程序結(jié)束 、調(diào)用GC 都能解鎖.
方法2：用記錄鎖鎖表.
public?void?test()?{??
String?sql?=?"select?*?from?aa1?for?update";???
//?select?*?from?aa1?lock?in?share?mode;???
try?{??
conn.setAutoCommit(false);??
this.pstmt?=?conn.prepareStatement(sql);??
pstmt.executeQuery();??
}?catch?(Exception?e)?{??
System.out.println("異常"?+?e.getMessage());??
}??
}
1.for update 與 lock in share mode 屬于行級(jí)鎖和頁(yè)級(jí)鎖
2.for update 排它鎖,lock in share mode 共享鎖
3.對(duì)于記錄鎖.必須開啟事務(wù).
4.行級(jí)鎖定事實(shí)上是索引記錄的鎖定.只要是用索引掃描的行（或沒索引全表掃描的行）,都將被鎖住.
5.在不同的隔離級(jí)別下還會(huì)使用next-key locking算法.即所掃描的行之間的“間隙”也會(huì)也鎖住(在Repeatable read和Serializable隔離級(jí)別下有間隙鎖).
6.在mysql中共享鎖的含義是:在被共享鎖鎖住的行,即使內(nèi)容被修改且并沒有提交.在另一個(gè)會(huì)話中依然看到最新修改的信息.
在同一會(huì)話中加上了共享鎖.可以對(duì)這個(gè)表以及這個(gè)表以外的所有表進(jìn)行增、刪、改、查的操作.
在不同的會(huì)話中.可以查到共享鎖鎖住行的最新消息.但是在Read Uncommitted隔離級(jí)別下不能對(duì)鎖住的表進(jìn)行刪，
改操作.（需要等待鎖釋放才能操作...）
在Read Committed隔離級(jí)別下不能對(duì)鎖住的表進(jìn)行刪，改操作.（需要等待鎖釋放才能操作...）
在Repeatable read隔離級(jí)別下不能對(duì)鎖住行進(jìn)行增、刪、改操作.（需要等待鎖釋放才能操作...）
在Serializable隔離級(jí)別下不能對(duì)鎖住行進(jìn)行增、刪、改操作.??（需要等待鎖釋放才能操作...）
7.在mysql中排他鎖的含義是:在被排它鎖鎖住的行,內(nèi)容修改并沒提交，在另一個(gè)會(huì)話中不會(huì)看到最新修改的信息。
在不同的會(huì)話中.可以查到共享鎖鎖住行的最新消息.但是Read Uncommitted隔離級(jí)別下不能對(duì)鎖住的表進(jìn)行刪，
改操作.（需要等待鎖釋放才能操作...）
在Read Committed隔離級(jí)別下不能對(duì)鎖住的表進(jìn)行刪，改操作.（需要等待鎖釋放才能操作...）
在Repeatable read隔離級(jí)別下不能對(duì)鎖住行進(jìn)行增、刪、改操作.（需要等待鎖釋放才能操作...）
在Serializable隔離級(jí)別下不能對(duì)鎖住行進(jìn)行增、刪、改操作. （需要等待鎖釋放才能操作...）
8.在同一個(gè)會(huì)話中的可以疊加多個(gè)共享鎖和排他鎖.在多個(gè)會(huì)話中,需要等待鎖的釋放.
9.SQL中的update 與 for update是一樣的原理.
10.等待超時(shí)的參數(shù)設(shè)置:innodb_lock_wait_timeout=50 (單位秒).
11.任何可以觸發(fā)事務(wù)提交的命令,都可以關(guān)閉共享鎖和排它鎖.
MySQL 5.1支持對(duì)MyISAM和MEMORY表進(jìn)行表級(jí)鎖定，對(duì)BDB表進(jìn)行頁(yè)級(jí)鎖定，對(duì)InnoDB表進(jìn)行行級(jí)鎖定。
如果不能同時(shí)插入，為了在一個(gè)表中進(jìn)行多次INSERT和SELECT操作，可以在臨時(shí)表中插入行并且立即用臨時(shí)表中的記錄更新真正的表。
這可用下列代碼做到：
mysql LOCK TABLES real_table WRITE, insert_table WRITE;
mysql INSERT INTO real_table SELECT * FROM insert_table;
mysql TRUNCATE TABLE insert_table;
mysql UNLOCK TABLES;
關(guān)于mysql中的樂觀鎖和悲觀鎖面試的時(shí)候被問(wèn)到的概率還是比較大的。
mysql的悲觀鎖：
其實(shí)理解起來(lái)非常簡(jiǎn)單，當(dāng)數(shù)據(jù)被外界修改持保守態(tài)度，包括自身系統(tǒng)當(dāng)前的其他事務(wù)，以及來(lái)自外部系統(tǒng)的事務(wù)處理，因此，在整個(gè)數(shù)據(jù)處理過(guò)程中，將數(shù)據(jù)處于鎖定狀態(tài)。悲觀鎖的實(shí)現(xiàn)，往往依靠數(shù)據(jù)庫(kù)提供的鎖機(jī)制，但是也只有數(shù)據(jù)庫(kù)層提供的鎖機(jī)制才能真正保證數(shù)據(jù)訪問(wèn)的排他性，否則，即使在自身系統(tǒng)中實(shí)現(xiàn)了加鎖機(jī)制，也無(wú)法保證外部系統(tǒng)不會(huì)修改數(shù)據(jù)。
來(lái)點(diǎn)實(shí)際的，當(dāng)我們使用悲觀鎖的時(shí)候我們首先必須關(guān)閉mysql數(shù)據(jù)庫(kù)的自動(dòng)提交屬性，因?yàn)镸ySQL默認(rèn)使用autocommit模式，也就是說(shuō)，當(dāng)你執(zhí)行一個(gè)更新操作后，MySQL會(huì)立刻將結(jié)果進(jìn)行提交。
關(guān)閉命令為：set autocommit=0;
悲觀鎖可以使用select…for update實(shí)現(xiàn)，在執(zhí)行的時(shí)候會(huì)鎖定數(shù)據(jù)，雖然會(huì)鎖定數(shù)據(jù)，但是不影響其他事務(wù)的普通查詢使用。此處說(shuō)普通查詢就是平時(shí)我們用的：select * from table 語(yǔ)句。在我們使用悲觀鎖的時(shí)候事務(wù)中的語(yǔ)句例如：
//開始事務(wù)
begin;/begin work;/start transaction; (三選一)
//查詢信息
select * from order where id=1 for update;
//修改信息
update order set name='names';
//提交事務(wù)
commit;/commit work;(二選一)
此處的查詢語(yǔ)句for update關(guān)鍵字，在事務(wù)中只有SELECT ... FOR UPDATE 或LOCK IN SHARE MODE 同一條數(shù)據(jù)時(shí)會(huì)等待其它事務(wù)結(jié)束后才執(zhí)行，一般的SELECT查詢則不受影響。
執(zhí)行事務(wù)時(shí)關(guān)鍵字select…for update會(huì)鎖定數(shù)據(jù)，防止其他事務(wù)更改數(shù)據(jù)。但是鎖定數(shù)據(jù)也是有規(guī)則的。
查詢條件與鎖定范圍：
1、具體的主鍵值為查詢條件
比如查詢條件為主鍵ID=1等等，如果此條數(shù)據(jù)存在，則鎖定當(dāng)前行數(shù)據(jù)，如果不存在，則不鎖定。
2、不具體的主鍵值為查詢條件
比如查詢條件為主鍵ID1等等，此時(shí)會(huì)鎖定整張數(shù)據(jù)表。
3、查詢條件中無(wú)主鍵
會(huì)鎖定整張數(shù)據(jù)表。
4、如果查詢條件中使用了索引為查詢條件
明確指定索引并且查到，則鎖定整條數(shù)據(jù)。如果找不到指定索引數(shù)據(jù)，則不加鎖。
悲觀鎖的確保了數(shù)據(jù)的安全性，在數(shù)據(jù)被操作的時(shí)候鎖定數(shù)據(jù)不被訪問(wèn)，但是這樣會(huì)帶來(lái)很大的性能問(wèn)題。因此悲觀鎖在實(shí)際開發(fā)中使用是相對(duì)比較少的。
mysql的樂觀鎖：
相對(duì)悲觀鎖而言，樂觀鎖假設(shè)數(shù)據(jù)一般情況下不會(huì)造成沖突，所以在數(shù)據(jù)進(jìn)行提交更新的時(shí)候，才會(huì)對(duì)數(shù)據(jù)的沖突與否進(jìn)行檢測(cè)，如果發(fā)現(xiàn)沖突，則讓返回用戶錯(cuò)誤的信息，讓用戶決定如何去做。
一般來(lái)說(shuō)，實(shí)現(xiàn)樂觀鎖的方法是在數(shù)據(jù)表中增加一個(gè)version字段，每當(dāng)數(shù)據(jù)更新的時(shí)候這個(gè)字段執(zhí)行加1操作。這樣當(dāng)數(shù)據(jù)更改的時(shí)候，另外一個(gè)事務(wù)訪問(wèn)此條數(shù)據(jù)進(jìn)行更改的話就會(huì)操作失敗，從而避免了并發(fā)操作錯(cuò)誤。當(dāng)然，還可以將version字段改為時(shí)間戳，不過(guò)原理都是一樣的。
例如有表student，字段：
id,name,version
1 a 1
當(dāng)事務(wù)一進(jìn)行更新操作：update student set name='ygz' where id = #{id} and version = #{version};
此時(shí)操作完后數(shù)據(jù)會(huì)變?yōu)閕d = 1,name = ygz,version = 2，當(dāng)另外一個(gè)事務(wù)二同樣執(zhí)行更新操作的時(shí)候，卻發(fā)現(xiàn)version != 1，此時(shí)事務(wù)二就會(huì)操作失敗，從而保證了數(shù)據(jù)的正確性。
悲觀鎖和樂觀鎖都是要根據(jù)具體業(yè)務(wù)來(lái)選擇使用，本文僅作簡(jiǎn)單介紹。
重慶分公司
重慶分公司
