关于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

关于mysql数据库行级锁的使用(一)的相关教程结束。