Multi-threaded calls generate primary key serial number stored procedure creates a primary key conflict problem solution

Encounter a primary key conflict problem when developing multithreaded tests that insert data

The questions are described in detail as follows:

--------------------------------------------------------------

Call Procedure_insert

Procedure_insert

Begin

Call procedure (get serial number)

Insert into table values (serial number as ID, other columns);

End

Serial Number Stored procedure:

Update tab Statistics Field +1

Update tab Final serial number set type + Month Day + complement 0 bit + statistic field

Select final serial number;//as Primary key

---------------------------------------------------------------

Problem: Primary key conflicts generated by multithreading concurrency

Write a Java multithreaded call test, found that when added to the transaction will not be the problem, whether in the stored procedure plus or the code process plus the transaction can be, seemingly the problem is resolved, the scenario is as follows:

-----------------------------------------------------------------

Program code con.begin Start transaction

Call Procedure_insert

Procedure_insert

Begin

Call procedure (get serial number)

Insert into table values (serial number as ID, other columns);

End

Program code Con.commit ();

Serial Number Stored procedure:

Update tab Statistics Field +1

Update tab Final serial number set type + Month Day + complement 0 bit + statistic field

Select final serial number;//as Primary key

-----------------------------------------------------------------

But where does the problem occur, since it is a primary key conflict and only happens if there is no transaction under multithreading concurrency, test the stored procedure that generates the primary key separately, as follows:

Found the problem, sure enough, the multi-threaded without the transaction caused by

Since the test results, but also know the solution, it seems to solve the problem, then there are new problems, why not add the transaction multi-threaded call generated serial number of the stored procedure will produce the same primary key?

It's about how my serial number was generated.

Procedure

Begin

UPDATE statement;

UPDATE statement stitching;

SELECT statement;

End

Because of multithreading concurrency, which causes the update of the serial number is asynchronous, the first statement may be executed at the same time, and then execute the second sentence, resulting in the end only the last primary key; The process of generating the primary key is not a whole, resulting in the generation of the same primary key.

Multi-threaded calls generate primary key serial number stored procedure creates a primary key conflict problem solution