关于mysql数据库行级锁的使用(一)

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