SQLServerCentral / Discuss Content Posted by Brandie Tarvin / Article Discussions / Article Discussions by Author / Fun with ACID / Latest Posts

RE: Fun with ACID

Brandie Tarvin — Mon, 21 Jan 2013 08:04:09 GMT

[quote][b]asok14215 (1/17/2013)[/b][hr]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".[/quote]Apparently my brain was elsewhere when I typed that. I'll see if Steve can fix the typo.

RE: Fun with ACID

JohnB_SQL — Fri, 18 Jan 2013 07:35:53 GMT

Always nice to refresh the fundamentals

RE: Fun with ACID

sjimmo — Fri, 18 Jan 2013 06:05:01 GMT

Great refresher. I have saved it for future re-reading periodical, if even to remember Atomicity. (I to forget this word.:hehe:

RE: Fun with ACID

Scott D. Jacobson — Fri, 18 Jan 2013 02:01:49 GMT

[quote]Yes, ACID is brilliant and 99.999% of the time functions perfectly, but just because it's unsinkable doesn't mean it can't sink (fail) that other .001% of the time.[/quote][url=http://xkcd.com/1161/]Obligatory.[/url]

RE: Fun with ACID

asok14215 — Thu, 17 Jan 2013 12:57:16 GMT

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.

RE: Fun with ACID

Michael Valentine Jones — Thu, 17 Jan 2013 10:49:19 GMT

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!

RE: Fun with ACID

scott mcnitt — Thu, 17 Jan 2013 10:06:31 GMT

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.

RE: Fun with ACID

chuck.hamilton — Thu, 17 Jan 2013 08:57:00 GMT

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.

RE: Fun with ACID

patspray — Thu, 17 Jan 2013 08:44:57 GMT

[quote][b]Brandie Tarvin (1/17/2013)[/b][hr][quote][b]chuck.hamilton (1/17/2013)[/b][hr] 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.[/quote] Hence it actually is subscribing to the very first rule of ACID (Atomicity).[/quote]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:

RE: Fun with ACID

Brandie Tarvin — Thu, 17 Jan 2013 08:25:38 GMT

[quote][b]chuck.hamilton (1/17/2013)[/b][hr]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.[/quote]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).

RE: Fun with ACID

chuck.hamilton — Thu, 17 Jan 2013 08:20:09 GMT

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.

RE: Fun with ACID

Brandie Tarvin — Thu, 17 Jan 2013 08:13:39 GMT

[quote][b]Steve Thompson-454462 (1/17/2013)[/b][hr]In discussing Isolation, I thought the analogy of the pizza box was clever and appropriate. However then you go on to say:[quote]if I'm updating customer information in my database, it should not prevent another process from reading customer information[/quote]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?[/quote]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.[quote]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")?[/quote]Heh. I'll see what I can do.

RE: Fun with ACID

Steve Thompson-454462 — Thu, 17 Jan 2013 08:07:47 GMT

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:[quote]if I'm updating customer information in my database, it should not prevent another process from reading customer information[/quote]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 [i]other[/i] 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")?

RE: Fun with ACID

Stuart Davies — Thu, 17 Jan 2013 06:36:56 GMT

[quote][b]Brandie Tarvin (1/17/2013)[/b][hr][quote][b]paul.knibbs (1/17/2013)[/b][hr]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.[/quote]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.[/quote]You achieved your goal then Brandie (IMHO) - thanks for the article

RE: Fun with ACID

Brandie Tarvin — Thu, 17 Jan 2013 06:20:54 GMT

[quote][b]paul.knibbs (1/17/2013)[/b][hr]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.[/quote]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.

RE: Fun with ACID

Brandie Tarvin — Thu, 17 Jan 2013 06:16:09 GMT

[quote][b]patspray (1/17/2013)[/b][hr]Customized record locking is achievable in MS SQL Server CE - for an in-depth understanding go to Micro Soft's [url=http://msdn.microsoft.com/en-us/library/ms172975(v=sql.90).aspx][u]knowledge base[/u][/url] and read the section (and subsections) on Locking.....~Paka Hell_On_Wheelz (twitter)[/quote]Thanks for the info! Nice to see you here too. @=)

RE: Fun with ACID

paul.knibbs — Thu, 17 Jan 2013 06:13:42 GMT

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.

RE: Fun with ACID

patspray — Thu, 17 Jan 2013 05:39:44 GMT

Customized record locking is achievable in MS SQL Server CE - for an in-depth understanding go to Micro Soft's [url=http://msdn.microsoft.com/en-us/library/ms172975(v=sql.90).aspx][u]knowledge base[/u][/url] and read the section (and subsections) on Locking.Locking, for the most part, in CE is automatic but some control is possible:Handling deadlocks.Setting the lock time-out duration. Setting the [u][url=http://msdn.microsoft.com/en-US/library/ms171885(v=sql.90).aspx]transaction isolation level[/url][/u]. Using table-level locking hints with the SELECT, INSERT, UPDATE, and DELETE statements. As a DBA [u]and[/u] program developer, I tend to control some aspects of ACID on the program side (particularly atomicity and isolation).Just some random thoughts....Breakfast? Pizza?? mmmMMMMMmmmm :-D~Paka Hell_On_Wheelz (twitter)

RE: Fun with ACID

johnzarifis — Thu, 17 Jan 2013 05:17:56 GMT

[quote](I always forget the word "Atomicity". Who even coined that mouthful?) [/quote]Greek word Ατομικότητα...

RE: Fun with ACID

Brandie Tarvin — Thu, 17 Jan 2013 04:04:14 GMT

[quote][b]arun1_m1 (1/17/2013)[/b][hr]The ACID property is interesting if we hook it to the conventional Db management system. If I talk about Mobile Database systems, in SQL Server CE for a SELECT operation there is no SHARED lock obtained, which means any record that I read is UNCOMMITTED. In this case how does one have ISOLATION property maintained?[/quote]ACID is a fundamental concept for all databases, even mobile ones. How Microsoft managed to program the CE engine to maintain Isolation is not a question I can answer (because I don't know much about mobile DBs). But to have a database that doesn't abide ACID is a violation of the most basic principals of database design. So unless MS seriously screwed something up, even CE has Isolation.That being said, I am going over to Twitter to see if I can get a Microsoft designer to help us answer that question.

RE: Fun with ACID

jchilton — Thu, 17 Jan 2013 03:37:03 GMT

Article's a bit too "wordy" for me. And the spelling of consistency wasn't consistant! ;-) Atomicity states that database modifications must follow an “all or nothing” rule. Each transaction is said to be “atomic.” If one part of the transaction fails, the entire transaction fails. It is critical that the database management system maintain the atomic nature of transactions in spite of any DBMS, operating system or hardware failure. •Consistency states that only valid data will be written to the database. If, for some reason, a transaction is executed that violates the database’s consistency rules, the entire transaction will be rolled back and the database will be restored to a state consistent with those rules. On the other hand, if a transaction successfully executes, it will take the database from one state that is consistent with the rules to another state that is also consistent with the rules. •Isolation requires that multiple transactions occurring at the same time not impact each other’s execution. For example, if Joe issues a transaction against a database at the same time that Mary issues a different transaction, both transactions should operate on the database in an isolated manner. The database should either perform Joe’s entire transaction before executing Mary’s or vice-versa. This prevents Joe’s transaction from reading intermediate data produced as a side effect of part of Mary’s transaction that will not eventually be committed to the database. Note that the isolation property does not ensure which transaction will execute first, merely that they will not interfere with each other. •Durability ensures that any transaction committed to the database will not be lost. Durability is ensured through the use of database backups and transaction logs that facilitate the restoration of committed transactions in spite of any subsequent software or hardware failures.

RE: Fun with ACID

arun1_m1 — Thu, 17 Jan 2013 02:59:22 GMT

The ACID property is interesting if we hook it to the conventional Db management system. If I talk about Mobile Database systems, in SQL Server CE for a SELECT operation there is no SHARED lock obtained, which means any record that I read is UNCOMMITTED. In this case how does one have ISOLATION property maintained?

RE: Fun with ACID

Jonathan Mallia — Thu, 17 Jan 2013 02:11:47 GMT

Great refresher ;)Thanks

RE: Fun with ACID

SQLRNNR — Thu, 17 Jan 2013 00:06:36 GMT

I read this article expecting Rainbows and Unicorns. Instead I got Homer and Pizza. What a warped sense of humor you have. :-DSeriously though - Well Done Brandie.

Fun with ACID

Brandie Tarvin — Thu, 17 Jan 2013 00:01:36 GMT

Comments posted to this topic are about the item [B]Fun with ACID[/B]