首页 > 代码库 > mysql数据库行级锁的使用(二)

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数据库行级锁的使用(二)