Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Fun with ACID Expand / Collapse
Author
Message
Posted Thursday, January 17, 2013 6:20 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 8:54 AM
Points: 5,579, Visits: 6,363
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 Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1408386
Posted Thursday, January 17, 2013 6:36 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 8:36 AM
Points: 3,086, Visits: 3,322
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 question

There 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
Post #1408394
Posted Thursday, January 17, 2013 8:07 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 8:29 AM
Points: 266, Visits: 1,687
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")?
Post #1408438
Posted Thursday, January 17, 2013 8:13 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 8:54 AM
Points: 5,579, Visits: 6,363
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 Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1408445
Posted Thursday, January 17, 2013 8:20 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 12:20 PM
Points: 50, Visits: 243
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.
Post #1408452
Posted Thursday, January 17, 2013 8:25 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 8:54 AM
Points: 5,579, Visits: 6,363
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 Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1408458
Posted Thursday, January 17, 2013 8:44 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 12, 2013 9:29 AM
Points: 2, 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
Post #1408470
Posted Thursday, January 17, 2013 8:57 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 12:20 PM
Points: 50, Visits: 243
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.
Post #1408476
Posted Thursday, January 17, 2013 10:06 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 18, 2014 5:39 AM
Points: 41, Visits: 378
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.
Post #1408525
Posted Thursday, January 17, 2013 10:49 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 11:09 PM
Points: 3,108, Visits: 11,502
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!





Post #1408547
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse