Re: SqlTransaction.ZombieCheck error

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

"Colbert Zhou [MSFT]" <colbertz@xxxxxxxxxxxxxxxxxxxx> wrote in message news:JFCiE5voJHA.1856@xxxxxxxxxxxxxxxxxxxxxxxxx
Hello Andrew,

I discussed this issue with some senior SQL engineers internally. Their
feedbacks tend towards the current behavior. The reason is that Transaction
is just designed as atom action. And when the high severity error occurs
and it is not recoverable, they think it makes more sense that the rollback
happens automatically. If we do not design as this way, what would it be
when the programmer's transaction.Rollback() fails for unexpected reason?

The main issue I have is the inconsistency to the developer. SQL Server automatically rolls back the transaction for insert issues caused by Datetime conversion errors. However, it does not roll back automatically for insert issues caused because a data element is too long.

For some reason the SQL Server team decided that the datetime conversion issue is of higher severity. I disagree with that assesment. In both cases the end result to the application developer is the same. The record fails to load. From the application developer standpoint the severity is the same, so it makes sense to treat them the same.

The other issue I have with the decision to automatically roll back is the lack of choice. I understand the need for atomicity. Howerver, only the app developer knows what operations actually need to be atomic. In the case of my application each insert is it's own atomic operation. I use transactions purely for the performance gain. Simply wrapping batches of inserts together in a transactions has shortened processing time of my app nearly in half.

I should point out before someone else does that I can't simply re-architect my app to use features like Bulk Copy or SSIS. My application needs to be able to work with as many different databases as possible.

I've worked around the issue. However, I'd still appreciate it if you relayed to the SQL Server Team my comments about the severity and the fact that Atomicity isn't the only reason to use transactions.

Andrew Faust

.

Relevant Pages

  • RE: Backups have Shadow Copy Problems
    ... Active backup destination: File ... Reason: The process cannot access the file because it is being used by ... Warning: Unable to open "C:\Documents and Settings\Administrator\Local ... Warning: Unable to open "C:\Program Files\Microsoft SQL ...
    (microsoft.public.windows.server.sbs)
  • RE: Backups have Shadow Copy Problems
    ... recovery model of the SQL Server database to Simple. ... Warning: Unable to open "C:\Program Files\Microsoft SQL ... Reason: The process cannot access the file because it is being used by ... Backup completed on 2/14/2006 at 3:39 PM. ...
    (microsoft.public.windows.server.sbs)
  • [Full-Disclosure] RE: SQL Slammer doing the rounds again?
    ... I was assuming that this was the only reason the poor netadmin ... the web designers and their choices; I can't speak to the issues ... Security Business Unit ... SQL Slammer doing the rounds again? ...
    (Full-Disclosure)
  • Re: Backup erros
    ... You may have a SQL ... Error 800423f4 appears in the backup log file when you back up a volume by ... > Reason: The process cannot access the file because it is being used by> another process. ...
    (microsoft.public.windows.server.sbs)
  • Re: NexusDb
    ... > At no time did I query your evaluation of the SQL statements. ... > That really only leaves the question of Transactions IMO. ... specific SQL commands that support starting, ...
    (borland.public.delphi.thirdpartytools.general)