Table t_dept_total (dept_no varchar2 (20), op_date date, the total number, constrant pk_dept_total primary key (dept_no op_date));
Use the merge into data written to:
The merge into t_dept_total a using: (select deptno dept_no, to_date (: opdate, 'yyyy - MM - dd) op_date, 1 inc from dual) on b (a. d. ept_no=b.d ept_no and a.o p_date=b.o p_date)
The when matched then update the set total=total + b.i nc
When not matched then insert (dept_no op_date, total) values (b.d ept_no, b.o p_date, b.i nc)
: the deptno and opdate for dynamic incoming parameters,
Now use multithreading incoming and execute the statement above, if the incoming parameters: the deptno and: opdate, same primary key conflict error will occur and execute it again after this kind of situation if the error will be successful,
Oracle is how to deal with? Why not perform the update to insert one of the other? Large amount of data when multithreaded how should use in order to avoid a primary key repeat?

CodePudding user response:

Use pessimistic locks, the select... For update again after the merge, but the premise is multithreaded merge, in any of a primary key of the same data is correct in our business,

CodePudding user response:

reference 1st floor minsic78 response:

use pessimistic locks, the select... For update again after the merge, but the premise is multithreaded merge, regardless of which a primary key of the same data is correct, in business

The select... For update is the premise of data already exists, is now the table is empty, no data, merge1000 pen the same data at the same time, oracle may think that at this moment 1000 data to perform insert operations (the primary key to the increase of, there will be a 1000 article in addition to the primary key is different, other fields have the same record), so the select... For update can't fundamentally solved,

CodePudding user response:

The

refer to the second floor 1 fcsoft reply:

Quote: refer to 1st floor minsic78 response:

Use pessimistic locks, the select... For update again after the merge, but the premise is multithreaded merge, regardless of which a primary key of the same data is correct, in business

The select... For update is the premise of data already exists, is now the table is empty, no data, merge1000 pen the same data at the same time, oracle may think that at this moment 1000 data to perform insert operations (the primary key to the increase of, there will be a 1000 article in addition to the primary key is different, other fields have the same record), so the select... For update can't fundamentally solved,

Let it error bai, you since the program processing, so that the business, no matter which thread insert data, can be considered to be correct, because there is no logic to deal with first, an error is an error, at least a primary key has been entered,