January 17, 2013 at 8:25 am
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).
January 17, 2013 at 8:44 am
Brandie Tarvin (1/17/2013)
chuck.hamilton (1/17/2013)
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.
Hence it actually is subscribing to the very first rule of ACID (Atomicity).
Unfortunately, SQL does this SILENTLY - which means anything the input system has set up to deal with a failed transaction is circumvented. The system must rely on user reports for "disappeared" transactions for which there are now no records. In systems where ANY transaction (even failed ones) are considered important (and are preserved as records in the database) - having silent rollbacks with NO alerts will drive IT more than a little :crazy:
January 17, 2013 at 8:57 am
We have implemented triggers on the user tables & msmerge_tombstone table to differentiate between user deletes and system deletes and in the case of the latter save the data before SQL deletes it. IMO DBAs and/or developers should not have to jump through these hoops. There should be a better way to handle this condition. Something that can be set up automatically when you create the publication.
January 17, 2013 at 10:06 am
I love that such a basic refresher can generate such good discussion.
I would like to see the same thing happen in a related area. The article could perhaps be called "Fun with Unit of Work and Concurrency.”
Sadly, it seems that very few BA’s and even experienced database developers consider this topic when designing systems.
We could even spell the word “concurrency” in different ways to bring out the spelling trolls.
January 17, 2013 at 10:49 am
I was expecting a different kind of fun with ACID.
When I was in the 8th grade we noticed one day after school that all the teachers were in a meeting so there was no one around to stop us.
We went into the science lab and mixed up a concoction using 3 different concentrated acids. We knew it was nasty why we saw a purple mist rising from it.
To finish it off, we dropped a sizable chunk of sodium metal into the mixture. It immediately started burning with a violent, brilliant blue flame. After a few seconds the white hot chunk of sodium popped out of the beaker, landed on the table, and melted a hole about an inch in diameter completely through the table top.
At that point, we called a halt to the experiment, destroyed the evidence as best we could, and cleared out.
Now that was fun with ACID!
January 17, 2013 at 12:57 pm
Nice.
Sorry for being pedantic, but I think "D" always stands for "Durability"...which you do use, but you also use "Duration", which I've never heard used for "D".
Ah, I wish we could actually depend on Durability as a black box in real life.
January 18, 2013 at 2:01 am
January 18, 2013 at 6:05 am
Great refresher. I have saved it for future re-reading periodical, if even to remember Atomicity. (I to forget this word.:hehe:
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
January 18, 2013 at 7:35 am
Always nice to refresh the fundamentals
January 21, 2013 at 8:04 am
asok14215 (1/17/2013)
Sorry for being pedantic, but I think "D" always stands for "Durability"...which you do use, but you also use "Duration", which I've never heard used for "D".
Apparently my brain was elsewhere when I typed that. I'll see if Steve can fix the typo.
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply