项目上一个业务需要对某条数据库记录加锁,使用的是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 JDBCTest { 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(); System.out.println("Thread 1 before"+ Calendar.getInstance().getTime()); st.executeQuery("select * from table_name where id=63 for update"); TimeUnit.SECONDS.sleep(5); connection.commit(); System.out.println("Thread 1 after "+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(); System.out.println("Thread 2 before"+Calendar.getInstance().getTime()); st.executeQuery("select * from table_name where id=63 for update"); TimeUnit.SECONDS.sleep(10); connection.commit(); System.out.println("Thread 2 after"+Calendar.getInstance().getTime()); } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } catch (InterruptedException e) { e.printStackTrace(); } } }).start(); } }
刚开始测试的时候发现行级锁未生效
涉及到一下几个方面:
首先要使用connection.setAutoCommit(false);开启事务,执行结束关闭事务即connection.commit();
其次,通过 TimeUnit.Second.sleep(5); 设置当前线程sleep一定的时间
执行结果如下:
Thread 2 beforeThu Sep 01 15:50:50 CST 2016
Thread 1 beforeThu Sep 01 15:50:50 CST 2016
Thread 1 after Thu Sep 01 15:50:55 CST 2016
Thread 2 afterThu Sep 01 15:51:05 CST 2016
thread 1 先获得行级锁执行结束
thread 2 在 Thread 1执行结束后然后执行从开始到结束一共花了15s
参考资料 http://www.cnblogs.com/jukan/p/5670950.html