Home Forums Article Discussions Article Discussions by Author Discuss Content Posted by Brandie Tarvin Fun with ACID RE: Fun with ACID

  • January 17, 2013 at 8:25 am

    #1578345

    chuck.hamilton (1/17/2013)

    One of my pet peeves with SQL Server is that it allows the silent violation of of the (d)urability principle.

    Consider SQL replication.

    An INSERT gets committed on the publisher. A full disk on the subscriber database causes the INSERT to fail on the subscription. SQL Server now decides that synchronization between the subscriber and publisher is more important than durability of a committed transaction and quietly issues a system delete for that row back on the publisher. In the end, a committed insert simply disappears.

    I've seen this happen with merge replication, and I believe it's also a problem with transactional replication. IMO this is a big mistake.

    I understand the frustration, but what happens is that SQL Server is treating the whole Replication publication as part of one over reaching transaction. Because the transaction fails on the subscriber end, it is rolled back from the publisher end. Hence it actually is subscribing to the very first rule of ACID (Atomicity).

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.