unique index, delete/insert under transaction

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

unique index, delete/insert under transaction

Author

Topic

leonfin
Starting Member

6 Posts

Posted - 2007-09-08 : 23:15:48

Hi,

I've encountered the following problem. Under transaction i delete records from a table that has UNIQUE INDEX on a column. After this, I try to insert new/updated records that have been deleted. I get error saying:
Cannot insert duplicate key row in object 'Test' with unique index 'idxPrimaryTest'.

For testing purposes I even tried deleting every record from the table before inserting same records back on next line all under same transaction and still above error comes back. However, when I do count() of items in the table before/after delete, the count correctly reflects deleted records.

What am I missing? Transaction doesn't apply to indexes? I've tried every transaction isolation level (so the cause is not transaction level).

Thank you in advance for any clarifications on this!

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-09-09 : 00:03:21

You may put them in separate transaction since deletion is not completed until you commit the transaction.

leonfin
Starting Member

6 Posts

Posted - 2007-09-09 : 00:28:42

Thanks for the reply. But select count() from the deleted table reflects correct number of records. Are indexes not updated until transaction is commited?

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2007-09-09 : 04:07:13

enclose your delete in a begin tran and commit tran
then issue your insert below

although you should save the deleted records in a temp table in case something goes wrong and you can reinsert them back.

--------------------
keeping it simple...

Kristen
Test

22859 Posts

Posted - 2007-09-09 : 05:52:00

"After this, I try to insert new/updated records that have been deleted"

We do the following:

1) Delete records not present in New Staging Data
2) Update records present in both the Table AND the New Staging Data
3) Insert records present in the New Staging Data, but NOT in the Table data

AT (2) we usually ONLY Update records that are DIFFERENT between Staging data and Table (using a massive! WHERE clause)

Kristen

leonfin
Starting Member

6 Posts

Posted - 2007-09-09 : 11:29:09

jen: "enclose your delete in a begin tran and commit tran then issue your insert below"

This is one of the steps I've tried (sorry, forgot them in my post). Same exact behavior. (error with duplicate index).

I'm interested in how unique indexes or indexes behave under transaction and delete. When are indexes re-applied. Is this change visible within the transaction or not. How to explain the behavior I'm seeing. When reading from the table, deleted records are not present. But when inserting deleted records, it errors with duplicate index.

Kristen
Test

22859 Posts

Posted - 2007-09-09 : 14:10:38

"Same exact behavior. (error with duplicate index). "

Then I reckon there genuinely is a duplicate.

Maybe the index was created originally with "Ignore duplicate keys", and now you have stumble across one that actually has multiple entries in the index?

Perhaps review with

SELECT MyKeyCol, COUNT(*)
FROM MyTable
GROUP BY MyKeyCol
HAVING COUNT(*) > 1

Or maybe your "re-insert" is trying to insert two rows with the same Key, or inadvertently a key that already exists, but was not part of what was deleted.

My gut feeling is that this much more likely than a problem with delete-key and reinsert-same-key issue

Kristen

leonfin
Starting Member

6 Posts

Posted - 2007-09-10 : 00:10:43

Kristen: but I also tried "delete from MyTable" and then inserting under same transaction and I get duplicate error. If I delete everything from the table, than there is no way there can be a duplicate unless indexes are not recalculated untill transaction is committed.

leonfin
Starting Member

6 Posts

Posted - 2007-09-10 : 00:13:17

Kristen: also, the key is not the same. It's a different key. Only two records are being inserted, and it's easy to check their keys manually.

Kristen
Test

22859 Posts

Posted - 2007-09-10 : 02:57:22

Well I have no problem with Delete/Re-insert from Unique index within a transaction. In the example below (1) fails, as expected, and (2) succeeded without error:


CREATE TABLE #TEMP
(
	MyID INT IDENTITY NOT NULL,
	MyUnique INT NOT NULL,
	PRIMARY KEY
	(
		MyID
	)
)
GO
CREATE UNIQUE INDEX IxTemp ON #TEMP
(
	MyUnique
)
GO

INSERT INTO #TEMP(MyUnique) SELECT 1
GO
SELECT * FROM #TEMP
GO

PRINT '(1)Generate Duplicate Key error'
GO
INSERT INTO #TEMP(MyUnique) SELECT 1
GO
PRINT '(1)Done'
PRINT ''
PRINT ''
GO


PRINT '(2)Try Delete/Insert replacement transaction'
GO
BEGIN TRANSACTION
GO
PRINT 'Delete ...'
GO
DELETE #TEMP WHERE MyUnique = 1
GO
PRINT 'Insert ...'
GO
INSERT INTO #TEMP(MyUnique) SELECT 1
GO
PRINT 'Commit ...'
GO
COMMIT
GO
PRINT '(2)Done'
PRINT ''
PRINT ''

DROP TABLE #TEMP
GO

leonfin
Starting Member

6 Posts

Posted - 2007-09-10 : 16:07:26

Kristen: after your test case I tried to analyze the data again.
This is error on my part when trying to analyze the error with duplicates index. There were 2 same records being inserted. Sorry for wasting anyones time and thanks a lot for your help!

Kristen
Test

22859 Posts

Posted - 2007-09-10 : 16:13:01

Glad you got it sorted.

That particular scenario is quite hard to find because when you do "Does Import-data exist in Target table?" the answer is No! because the issue is "Is Import data duplicated?"

We used to Insert data directly into the target table, now we tend to loop round calling our "Save" Sproc, which contains lots of validation logic, so that we can get the full range of error/validation messages we have developed over the years, Downside is that calling an SProc for each record to be inserted is glacially slow compared to just doing a

INSERT INTO MyTable
SELECT *
FROM ImportTable

but after lots of time wasted tracking down problems I've given up caring about Import performance

... and instead just say "It will be done when it is done"

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources