Solve the problem of multi-thread calling sql stored procedure

• Scenario:

Our program is now multi-threaded, and I need to insert the data in the temporary table into TABLE_M, but at this time other threads may be inserting, so I can't use our previous solution (select max(oid) from Tuning.TABLE_M..., then to maintain the relationship between the main and sub-tables, change the self-increment attribute of the OID of TABLE_M, insert, and then add the self-increment attribute...)

I want to do this now:

1,) Insert the temporary data into TABLE_M (OID is self-incrementing) (Insert Into ....select ...), then I get select Scope_Identity() as @MaxTABLE_MOID, and then modify the temporary data according to this @MaxTABLE_MOIDTABLE_MOID in the table.

1.1,) (1,) Will this scheme have the following problems:

A thread, 1-10 total 10 records,

B thread, 1-10 total 10 records

When thread A is inserted,

Thread B is also reinserting

A thread and B thread are interactive insertion, A thread inserts one, B thread inserts one

It works like this:

For example, the OID of TABLE_M at this time is 100

The OID of the first TABLE_M inserted by thread A becomes 101

The OID of the first TABLE_M inserted by thread B becomes 102

When I finished inserting thread A, the OID of the last record inserted by thread A is 120

At this time, my Temp will go to this 120 to modify the word table relationship

TABLE_M (OID=120) is no problem when A is inserted

TABLE_M(OID=119) is inserted by B, but it is associated with the ninth record of the temporary table corresponding to thread A

Sad!!!

1.2,) Are we going to use With(lock) when inserting?

2,) I also have an idea, that is, after the insertion is completed, the generated OIDs of the batch of data inserted into TABLE_M are queried according to the conditions, and then these OIDs are matched with temp (matching in order, because the insertion of temp must haveorder insertion), and then modify the TABLE_MOID in temp, so that the child data can be inserted into TABLE_M_LteCell, TABLE_M_GsmCell, TABLE_M_TdsCell.

• DBA provides solutions

We can make a separate table for OID auto-increment maintenance, which can be temporarily called the MAX_OID table.The easiest way is to just record: the current largest OID.

So we follow the following steps when doing multi-threaded operations

1. Get the value in the MAX_OID table, so you can get the self-incrementing starting serial number‍

2. Get the maximum number of rows in the temporary table at the time of this insertion.Do not use select count(1) from table, use the following statement for maximum performance.

sql statement to directly get the number of table rows:

select sum(row_count)

from sys.dm_db_partition_stats

where index_id<1 and object_id=object_id('table name')

3. Using max_oid + the number of rows in the temporary table, you can get the maximum MAXOID number of the main table in the future after the temporary table is inserted into the main table

4. Update the MAX_OID table.Change the data in the table to: ‍max_oid+the number of rows in the temporary table‍

Special note: In order to ensure transaction consistency, the above steps need to be completed in one transaction, and the following statements are required

SET XACT_ABORT ON

begin tran

Execute the sql statement (that is, the logical operations of 1-4 above)

commit

Although the above four steps are verbose in text, the actual operation will be very fast.All system tables are used to read, which must be at the millisecond level and will not affect performance.

In this way, each thread reads the value in the MAX_OID table, and there will be no conflict.‍

Such as the following example:

The current maximum value of MAX_OID is 100.‍

The temporary table of thread one is 30 rows

The temporary table of thread two is 40 rows

Thread one:

1. Read the MAX_OID table.Get 100‍

2. Get the number of temporary table rows: 30

3. The predicted maxoid is 100+30=130

4. Change MAX_OID to 130.‍

Thread two:

1. Read the MAX_OID table.get 130

2. Get the number of temporary table rows: 40

3. The predicted maxoid is 130+40=170

4. Change MAX_OID to 170.

Note: The MAX_OID obtained by thread two must be 130, not 100. Because thread one has updated this value.And the update process is locked.Thread two can read less than 100.‍

This avoids conflicts.