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 12:01 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:56 AM
Points: 7,073, Visits: 6,231
Comments posted to this topic are about the item Fun with ACID

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 #1408170
Posted Thursday, January 17, 2013 12:06 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 5:28 PM
Points: 20,483, Visits: 14,136
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1408173
Posted Thursday, January 17, 2013 2:11 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Sunday, April 20, 2014 2:22 AM
Points: 974, Visits: 1,066
Great refresher ;)

Thanks
Post #1408227
Posted Thursday, January 17, 2013 2:59 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Sunday, April 20, 2014 3:42 PM
Points: 854, Visits: 513
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?
Post #1408253
Posted Thursday, January 17, 2013 3:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 31, 2013 3:42 AM
Points: 1, 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.
Post #1408287
Posted Thursday, January 17, 2013 4:04 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:56 AM
Points: 7,073, Visits: 6,231
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 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 #1408312
Posted Thursday, January 17, 2013 5:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 3:39 AM
Points: 1, Visits: 33
(I always forget the word "Atomicity". Who even coined that mouthful?)


Greek word Ατομικότητα...
Post #1408354
Posted Thursday, January 17, 2013 5:39 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
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)
Post #1408363
Posted Thursday, January 17, 2013 6:13 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 9:07 AM
Points: 1,529, Visits: 5,187
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.
Post #1408378
Posted Thursday, January 17, 2013 6:16 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:56 AM
Points: 7,073, Visits: 6,231
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 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 #1408382
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse