Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Fun with ACID


Fun with ACID

Author
Message
Brandie Tarvin
Brandie Tarvin
SSCrazy Eights
SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)

Group: General Forum Members
Points: 8939 Visits: 8813
Comments posted to this topic are about the item Fun with ACID

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.
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22786 Visits: 18261
I read this article expecting Rainbows and Unicorns. Instead I got Homer and Pizza. What a warped sense of humor you have. :-D

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

Jonathan Mallia
Jonathan Mallia
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1083 Visits: 1288
Great refresher Wink

Thanks
arun1_m1
arun1_m1
SSC Eights!
SSC Eights! (927 reputation)SSC Eights! (927 reputation)SSC Eights! (927 reputation)SSC Eights! (927 reputation)SSC Eights! (927 reputation)SSC Eights! (927 reputation)SSC Eights! (927 reputation)SSC Eights! (927 reputation)

Group: General Forum Members
Points: 927 Visits: 612
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?
jchilton
jchilton
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 18
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.
Brandie Tarvin
Brandie Tarvin
SSCrazy Eights
SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)

Group: General Forum Members
Points: 8939 Visits: 8813
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.

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.
johnzarifis
johnzarifis
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 40
(I always forget the word "Atomicity". Who even coined that mouthful?)


Greek word Ατομικότητα...
patspray
patspray
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 10
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 :-D

~P
aka Hell_On_Wheelz (twitter)
paul.knibbs
paul.knibbs
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2108 Visits: 6223
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.
Brandie Tarvin
Brandie Tarvin
SSCrazy Eights
SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)

Group: General Forum Members
Points: 8939 Visits: 8813
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. @=)

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.
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