Mysql Insert checking scheme in concurrent environment _mysql

Business background:
The basic business scenario is that the request data (vehicle VIN information) goes into the interface, you need to determine its state in the database, if the VIN does not exist in the library, or if the VIN status is 1 (completed), then after performing some checks, insert the data into the database, and the new VIN status is 0 , the human processing interface is invoked, the result is returned in 10 minutes, and the state is set to 1. If its status is "0 (in Process)", the action is dismissed and the prompt is returned.
In a single-threaded environment, such a business is not a problem, however, when the concurrent access interface, there will be a simultaneous entry of two VIN same request AB, the normal situation should insert a A, dismiss a B. However, in concurrent environments, B performs a check state when a is not plugged in, so AB enters the database and the data is wrong.

Solution One:
The first thought is to use SQL processing, the corresponding field of the database to add a unique index to ensure consistency. If you insert duplicate data, catch the exception and make a prompt.

ALTER tablename ADD UNIQUE [IndexName] On (tablecolumns (length))

However, because of the business restrictions, VIN can be duplicated in the library, many duplicate data query the latest, so can no longer add a unique index on VIN.

Solution Two:
Using the MySQL transaction operation, the check for presence and insertion is handled as a transaction and is not inserted when the check fails. Search from the Internet for a moment, the general idea is as follows:

public static void StartTransaction (Connection con, string[] sqls) throws Exception { 
    try { 
      //transaction start 
      Con.setau Tocommit (false);  Setting up a connection is not automatically committed, that is, operations performed with this connection are not updated to the database 
      SM = con.createstatement ();////Create Statement Object 

      //Execute incoming SQL statement for 
      (int i = 0; i < sqls.length; i++) { 
        sm.execute (sqls[i]);//Executing the statement to add things 
      } 
      con.commit ();  Submit to Database processing 
      //Transaction End 

    //capture exceptions in the Execute SQL statement group   
    (SQLException e) { 
      try { 
        System.out.println (" Transaction execution failed, rollback! \ n "); 
        Con.rollback (); If the previous statement has an exception, rollback, cancel all previous operations 
      } catch (SQLException E1) { 
        e1.printstacktrace (); 
      } 
    } finally { C22/>sm.close (); 
    } 
  

But this actually does not solve the concurrency problem, so that only two operations into an atomic SQL operation, can be used to insert two data consistency, but not suitable for requirements.

Since the SQL level does not solve the problem, consider solving it from Java's concurrent programming direction.
Solution Three:
Java solves concurrency problems, the first thought is to use built-in locks or reentrant locks, the basic syntax is as follows:
• Built-in locks:
Because it is handled in the servlet, you use synchronized (this) to process the business code directly so that only one thread can access the business code in concurrent situations:

Synchronized (this) {
  //todo1: Check if vin exists
  //todo2: If there is no insert Vin
}

• Reentrant Locks:
Equivalent to a more flexible built-in lock, which is essentially the same as a built-in lock

public class Dashengcallback extends HttpServlet {
  private static reentrantlock lock= new Reentrantlock ();
  protected void doget (HttpServletRequest request, httpservletresponse response) {
    lock.lock ();
    try{
      //todo1: Check if vin exists
      //todo2: If there is no insert Vin
    }finally{
      lock.unlock ()
    ;
}}

After testing, this scheme is feasible, and ultimately not used because of the direct use of this method of locking, locking code too much, affecting efficiency.

Solution Four:
Set up a query map, store the data before inserting, delete the data after inserting, the code is as follows:

    Concurrenthashmap<string, string> vinmap=new concurrenthashmap<string,string> ();
    if (Vinmap.containskey (VIN)) {
      //Todo1:vin request is completed, delete this Vinno
      //Todo2 from Vininrequestmap: Return query
    }
    Vinmap.put (VIN, "");
    TODO3: Insert Vin to database
    Vinmap.remove (VIN);
  }

This solution basically satisfies the business demand, the only problem is that the update time of the interface should be staggered with the business time, otherwise the update interface will empty the vinmap, resulting in the data confusion in the library, error.

The above is the entire content of this article, I hope to help you learn.