MySQL database row-level lock usage (ii)

Another requirement on the project is:

You need to lock the current table when doing the statistics to not update the current table record

Directly on the code

 Packagecom.robert.RedisTest;Importjava.sql.Connection;ImportJava.sql.DriverManager;Importjava.sql.SQLException;Importjava.sql.Statement;ImportJava.util.Calendar;ImportJava.util.concurrent.TimeUnit; Public classJdbccountlocktest {Private StaticString Jdbcurl = "Jdbc:mysql://localhost:3306/test"; Private StaticString username = "Test"; Private StaticString password = "Test";  Public Static voidMain (string[] args) {NewThread (NewRunnable () { Public voidrun () {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= ' a ' 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 (); NewThread (NewRunnable () { Public voidrun () {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= ' 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 (); }}

Sleep 1s first in thread 2

The thread 1 is guaranteed to execute first

Then thread 2 can perform the update operation after the thread 1 execution finishes.

The results of the implementation are as follows:

Thread 1 Commit Thu Sep 15:58:51 CST 2016
Thread 2 executeQuery finish Thu Sep 15:58:51 CST 2016
Thread 2 executeupdate finish Thu Sep 15:58:51 CST 2016

MySQL database row-level lock usage (ii)