locked
SQL DB Transaction fails when executing INSERT after DELETE, on duplicated PK RRS feed

  • Question

  • Hi,

    Your help is very much appreciated.

    When running a DELETE and then INSERT (BULK INSERT) sql commands , I am receiving an error

    “Cannot insert duplicate key”.

    It seems like the DELETE doesn’t actually delete the records, and therefore the INSERT fails.

    [Framework 4.0, SQL Server 2008].

    Heres' the code I'm using:

    public static bool RunInTransInternal(List<SqlCommand> cmds, string conn)

            {

                try

                {

                    using (TransactionScope scope = new TransactionScope(TransactionScopeOption.RequiresNew,

                        new TransactionOptions()

                        {

                            IsolationLevel = System.Transactions.IsolationLevel.Serializable,

                            Timeout = TimeSpan.FromHours(2)

                        }))

                    {

                        using (SqlConnection connection1 = new SqlConnection(conn))

                        {

                            connection1.Open();

                            for (i = 0; i < cmds.Count; i++)

                            {

                                cmds[i].Connection = connection1;

                                cmds[i].CommandTimeout = 36000;

                                cmds[i].ExecuteNonQuery();

                            }

                        }

                        scope.Complete();

                    }

                }

                catch (Exception ex)

                {

                    return false

                }

                return true;

            }

    Cheers

    <city w:st="on"><place w:st="on">Roy</place></city>

    Thursday, May 3, 2012 5:00 AM

Answers

All replies

  • So you first run delete on row and then try to insert that same row back with same primary key at the transaction? You need to commit the delete transaction before you can insert new row with the same key. The actual delete is not happened considering the consistency of the database until you have committed the transaction that performs the delete.
    Thursday, May 3, 2012 5:24 AM
  • Thanks,

    if the DELETE and INSERT run separately (not within the same transaction), the INSERT will succeed.

    However, I want to maintain data integrity, hence running both in one transaction

    (i.e. if the INSERT fails, I don't want to DELETE to execute..)
    Thursday, May 3, 2012 9:11 PM
  • On 5/3/2012 5:11 PM, win programmer wrote:
    > Thanks,
    >
    > if the DELETE and INSERT run separately (not within the same
    > transaction), the INSERT will succeed.
    >
    > However, I want to maintain data integrity, hence running both in one
    > transaction
    >
    > (i.e. if the INSERT fails, I don't want to DELETE to execute..)
     
    Well, you need to come up with some different logic, because this is not
    going to work for you.
     
    Thursday, May 3, 2012 9:16 PM
  • You can use some boolean flag to do it so:

    bool bContinue = false;
    //do insert:
    //...
    try
    {
       cmd.ExecuteNonQuery();
       bContinue = true;
    }
    catch (Execption ex)
    {
        //show exception...
    }
    
    //now, if insert was ok, then continue with delete query:
    if(bContinue)
    {
        //do delete...
    }


    Mitja

    Thursday, May 3, 2012 9:17 PM
  • Insert is not possible because of if you don't delete the previous row with primary key you are inserting then insert won't never succeed. You need to do some other logic to first delete row in one transaction, then data is in consistent state and then insert new row with the primary key you just deleted.

    Just for example you might create separate table where you copy rows you are about to delete. Perform copy and delete in transaction so if inserting to copy table fails or deleting from source table fails then you can rollback that transaction and no harm is done. If this goes ok, data is copied and deleted, you then known you have the original rows and you can perform insert of the new rows.

    • Marked as answer by Alexander Sun Thursday, May 10, 2012 5:13 AM
    Friday, May 4, 2012 4:47 AM
    • Marked as answer by Alexander Sun Thursday, May 10, 2012 5:13 AM
    Sunday, May 6, 2012 10:30 PM