SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Fun with ACID


Fun with ACID

Author
Message
Brandie Tarvin
Brandie Tarvin
SSC-Dedicated
SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)

Group: General Forum Members
Points: 37047 Visits: 9268
paul.knibbs (1/17/2013)
Bit uncertain about the description of Isolation. Surely the whole point of record locking is that it DOES prevent the other transaction until the first one is finished? In your pizza example, there's somebody present who doesn't themselves eat any pizza, but whose job is to stop two people grabbing the same slice--and if the only way to stop that is to grab somebody's hand and stop them reaching for the slice until someone else has finished with it, well, that's just fine and dandy.


Depends on the lock, really. And the queries. Read up on Replication, Shared Locks, Exclusive Locks, etc.

Locking is an article in and of itself (possibly a series). I was just trying to cover the brief 50k foot view of the ACID concepts without going off on tangents.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/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.
Stuart Davies
Stuart Davies
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7351 Visits: 4817
Brandie Tarvin (1/17/2013)
paul.knibbs (1/17/2013)
Bit uncertain about the description of Isolation. Surely the whole point of record locking is that it DOES prevent the other transaction until the first one is finished? In your pizza example, there's somebody present who doesn't themselves eat any pizza, but whose job is to stop two people grabbing the same slice--and if the only way to stop that is to grab somebody's hand and stop them reaching for the slice until someone else has finished with it, well, that's just fine and dandy.


Depends on the lock, really. And the queries. Read up on Replication, Shared Locks, Exclusive Locks, etc.

Locking is an article in and of itself (possibly a series). I was just trying to cover the brief 50k foot view of the ACID concepts without going off on tangents.


You achieved your goal then Brandie (IMHO) - thanks for the article

-------------------------------Posting Data Etiquette - Jeff Moden Smart way to ask a questionThere are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan I would never join a club that would allow me as a member - Groucho Marx
Steve Thompson
Steve Thompson
Right there with Babe
Right there with Babe (798 reputation)Right there with Babe (798 reputation)Right there with Babe (798 reputation)Right there with Babe (798 reputation)Right there with Babe (798 reputation)Right there with Babe (798 reputation)Right there with Babe (798 reputation)Right there with Babe (798 reputation)

Group: General Forum Members
Points: 798 Visits: 2180
Congrats on the article, Brandie!!!

I'll skip the Tim Leary jokes and get right to my question:

In discussing Isolation, I thought the analogy of the pizza box was clever and appropriate. However then you go on to say:
if I'm updating customer information in my database, it should not prevent another process from reading customer information


That sounds like you could be promoting the idea of allowing dirty reads, which I never thought of as being a requirement of supporting the Isolation tenet (though I'm far from an expert); in fact, couldn't that be considered a violation of consistency? Or were you simply advocating for the most granular form of locking that would support consistency (e.g. I should be able to read other customer data, though not the data being updated)?

Oh and before I forget, for your next article can you work in a reference to Disco Stu (maybe something like, "Disco Stu likes disc defragmentation")?
Brandie Tarvin
Brandie Tarvin
SSC-Dedicated
SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)

Group: General Forum Members
Points: 37047 Visits: 9268
Steve Thompson-454462 (1/17/2013)
In discussing Isolation, I thought the analogy of the pizza box was clever and appropriate. However then you go on to say:
if I'm updating customer information in my database, it should not prevent another process from reading customer information


That sounds like you could be promoting the idea of allowing dirty reads, which I never thought of as being a requirement of supporting the Isolation tenet (though I'm far from an expert); in fact, couldn't that be considered a violation of consistency?


Actually, I'm not promoting dirty reads. What I'm promoting is the idea of access to previously committed data. Just because Mary Sue's last name is being updated to her new last name does not mean another process shouldn't be able to read the previous last name at the same time.

Now I grant there are all sorts of other concerns that go along with that, but for the purposes of the 50k foot view, that's basically what I meant.


Oh and before I forget, for your next article can you work in a reference to Disco Stu (maybe something like, "Disco Stu likes disc defragmentation")?


Heh. I'll see what I can do.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/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.
chuck.hamilton
chuck.hamilton
Old Hand
Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)

Group: General Forum Members
Points: 364 Visits: 402
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.
Brandie Tarvin
Brandie Tarvin
SSC-Dedicated
SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)

Group: General Forum Members
Points: 37047 Visits: 9268
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/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.
patspray
patspray
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 10
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
chuck.hamilton
chuck.hamilton
Old Hand
Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)

Group: General Forum Members
Points: 364 Visits: 402
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.
scott mcnitt
scott mcnitt
SSC Veteran
SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)

Group: General Forum Members
Points: 254 Visits: 437
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.
Michael Valentine Jones
Michael Valentine Jones
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14175 Visits: 11848
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!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search