Continuing a Transaction After Primary Key Violation Error

Continuing a transaction after primary key violation error

You can also use SAVEPOINTs in a transaction.

Pythonish pseudocode is illustrate from the application side:

database.execute("BEGIN")
foreach data_row in input_data_dictionary:
    database.execute("SAVEPOINT bulk_savepoint")
    try:
        database.execute("INSERT", table, data_row)
    except:
        database.execute("ROLLBACK TO SAVEPOINT bulk_savepoint")
        log_error(data_row)
        error_count = error_count + 1
    else:
        database.execute("RELEASE SAVEPOINT bulk_savepoint")

if error_count > error_threshold:
    database.execute("ROLLBACK")
else:
    database.execute("COMMIT")

Edit: Here's an actual example of this in action in psql based on a slight variation of the example in the documentation (SQL statements prefixed by ">"):

> CREATE TABLE table1 (test_field INTEGER NOT NULL PRIMARY KEY);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "table1_pkey" for table "table1"
CREATE TABLE

> BEGIN;
BEGIN
> INSERT INTO table1 VALUES (1);
INSERT 0 1
> SAVEPOINT my_savepoint;
SAVEPOINT
> INSERT INTO table1 VALUES (1);
ERROR:  duplicate key value violates unique constraint "table1_pkey"
> ROLLBACK TO SAVEPOINT my_savepoint;
ROLLBACK
> INSERT INTO table1 VALUES (3);
INSERT 0 1
> COMMIT;
COMMIT
> SELECT * FROM table1;  
 test_field 
------------
          1
          3
(2 rows)

Note that the value 3 was inserted after the error, but still inside the same transaction!

The documentation for SAVEPOINT is at http://www.postgresql.org/docs/8.4/static/sql-savepoint.html.

Continue after primary key violation error

You could use an instead of insert trigger. Inside the trigger, do an Insert into the table, where not exists CommReceipt.CR_Key = inserted.CR_Key.

Create trigger T_CommReceiptInsteadOfInsert on CommReceipt
Instead of Insert
As
Begin

--Insert duplicate records into another table
Insert Into CommReceipt_Duplicates(CR_Key, ...)
Select CR_Key, ...
From inserted i
Where exists (select * from CommReceipt c Where c.CR_Key = i.CR_Key)

--Insert non duplicate records
Insert Into CommReceipt(CR_Key, ...)
Select CR_Key, ...
From inserted i
Where not exists (select * from CommReceipt c Where c.CR_Key = i.CR_Key)

End

Data Flow Violation of PRIMARY KEY

I am not hundred present OK with you solution but if we want to fix the problem that you have I have several things to tell you.

1 : You have Primary key violation because you have insert command two times in your data flow.

2 : Instead of your data flow I suggest you some things like that.

Add Three Data Flow task in your control flow.

We transfer all Insert in the first one.

Then we will transfer all update command. You can improuve your select command for this step by selecting just the last updated for each sourceId command instead of selecting all updated tag.

The last step is for deleting data.

But I think you should use this solution instead of yours.

1: Create a temp table in destination database with the same schema that we have in source database.

2 : truncate this table each time.

3 : Bring all Data that from source server to a Temp table in destination database.(All inserted all updated)

4 : Use T/SQL merge command to merge Temp Table and your destination table.

5 : Run a Delete command to delete the rows in destination.

Note: If you have two fields in your source table as (InsertedDate/UppdatedDate)
you do not need trigger in your table for inser and update. you can use those columns with the last transfer date (you have to register it some where each time you are transferring the data)

Avoid Violation of PRIMARY KEY in delete\insert transaction?

We have 2 transactions with the same @Id
We don't have a record with @Id in the table my_table

This is the scenario that Range Locking is intended to address. Under SERIALIZABLE or with the HOLDLOCK hint you will take key range locks on empty key ranges.

eg

drop table if exists tt
go
create table tt(id int primary key, a int)

begin transaction
    delete from tt 
    where id = 1
    select resource_type, request_mode, request_status
    from sys.dm_tran_locks
    where request_session_id = @@spid 
commit transaction

go

begin transaction
    delete from tt with (holdlock) 
    where id = 1
    select resource_type, request_mode, request_status
    from sys.dm_tran_locks
    where request_session_id = @@spid 
commit transaction

outputs

(0 rows affected)
resource_type                                                request_mode                                                 request_status
------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------
OBJECT                                                       IX                                                           GRANT

(1 row affected)


(0 rows affected)
resource_type                                                request_mode                                                 request_status
------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------
KEY                                                          RangeX-X                                                     GRANT
OBJECT                                                       IX                                                           GRANT

(2 rows affected)

Violation of PRIMARY KEY constraint. Cannot insert duplicate key in object

I'm pretty sure pk_OrderID is the PK of AC_Shipping_Addresses

And you are trying to insert a duplicate via the _Order.OrderNumber?

Do a

select * from AC_Shipping_Addresses where pk_OrderID = 165863;

or select count(*) ....

Pretty sure you will get a row returned.

It is telling you that you are already using pk_OrderID = 165863 and cannot have another row with that value.

if you want to not insert if there is a row

insert into table (pk, value) 
select 11 as pk, 'val' as value 
where not exists (select 1 from table where pk = 11)

---

Related Topics