When in transaction scope how do I run a query outside transaction?

[Allan Ebdrup]

I'm using dotNet 2.0 and System.Transact ions to run transactions that span
multiple database queries, Now I would like to log any Sql errors that occur
in the transaction, but when I insert the logentry into the database the
query that does the inserting is automatically enlisted in the running
transaction and rolled back when the transaction aborts.
How do I run the query inserting into the log outside the current
transaction, so that it is not rolled back with the current transaction?
I'm guessing I create a new transaction scope that doesn't enlist in the
current transaction, is this correct? And if so, how do I do it?

Kind Regards,
Allan Ebdrup

[Marc Gravell]

does this work?

using(Transacti onScope ts = new
TransactionScop e(TransactionSc opeOption.Suppr ess)) {
// your work
ts.Complete();
}

Marc

[Nicholas Paldino [.NET/C# MVP]]

While supporess will work, it's probably not the best idea in this
situation, especially if the logging requires more than one operation and
the whole thing has to be atomic.

It's probably better to create a new transaction scope with RequiresNew
instead.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard. caspershouse.co m

"Marc Gravell" <ma**********@g mail.comwrote in message
news:Or******** ******@TK2MSFTN GP05.phx.gbl...

does this work?

using(Transacti onScope ts = new
TransactionScop e(TransactionSc opeOption.Suppr ess)) {
// your work
ts.Complete();
}

Marc

[Marc Gravell]

I considered that, but guessed (albeit without any evidence) that
typical logging would be doing a very simple single insert (hence no
huge atomicity requirement) without querying any data first (so no
isolation-leevl range-lock subtleties) nor lock escalation, so *for
this specific scenario* suppress might be OK.

Of course, another approach is to use a queue for logging, cleared
down on a (not too irregular) basis, so that a: the main transactional
code isn't impeded by logging IO, and b: when loggin *does* happen it
can happen in a batch (or even SqlBulkCopy depending on throughput)
rather than individual statements.

Marc

[Nicholas Paldino [.NET/C# MVP]]

Marc,

Your assumption falls short, because there might be more than one
operation being performed that is not a read.

For example, you might have a general audit/log table with the time the
audit took place, etc, etc, and then specific detail tables related to the
general table.

What it comes down to is that unless it is a single operation with a
single resource, you most definitely want a new transaction. Even with file
systems that are becoming transactional (NTFS in Vista), it's more important
than ever to take this into account.

I agree that a queue operation might be a good choice here, but even
then, depending on the type of queue, you still have to take transactions
into account.

--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard. caspershouse.co m

"Marc Gravell" <ma**********@g mail.comwrote in message
news:11******** **************@ n59g2000hsh.goo glegroups.com.. .

>I considered that, but guessed (albeit without any evidence) that
typical logging would be doing a very simple single insert (hence no
huge atomicity requirement) without querying any data first (so no
isolation-leevl range-lock subtleties) nor lock escalation, so *for
this specific scenario* suppress might be OK.

Of course, another approach is to use a queue for logging, cleared
down on a (not too irregular) basis, so that a: the main transactional
code isn't impeded by logging IO, and b: when loggin *does* happen it
can happen in a batch (or even SqlBulkCopy depending on throughput)
rather than individual statements.

Marc

[Allan Ebdrup]

I will be dooing several operations on the database in the logging, so I
quess I should use RequiresNew.
Thanks for all the feedback.

Kind Regards,
Allan Ebdrup