项目上一个业务需要对某条数据库记录加锁,使用的是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
05-11 10:48