java解決mysql鎖表（mysql表鎖住了）

本文目錄一覽：

• 1、MySQL資料庫表鎖定的幾種方法實現
• 2、mysql存儲過程出現鎖表鎖行的情況怎麼解決
• 3、java程序中如何實現對mysql資料庫中表的鎖定
• 4、Java如何實現對Mysql資料庫的行鎖

MySQL資料庫表鎖定的幾種方法實現

如果兩個程序都向表中寫數據顯然會造成很大的麻煩，甚至會有意外情況發生。如果表正由一個程序寫入，同時進行讀取的另一個程序也會產生混亂的結果。

鎖定表的方法

防止客戶機的請求互相干擾或者伺服器與維護程序相互干擾的方法主要有多種。如果你關閉資料庫，就可以保證伺服器

和myisamchk和isamchk之間沒有交互作用。但是停止伺服器的運行並不是一個好注意，因為這樣做會使得沒有故障的資料庫和表也不可用。本節主

要討論的過程，是避免伺服器和myisamchk或isamchk之間的交互作用。實現這種功能的方法是對錶進行鎖定。

伺服器由兩種表的鎖定方法：

1.內部鎖定

內部鎖定可以避免客戶機的請求相互干擾——例如，避免客戶機的SELECT查詢被另一個客戶機的UPDATE查詢所干擾。也可以利用內部鎖定機制防止伺服器在利用myisamchk或isamchk檢查或修復表時對錶的訪問。

語法：鎖定表：LOCK TABLES

tbl_name {READ | WRITE},[ tbl_name {READ | WRITE},…]

解鎖表：UNLOCKTABLESLOCKTABLES為當前線程鎖定表。UNLOCK TABLES釋放被當前線程持有的任何鎖。當線程發出另外一個LOCK

TABLES時，或當伺服器的連接被關閉時，當前線程鎖定的所有表自動被解鎖。

如果一個線程獲得在一個表上的一個READ鎖，該線程(和所有其他線程)只能從表中讀。如果一個線程獲得一個表上的一個WRITE鎖，那麼只有持鎖的線程READ或WRITE表，其他線程被阻止。

每個線程等待(沒有超時)直到它獲得它請求的所有鎖。

WRITE鎖通常比READ鎖有更高的優先順序，以確保更改儘快被處理。這意味著，如果一個線程獲得READ鎖，並且然後另外一個線程請求一個WRITE鎖,

隨後的READ鎖請求將等待直到WRITE線程得到了鎖並且釋放了它。

顯然對於檢查，你只需要獲得讀鎖。再者鍾情跨下，只能讀取表，但不能修改它，因此他也允許其它客戶機讀取表。對於修復，你必須獲得些所以防止任何客戶機在你對錶進行操作時修改它。

2.外部鎖定

伺服器還可以使用外部鎖定(文件級鎖)來防止其它程序在伺服器使用表時修改文件。通常，在表的檢查操作中伺服器

將外部鎖定與myisamchk或isamchk作合使用。但是，外部鎖定在某些系統中是禁用的，因為他不能可靠的進行工作。對運行myisamchk或

isamchk所選擇的過程取決於伺服器是否能使用外部鎖定。如果不使用，則必修使用內部鎖定協議。

如果伺服器用–skip-locking選項運行，則外部鎖定禁用。該選項在某些系統中是預設的，如Linux。可以通過運行mysqladmin

variables命令確定伺服器是否能夠使用外部鎖定。檢查skip_locking變數的值並按以下方法進行：

◆

如果skip_locking為off，則外部鎖定有效您可以繼續並運行人和一個實用程序來檢查表。伺服器和實用程序將合作對錶進行訪問。但是，運行任何

一個實用程序之前，應該使用mysqladmin flush-tables。為了修復表，應該使用表的修復鎖定協議。

◆

如果skip_locaking為on，則禁用外部鎖定，所以在myisamchk或isamchk檢查修復表示伺服器並不知道，最好關閉伺服器。如果堅

持是伺服器保持開啟狀態，月確保在您使用此表示沒有客戶機來訪問它。

mysql存儲過程出現鎖表鎖行的情況怎麼解決

行鎖的等待

在介紹如何解決行鎖等待問題前，先簡單介紹下這類問題產生的原因。產生原因簡述：當多個事務同時去操作（增刪改）某一行數據的時候，MySQL 為了維護 ACID 特性，就會用鎖的形式來防止多個事務同時操作某一行數據，避免數據不一致。只有分配到行鎖的事務才有權力操作該數據行，直到該事務結束，才釋放行鎖，而其他沒有分配到行鎖的事務就會產生行鎖等待。如果等待時間超過了配置值（也就是 innodb_lock_wait_timeout 參數的值，個人習慣配置成 5s，MySQL 官方默認為 50s），則會拋出行鎖等待超時錯誤。

