January 17, 2013 at 12:01 am
Comments posted to this topic are about the item Fun with ACID
January 17, 2013 at 12:06 am
I read this article expecting Rainbows and Unicorns. Instead I got Homer and Pizza. What a warped sense of humor you have. 😀
Seriously though - Well Done Brandie.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 17, 2013 at 2:11 am
Great refresher 😉
Thanks
January 17, 2013 at 2:59 am
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?
January 17, 2013 at 3:37 am
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.
January 17, 2013 at 4:04 am
arun1_m1 (1/17/2013)
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?
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.
January 17, 2013 at 5:17 am
(I always forget the word "Atomicity". Who even coined that mouthful?)
Greek word ?t?µ???t?ta...
January 17, 2013 at 5:39 am
Customized record locking is achievable in MS SQL Server CE - for an in-depth understanding go to Micro Soft's knowledge base 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 transaction isolation level.
Using table-level locking hints with the SELECT, INSERT, UPDATE, and DELETE statements.
As a DBA and 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 😀
~P
aka Hell_On_Wheelz (twitter)
January 17, 2013 at 6:13 am
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.
January 17, 2013 at 6:16 am
patspray (1/17/2013)
Customized record locking is achievable in MS SQL Server CE - for an in-depth understanding go to Micro Soft's knowledge base and read the section (and subsections) on Locking.....
~P
aka Hell_On_Wheelz (twitter)
Thanks for the info! Nice to see you here too. @=)
January 17, 2013 at 6:20 am
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.
January 17, 2013 at 6:36 am
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 [/url]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
January 17, 2013 at 8:07 am
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")?
January 17, 2013 at 8:13 am
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.
January 17, 2013 at 8:20 am
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.
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply