mysql資料庫行級鎖的使用（二）

標籤：

項目上的另外一個需求是：

在做統計的時候需要將當前表鎖定不能更新當前表記錄

直接上代碼

package com.robert.RedisTest;import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;import java.sql.Statement;import java.util.Calendar;import java.util.concurrent.TimeUnit;public class JDBCCountLockTest {        private static String jdbcUrl = "jdbc:mysql://localhost:3306/test";    private static String username = "test";    private static String password = "test";        public static void main(String[] args) {                new Thread(new Runnable(){            public void run(){                                    try {                    Class.forName("com.mysql.jdbc.Driver");                    Connection connection = DriverManager.getConnection(jdbcUrl,username,password);                    connection.setAutoCommit(false);                    Statement st = connection.createStatement();                    st.executeQuery("select count(1) num from table_name where mobile_phone = ‘13651969037‘ and rule_id=‘39‘ for update");                    TimeUnit.SECONDS.sleep(5);                    connection.commit();                    System.out.println("Thread 1 commit "+Calendar.getInstance().getTime());                } catch (ClassNotFoundException e) {                    e.printStackTrace();                } catch (SQLException e) {                    e.printStackTrace();                } catch (InterruptedException e) {                    e.printStackTrace();                }            }        }).start();                new Thread(new Runnable(){            public void run(){                                    try {                    Class.forName("com.mysql.jdbc.Driver");                    Connection connection = DriverManager.getConnection(jdbcUrl,username,password);                    connection.setAutoCommit(false);                    Statement st = connection.createStatement();                    TimeUnit.SECONDS.sleep(1);                    st.executeQuery("select * from table_name where id=4139 for update");                    System.out.println("Thread 2 executeQuery finish "+Calendar.getInstance().getTime());                    String update_sql_1 = "update table_name set rule_id=‘40‘  where id = ‘4139‘";                    st.executeUpdate(update_sql_1);                    System.out.println("Thread 2 executeUpdate finish "+Calendar.getInstance().getTime());                    connection.commit();                } catch (ClassNotFoundException e) {                    e.printStackTrace();                } catch (SQLException e) {                    e.printStackTrace();                    e.printStackTrace();                } catch (InterruptedException e) {                    e.printStackTrace();                }            }        }).start();    }}

在Thread 2中 先sleep 1s

保證了Thread 1先執行

然後在Thread 1執行結束之後，Thread 2才能執行更新操作。

執行結果如下：

Thread 1 commit Thu Sep 01 15:58:51 CST 2016
Thread 2 executeQuery finish Thu Sep 01 15:58:51 CST 2016
Thread 2 executeUpdate finish Thu Sep 01 15:58:51 CST 2016

mysql資料庫行級鎖的使用（二）