如上圖所示，事務 A 與事務 B 同時會去 Insert 一條主鍵值為 1 的數據，由於事務 A 首先獲取了主鍵值為 1 的行鎖，導致事務 B 因無法獲取行鎖而產生等待，等到事務 A 提交後，事務 B 才獲取該行鎖，完成提交。這裡強調的是行鎖的概念，雖然事務 B 重複插入了主鍵，但是在獲取行鎖之前，事務一直是處於行鎖等待的狀態，只有獲取行鎖後，才會報主鍵衝突的錯誤。當然這種 Insert 行鎖衝突的問題比較少見，只有在大量並發插入場景下才會出現，項目上真正常見的是 updatedelete 之間行鎖等待，這裡只是用於示例，原理都是相同的。

三、產生的原因根據我之前接觸到的此類問題，大致可以分為以下幾種原因

java程序中如何實現對mysql資料庫中表的鎖定

方法1：用mysql命令鎖住表.

public void test() {  

    String sql = “lock tables aa1 write”;  

    // 或String sql = “lock tables aa1 read”;   

    // 如果想鎖多個表 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());  

    }  

}

對於read lock 和 write lock官方說明：

1.如果一個線程獲得一個表的READ鎖定，該線程（和所有其它線程）只能從該表中讀取。

如果一個線程獲得一個表的WRITE鎖定，只有保持鎖定的線程可以對錶進行寫入。

其它的線程被阻止，直到鎖定被釋放時為止。

2.當您使用LOCK TABLES時，您必須鎖定您打算在查詢中使用的所有的表。

雖然使用LOCKTABLES語句獲得的鎖定仍然有效，但是您不能訪問沒有被此語句鎖定的任何的表。

同時，您不能在一次查詢中多次使用一個已鎖定的表——使用別名代替，

在此情況下，您必須分別獲得對每個別名的鎖定。

對與read lock 和 write lock個人說明：

1.read lock 和 write lock 是線程級(表級別).

2.在同一個會話中加了read lock鎖. 只能對這個表進行讀操作.對這個表以外的任何錶都無法進行增、刪、改、查的操作.

但是在不同會話中，只能對加了read lock的表進行讀操作.但可以對read lock以外的表進行增、刪、改、查的操作.

3.在同一個會話中加了write lock鎖.只能對這個表進行讀、寫操作.對這個表以外的任何錶都無法進行增、刪、改、查的操作.

但是在不同會話中,無法對加了write lock的表進行讀、寫操作.但可以對write lock以外的表進行增、刪、改、查的操作.

4.如果表中使用了別名.(SELECT * FROM aa1 AS byname_table)

在對aa1加鎖時，必須把別名加上去(lock tables aa1 as byname_table read)

在同一個會話中.必須使用別名進行查詢.

在不同的會話中.可以不需要使用別名進行查詢.

5.在多個會話中可以對同一個表進行lock read操作.但不能在多個會話中對同一個表進行lock write操作（這些鎖將等待已鎖的表釋放自身的線程鎖）

如果多個會話對同一個表進行lock read操作.那麼在這些會話中,也只能對以鎖的表進行讀操作.

6.如果要你鎖住了一個表，需要嵌套查詢.你必須使用別名，並且，要鎖定別名.

例如.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程序中,要想解鎖,需要調用 unlock tables來解鎖.

如果沒有調用unlock tables.

關閉connection 、程序結束 、調用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 屬於行級鎖和頁級鎖

2.for update 排它鎖,lock in share mode 共享鎖

3.對於記錄鎖.必須開啟事務.

4.行級鎖定事實上是索引記錄的鎖定.只要是用索引掃描的行（或沒索引全表掃描的行）,都將被鎖住.

5.在不同的隔離級別下還會使用next-key locking演算法.即所掃描的行之間的「間隙」也會也鎖住(在Repeatable read和Serializable隔離級別下有間隙鎖).

6.在mysql中共享鎖的含義是:在被共享鎖鎖住的行,即使內容被修改且並沒有提交.在另一個會話中依然看到最新修改的信息.

在同一會話中加上了共享鎖.可以對這個表以及這個表以外的所有表進行增、刪、改、查的操作.

