Database administrators would be more overworked if it weren't for the ONE principal that universally governs all database activities (regardless of software brand): ACID. I don't usually worry about having the acronym memorized (most people don't). But since ACID is key to everything that databases are and will be, understanding the base concepts is essential. If I didn't understand ACID, I'd quite likely be out of a job since troubleshooting SQL problems would be ... well, almost impossible without that knowledge.
ACID is all about transactions and has 4 components: Atomicity, Consistancy, Isolation, and Duration. (I always forget the word "Atomicity". Who even coined that mouthful?)
Atomicity, the first concept, defines a transaction. The question asked is "What steps are required to complete this task?" Most SQL Server books use the bank example. Say I want to pay my phone bill through my bank's online billpay service. I use the software to schedule the payment, the bank takes money out of my checking account, packages the money into an electronic "thing," sends the money to the phone company, then marks the money as sent in the banks own records.
If the process fails during or after step 2 (the bank taking the money), I stand to lose my payment, still owe the bill, and even end up with overdraft fees on my account because the bank keeps taking money out of my account. Since the bank can't bleed money, they need to ensure that if any part of that process fails, everything "rolls back" to its original status (the money gets put back into my account and the bill goes unpaid). Every step of this process, even though they are unique steps, are wrapped up in an imaginary bundle of "All or Nothing" process called a transaction.
Consistency, the second concept, means that the transaction does not violate integrity. All records must be in a consistent state. This is a little harder to grasp than Atomicity. Again, I revert to a common example, orphaned records. The database may have a customer table with customer names in it and a separate address table that houses addresses with a customer ID column. For consistency, no address table record should exist for a non-existant customer. If I delete customer ID 456 in the customer table, all address records using customer 456 should likewise be deleted. A money column should not contain the value "ABC" and a date column should not contain 150. A transaction should always adhere to any data rules (foreign keys, constraints, default values, nullability declarations, etc.).
And, FYI, it's not ACID's fault if the DBA doesn't establish those rules. If I have an orphaned record in my database, it's not because ACID failed. It's because I failed to do my job properly.
Isolation, the third concept, refers to the ability to manipulate multiple transactions in a state separate from each other. If I have 5 friends reaching for the pizza box, the pizza box should not constrain them from grabbing their slices at the same time, nor should any of them jostle the others away from the box and prevent anyone else from getting a slice.
(Okay, maybe that's a bad example. I know some jokers who would do exactly that. @=)
The point is, if I'm updating customer information in my database, it should not prevent another process from reading customer information for the website or even removing customer information - even the stuff I'm currently accessing! (Yes, that really does happen). Nor should any deletes prevent my ability to update the record being deleted. (And that too really happens, but that concept is an entirely different topic.)
Durability, the last of the concepts, is probably the most important one to my mind. What happens in Vegas, stays in Vegas, remember? Well, what happens in the database (and gets committed) - no matter what else is going on - should stay in the database regardless of server failures, filled up hard drives, and other various system problems. Of course, Durability doesn't mean a damn thing if I'm not backing up my databases, but backups - or lack thereof - are another topic.
The key to Durability is that the data needs to be committed. If it is uncommitted data, chances are failure will cause it to roll back. And if it doesn't, there are other, bigger issues at hand. For one thing, having an uncommitted transaction laying around violates the first concept of ACID. For another, it's just Not Supposed To Happen, or so I've been told.
I take the Titanic view of ACID. 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. My thought is that ACID is both hardware- and software-dependent. If the software / database engine coding gets screwed up, then there is the possibility of failure. Until I can prove that this can happen, though, these thoughts are just my silly little imaginings. In public I will toe the ACID NEVER FAILS line.
To reiterate my initial point, we don't need to memorize the concept terms (Atomicity, Consistency, Isolation, and Duration) unless we're going to a job interview. But we all need to recognize the term ACID when it comes up in conversation and be able to discuss it intelligently. ACID is so central to every single other database concept that not knowing it and not understanding it is just begging to be pink slipped. Or laughed at for making pizza references in a tech blog post. Hmmm. Pizza.
'Scuse me. Homer and I have some lunch to track down.
