Mysql insertion check solution in a concurrent environment and concurrent mysql insertion Solution

Mysql insertion check solution in a concurrent environment and concurrent mysql insertion Solution

Business Background:
The basic business scenario is like this. To request data (vehicle vin information) to enter the interface, you must first determine its status in the database. If this vin does not exist in the database, or the vin status bit is "1 (completed)". After performing some check operations, insert the data to the database. In this case, the vin status is 0, and the manual processing interface is called, return the result in 10 minutes and set the status to 1. If its status bit is "0 (processing)", the operation is rejected and a prompt message is returned.
In A single-threaded environment, such A business is normal. However, when A concurrent access interface is used, two request AB with the same vin will be entered at the same time, deny B. However, in the concurrent environment, when B executes the check status, A is not inserted, so AB enters the database, and the data is wrong.

Solution 1:
The first thing that comes to mind is using SQL to add a unique index to the corresponding fields of the database to ensure consistency. If duplicate data is inserted, catch the exception and make a prompt.

ALTER tableName ADD UNIQUE [indexName] ON (tableColumns(length))

However, due to business restrictions, vin can be repeated in the database, and multiple duplicate data entries are queried to the latest, so it is not allowed to add a unique index on vin.

Solution 2:
Using mysql transaction operations, you can check whether there is and insert as a transaction for processing. When the check fails, no insert is performed. I searched from the Internet. The general idea is as follows:

Public static void StartTransaction (Connection con, String [] sqls) throws Exception {try {// transaction start con. setAutoCommit (false); // sets that the connection is not automatically submitted, that is, all operations performed with this connection are not updated to the database sm = con. createStatement (); // create a Statement object // execute the passed SQL Statement for (int I = 0; I <sqls. length; I ++) {sm.exe cute (sqls [I]); // execute the statement for adding a transaction} con. commit (); // submit it to the database for processing // transaction end // capture exceptions in the execution SQL statement group} catch (SQLException e) {try {System. out. println ("transaction execution Failed. Roll back! \ N "); con. rollback (); // if an exception occurs in the previous statement, roll back and cancel all the operations previously executed} catch (SQLException e1) {e1.printStackTrace ();}} finally {sm. close ();}}

However, in this case, the concurrency problem is still not solved. In this case, two operations are converted into an atomic SQL operation, which can be used to insert two data consistency statements at the same time, but is not suitable for requirements.

Since the SQL layer does not solve the problem, we should consider solving the problem from the java concurrent programming direction.
Solution 3:
Java solves the concurrency problem. The first thing that comes to mind is to use built-in locks or reentrant locks. The basic syntax is as follows:
· Built-in lock:
Because it is processed in Servlet, synchronized (this) is used to directly process the Business Code. In this case, only one thread can access the Business Code of this segment in concurrency:

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

· Reentrant lock:
It is equivalent to a more flexible built-in lock. Here it is basically the same as the 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 vin does not exist insert} finally {lock. unlock ();}}}

After testing, this solution is feasible. The final reason why it is not used is that the locking method is used directly. There are too many lock codes, which affects the efficiency.

Solution 4:
Set a query Map, store data before insertion, and delete data after insertion. The Code is as follows:

ConcurrentHashMap <String, String> vinMap = new ConcurrentHashMap <String, String> (); if (vinMap. containsKey (vin) {// todo1: After the vin request is completed, remove this vinNo from vinInRequestMap // todo2: The returned result is in the query.} vinMap. put (vin, ""); // todo3: insert vin to the database vinMap. remove (vin );}

This solution basically satisfies the business requirements. The only problem is that the update time of the interface must be staggered with the business time. Otherwise, the update interface will clear the vinMap, resulting in data confusion and errors in the database.

The above is all the content of this article, hoping to help you learn.

Articles you may be interested in:

• Mysql queue for concurrent reading
• Solutions to MYSQL stress caused by php concurrency
• Implementation Code of inserting data from one table into another table in MySql
• In-depth explanation of mysql Concurrent Insertion Optimization
• Mysql inserts Chinese characters into full question mark ??? Solution
• Example of auto-increment ID for concurrent Security using mysql Transaction Features
• Using shell scripts to insert data in mysql tables in batches
• How to solve the problem of concurrent UPDATE using SELECT + UPDATE in MySQL
• Insert multiple data records using one mysql statement
• High-performance and high-concurrency counters in MySQL (such as the number of clicks in articles)