Auto rollback when executing SQL queries from C#

Question

Hello,

I have a problem with some SQL queries that are wrapped inside a transaction. Here's how the code looks like:

using (SqlTransaction dbTrans = conn.BeginTransaction())

{

using (SqlCommand cmd = conn.CreateCommand())

{

  for(Parameters p in parameterList)
  try

  {
      //execute insert commmand
  }
  catch
  {
      //log exception
      //SQL server rolls back everything
      //even though no rollback statement is present!!!
  }

}

dbTrans.Commit();

}

I'm trying to execute some insert statements inside a transaction but if one fails, everything gets auto rollbacked. I know that in most situations this behavior is wanted by in my scenario it doesn't matter if a few statements don't make it. The reason for the transaction's existence is to improve speed. I know about bulk insert, but unfortunately I cannot use it here, so this is what I have to work with. Could you please tell me if it's possible to disable this behavior I described?

Answer 1

You could write the insert command in such a way that it never fails. For example:

insert into table1 (id, name) values (1,'charles')
where not exists (select * from table1 where id=1)

Answer 2

Basically you want to use transactions without them being all or nothing. This is simply not possible. My blog has a post on various ways of speeding up inserts. It might be useful to you.

Answer 3

This is the nature of a database transaction, which is to ensure atomicity. If an INSERT operation fails, the transaction must rollback.

Answer 4

I think "all or nothing" is the default behaviour of the transaction and you can not change it, and it is not correct to choose transaction for only speed.

You have to optimize your code use optimized classes and minimize number of database accesses.

Second thing why you can not use SqlBulkCopy class?? can you give more details

Answer 5

SQL does not rollback everything actually. It all depends on the error being raised. Some error do abort the current transaction, some don't. For instance a key violation error does not abort a transaction and you can safely continue execution. See Database Engine Error Severities for details on engine error severity.

One thing to look into is if your application is changing the default SET XACT_ABORT setting. When this setting is ON any error will cause the transaction to abort. Default is OFF.

Answer 6

Ok guys, thanks for all the useful input on this. I guess I'll have to leave things like this and just wait for Microsoft to add this functionality in the future, if they ever do.