在不同的會話中.可以查到共享鎖鎖住行的最新消息.但是在Read Uncommitted隔離級別下不能對鎖住的表進行刪，

改操作.（需要等待鎖釋放才能操作…）

在Read Committed隔離級別下不能對鎖住的表進行刪，改操作.（需要等待鎖釋放才能操作…）

在Repeatable read隔離級別下不能對鎖住行進行增、刪、改操作.（需要等待鎖釋放才能操作…）

在Serializable隔離級別下不能對鎖住行進行增、刪、改操作.  （需要等待鎖釋放才能操作…）

7.在mysql中排他鎖的含義是:在被排它鎖鎖住的行,內容修改並沒提交，在另一個會話中不會看到最新修改的信息。

在不同的會話中.可以查到共享鎖鎖住行的最新消息.但是Read Uncommitted隔離級別下不能對鎖住的表進行刪，

改操作.（需要等待鎖釋放才能操作…）

在Read Committed隔離級別下不能對鎖住的表進行刪，改操作.（需要等待鎖釋放才能操作…）

在Repeatable read隔離級別下不能對鎖住行進行增、刪、改操作.（需要等待鎖釋放才能操作…）

在Serializable隔離級別下不能對鎖住行進行增、刪、改操作. （需要等待鎖釋放才能操作…）

8.在同一個會話中的可以疊加多個共享鎖和排他鎖.在多個會話中,需要等待鎖的釋放.

9.SQL中的update 與 for update是一樣的原理.

10.等待超時的參數設置:innodb_lock_wait_timeout=50 (單位秒).

11.任何可以觸發事務提交的命令,都可以關閉共享鎖和排它鎖.

Java如何實現對Mysql資料庫的行鎖

下面通過一個例子來說明

場景如下：

用戶賬戶有餘額，當發生交易時，需要實時更新餘額。這裡如果發生並發問題，那麼會造成用戶餘額和實際交易的不一致，這對公司和客戶來說都是很危險的。

那麼如何避免：

網上查了下，有以下兩種方法：

1、使用悲觀鎖

當需要變更餘額時，通過代碼在事務中對當前需要更新的記錄設置for update行鎖，然後開始正常的查詢和更新操作

這樣，其他的事務只能等待該事務完成後方可操作

當然要特別注意，如果使用了Spring的事務註解，需要配置一下：

!– (事務管理)transaction manager, use JtaTransactionManager for global tx —

bean id=”transactionManager”

class=”org.springframework.jdbc.datasource.DataSourceTransactionManager”

property name=”dataSource” ref=”dataSource” /

/bean

!– 使用annotation定義事務 —

tx:annotation-driven transaction-manager=”transactionManager” /

在指定代碼處添加事務註解

@Transactional

@Override

public boolean increaseBalanceByLock(Long userId, BigDecimal amount)

throws ValidateException {

long time = System.currentTimeMillis();

//獲取對記錄的鎖定

UserBalance balance = userBalanceDao.getLock(userId);

LOGGER.info(“[lock] start. time: {}”, time);

if (null == balance) {

throw new ValidateException(

ValidateErrorCode.ERRORCODE_BALANCE_NOTEXIST,

“user balance is not exist”);

}

boolean result = userBalanceDao.increaseBalanceByLock(balance, amount);

long timeEnd = System.currentTimeMillis();

LOGGER.info(“[lock] end. time: {}”, timeEnd);

return result;

}

MyBatis中的鎖定方式，實際測試該方法確實可以有效控制，不過在大並發量的情況下，可能會有性能問題吧

select id=”getLock” resultMap=”BaseResultMap” parameterType=”java.lang.Long”

![CDATA[

select * from user_balance where id=#{id,jdbcType=BIGINT} for update;

]]

/select

2、使用樂觀鎖

這個方法也同樣可以解決場景中描述的問題（我認為比較適合併不頻繁的操作）：

設計表的時候增加一個version（版本控制欄位），每次需要更新餘額的時候，先獲取對象，update的時候根據version和id為條件去更新，如果更新回來的數量為0，說明version已經變更

需要重複一次更新操作，如下：sql腳本

update user_balance set Balance = #{balance,jdbcType=DECIMAL},Version = Version+1 where Id = #{id,jdbcType=BIGINT} and Version = #{version,jdbcType=BIGINT}

這是一種不使用資料庫鎖的方法，解決方式也很巧妙。當然，在大量並發的情況下，一次扣款需要重複多次的操作才能成功，還是有不足之處的。不知道還有沒有更好的方法。