On duplicate key update causes primary key non-continuous auto-increment Solution

On duplicate key update causes primary key non-continuous auto-increment Solution

This feature needs to be implemented in recent projects: To measure the service duration of each software, the client sends a message. If the user's software has been updated for use, if no record is added, the Code is as follows:

<! -- Batch Save the software usage time table -->
<Update id = "saveApp" parameterType = "java. util. List">
<Foreach collection = "appList" item = "item" index = "index" separator = ";">
Insert into app_table (userName, app, duration)
Values (# {userName}, # {item. app}, # {item. duration })
On duplicate key update duration = duration + # {item. duration}
</Foreach>
</Update>

For efficiency, the on duplicate key update function is used to automatically determine whether the table is updated or newly added. After a period of time, it is found that the table's primary key id (which has been set to continuous auto-increment) is not a continuous auto-increment, this causes the id auto-increment to be too fast and the maximum value is almost exceeded. We found that one feature of on duplicate key update is, when each update is performed, the id is automatically increased by 1. For example, if the maximum id is 5, an update operation is performed, and then an insert operation is performed, the value of id is 7 instead of 6.
There are two ways to solve this problem. The first is to modify the mode in innodb_autoinc_lock_mode, and the second is to split the statement into two actions: update and operation.

The first method: innodb_autoinc_lock_mode has three moderate modes: 0, 1, and 2. The default configuration of mysql5 is 1,

0 locks the table every time an auto-increment id is assigned.

1. The table will be locked only when bulk insert is used. In simple insert, only one light-weight mutex will be used, which is higher than zero concurrency.

2. I did not take a closer look. It seems that many of them are not secure.

When the default value of the database is 1, the above phenomenon will occur .. on duplicate key update, simple auto-increment IDs are added, whether insert or update occurs.

Because the Code has a large amount of data and requires a large amount of data to be updated and added, you cannot use the 0 mode. You can only split the database code into two steps: update and insert, the first step is to update the usage duration based on the user name and software name. The Code is as follows:

<Update id = "updateApp" parameterType = "App">
Update app_table
Set duration = duration + # {duration}
Where userName = # {userName} and appName = # {appName}
</Update>

Then, based on the returned value, if the returned value is greater than 0, data is no longer inserted after the update is successful. If the returned value is less than 0, the data needs to be inserted. The Code is as follows:

<Insert id = "saveApp" keyProperty = "id" useGeneratedKeys = "true" parameterType = "App">
Insert into app_table (userName, appName, duration)
Values (# {userName}, # {appName}, # {duration })
</Insert>

In this way, the efficiency of the solution is definitely affected. I don't know if data will be lost. Please try again after a while!