Trying to stop PK errors on inserts. Is SERIALIZABE the way to go?

Question

Post reply

Trying to stop PK errors on inserts. Is SERIALIZABE the way to go?

terry999

SSCertifiable

Points: 5029

September 12, 2019 at 11:25 pm

#3679672

Hi

I need to add of list of customers to a table from a file probably a CSV.

The file import will probably happen once a week/month.

I want to stop any other process that is trying to insert getting the same id, but hopefully not stop any updates/selects on previous rows.

CREATE TABLE dbo.tblCustomer(CustID int NOT NULL,CustName varchar(20) NOT NULL,
CONSTRAINT PK_tblCustomer PRIMARY KEY CLUSTERED (CustID ASC))

insert into tblcustomer (CustID, CustName) values(1, 'Andy'), (2, 'Bob'), (3, 'Charlie'), (4,'Diane')
-- Populate temp table #File with contents of File
Create table #File (CustName varchar(20) NOT NULL);
insert into #File (CustName) values ('Edward'), ('Fiona'), ('Grace');

As this is a 3rd Party DB, I cannot make CustID an Identity field

CustID int IDENTITY(1,1) NOT NULL

Or create a new table to store next CustID

My solution

BEGIN transaction;

insert into tblcustomer 
SELECT ISNULL(MAX(custid),0) + T.offset, T.Custname
FROM  tblCustomer (SERIALIZABLE )
CROSS APPLY (
select CustName, offset = ROW_NUMBER() OVER( ORDER BY CustName) from #File 
) as T
GROUP BY T.offset, T.Custname

COMMIT;

-- Do it many times while executing single insert in another connection to try and get PK error
GO 1000

I ran below while it was inserting from the temp table 1000 times - NO PK errors

insert into tblcustomer (CustID, CustName) 
select MAX(CustID) + 1, 'zzzz' from tblcustomer

SEEMS to work or was I just lucky?

Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246686 More actions · Answer 1

Why not use an IDENTITY column for this?

Or create a SEQUENCE and use that.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

terry999 SSCertifiable Points: 5029 More actions · Answer 2

Hi Phil

It’s a 3rd Party DB/App, I cannot change the tables.

Phil Parkin SSC Guru Points: 246686 More actions · Answer 3

terry999 wrote:

Hi Phil
It’s a 3rd Party DB/App, I cannot change the tables.

Apologies for not taking the time to read your original post thoroughly enough!

Can you not emulate exactly what the third-party app is doing when it creates new rows? Presumably, they have already cracked any locking/concurrency issues? (Or, given the Max(Id) + 1 design, perhaps not!!)

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

terry999 SSCertifiable Points: 5029 More actions · Answer 4

No problem Phil - thank you for looking at my post

I jumped the gun somewhat, had chat with colleague. It stores next available CustID on the Application server in a "Custom DB format" - NOT MSSQL not sure what though.

So hopefully I can call their API something like "reserve_customers ##" where ## is the number of customers I have in my file.

For my knowledge/understanding

I was using MAX(id) + 1 in another query window while I was executing, the (SERIALIZABLE) insert 1000 times on a different connection.

I got no PK clashes. I don't know if this was by luck or by design?

Would

insert into tblA (id) 
select max(id) + 1 from tblA

Cause PK clashes if many connections where executing it? I'm sure it would be a disaster for performance but wouldn't it be safe?

Phil Parkin SSC Guru Points: 246686 More actions · Answer 5

Using serializable should do the trick in your example, I agree.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Ken McKelvey SSCoach Points: 18765 More actions · Answer 6

Most third party applications that I come across have there own import routines. It may be worth doing some research to see if the app you are using has import routines. Also, the license of third party apps tends to stop you directly modifying the data so you had better check this as well.

terry999 SSCertifiable Points: 5029 More actions · Answer 7

Hi Ken - you're right - couldn't agree more.

I've contacted the vendor, I would never do this without checking first.

In this case It was more for my understanding, I've found out how little I knew about locking isolation levels