The Chocolate Malt Story formerly known as the Margarita Story1
One of the issues VB programmers often have to deal with is a change from Access and DAO to SQL Server and ADO. OLEDB and ADO do have a different architectural focus than DAO and I came up with what I call "The Chocolate Malt Story" to help explain that difference.
The Malt Shops of Microsoft Street
Let's say you're thirsty for a chocolate malt. You go down to the local ice cream shoppe area - we'll say it's called Microsoft Street.
First, you see an establishment called "Access Ice Cream Shoppe" and figure that sounds okay, so you go in. There's a long counter with a ice cream bartender and some tall stools. You sit at one of the stools and tell the ice cream bartender you'd like a chocolate malt. The ice cream bartender gives you a blender, a bottle of chocolate syrup, a container of ice cream, a bottle of malt mix, some milk, a glass, and some whipped cream. Since this is the Access Ice Cream Shoppe, you take all these ingredients which have been passed from the back of the counter over the counter to you, and proceed to mix them together with your hands. Your hands then lift the finished malt up to your mouth and your mouth is happy because it has a chocolate malt.
After a few of these malts made this way you're getting tired of having to mix them yourself. Besides having to focus on processing the malt, you're weary of waiting for the ice cream bartender to pass all those raw ingredients across the counter each time. You amble out of the Access Ice Cream Shoppe and resolve to seek further.
A few doors down on Microsoft Street you see a sign for "SQL Server Ice Cream Shoppe". It looks a little more expensive and there's someone at the door checking to be sure you're wearing shoes. Why not? you think, and go in.
The set up is roughly similar: the long counter, the ice cream bartender, the tall stools. But, there is a difference: when you sit down and order a chocolate malt, instead of the ice cream bartender handing you a blender, all those bottles and the ice cream, etc., you get handed a simple chocolate malt. Once again, your mouth is happy - it has a chocolate malt.
Wow! you think, this is cool! Much faster! I didn't have to spin all those cycles worrying about how to process a chocolate malt. And it didn't take as much time! I can drink a bunch more malts!
Back now to database land, where instead of the Access Ice Cream Shoppe we call that concept "Single Tier" and instead of the SQL Server Ice Cream Shoppe we call that one "Two Tier". With Access, even if all your tables are kept on a separate machine - say, a central file server - in order to process that data, it all (the blender, the bottles, ice cream, etc.) has to travel across the network to get to your client workstation where the query is run and the data formatted for display and/or printing (this is the mouth, incidentally).
With SQL Server, the query is run at the database server, which has its own data engine, and then just the slimmed down result set (the chocolate malt) is returned to the client workstation for display and/or printing. Your monitor and/or printer does not care whether the processing occurred on a server or on the client, BTW - there is no difference to the final output - just like your mouth doesn't care if your hands or the ice cream bartender's hands processed the chocolate malt.
(But, wait! you cry! what about DAO's DBEngine???! That's an engine, right??? Well, yes. But it runs only on the client.
It's closing time at the SQL Server Ice Cream Shoppe and as you leave you notice that a bit further down the street is a different ice cream shoppe - this one is called "Enterprise SQL Server Ice Cream Shoppe". We'll go check that one out next...
The Enterprise SQL Server Ice Cream Shoppe
It was a dark and stormy night; a darkness relieved only by the screaming neon of the "Enterprise SQL Server Ice Cream Shoppe", the sole establishment open on Microsoft Street in the wee hours of the morning. You've been driven to this pass by your unrelenting desire, no, need for the perfect chocolate malt...
After making it through the shoe checking portal, you are whisked away to a cozy room, filled with tables and chairs. "No counter??" you ponder warily as you take a seat, "I'm used to dealing directly with the ice cream bartender. Hmmmmm." Just then, as though by magic, a ice cream server approaches your table and asks what you'd like to malt. "Can't I just go up to the counter and get my malt?" you ask. "No, at this ice cream shoppe all your malt requests have to go through me. I pass them along to the ice cream bartender and then return with the result," replies the ice cream server, a little annoyed that you wanted to deal directly with the ice cream bartender.
You order a plain chocolate malt, blended, not stirred, and as the ice cream server disappears behind a heavy door you sit back to wait and see what happens (trying to ignore that pulsey-sounding noise and the word "energize"...)
One the other side of the door, the ice cream server presents your order to the ice cream bartender for one plain chocolate malt, blended, not stirred. The ice cream bartender goes behind the counter, mix, mix, whirr, whirr, pour, and hands the finished chocolate malt to the ice cream server.
You're surprised at how quickly the ice cream server returns with your malt, but perhaps it's not quite quick enough. "I've got it!", you exclaim to yourself as you sip your icy concoction, "I'll order a bunch of malts and that way I'll have them here and ready." You flag down the ice cream server and say, "I'd like 10 chocolate malts please, only this time use special expensive chocolate syrup."
"I'm sorry, but at this ice cream shoppe we have rules and you can only order one chocolate malt at a time."
"But a friend of mine was here last month - just after you opened - and said she got three at once."
"We used to allow more than one malt to be ordered at a time but then the counter kept sending back panicked messages about not being designed to take it and not knowing how far they could push it."
"Oh, okay, then. If that's the way it has to be. One will be fine." You sit back again and wait.
But not for long! The ice cream server had conferred with some managerial looking employee on this side of the heavy door. "Apparently the ice cream shoppe does not allow customers who haven't paid a cover fee to order chocolate malts made with special expensive chocolate syrup so your request for the chocolate malt made with special expensive chocolate syrup cannot be processed," the ice cream server reports without bringing a malt. Slightly bummed, you agree to take another plain chocolate malt.
(Your mouth, BTW, is still quite happy, despite the lack of the special expensive chocolate syrup, and has not noticed that a ice cream server, nor the other managerial-looking employee has entered the equation.)
It's now last call. You and everybody else at the ice cream shoppe have placed their malt orders. Many ice cream servers, who all strangely look similar as though they were pressed from the same cookie cutter, disappear through the magic door to the counter carrying messages about many malts.
You were a bit alarmed watching all those ice cream servers descend on that door at once. In your experience only one ice cream bartender functioned at a time. "It's going to take forever to get my malt," you mutter testily.
Back at the counter, the ice cream servers line up to request malts. Fortunately, there are many ice cream bartenders - a cluster, one might say - there who step up to the counter to get the order request and then step back to make the malt. Each ice cream server, laden with malts, goes back to the seating area and distributes the malts. It really didn't take that much extra time. Many mouths were made happy.
So, back to Database land again, this is called "3-tier" or "n-tier" (remember the managerial-looking employee? - that could be considered another tier). The important thing you want to get out of this is that you never directly deal with the data (the ice cream bartender). If the data (ice cream bartender) is only focusing on processing (making malts), it can churn out a lot more result sets. If the data has to worry about making sure only one request comes in at a time and that it's properly formatted (customer asks for more than one malt at a time or customer asks for a malt to be served on a plate instead of a glass), that's processing cycles that cannot be used for just data.
Middle-tier components - the ice cream server and the managerial-looking employee - are the go-betweens of the client and data, enforcing business rules and delivering either data results or error results from the data server to the client.
I'm sure at this point you're wondering how mistakes aren't made like your chocolate malt getting someone else's strawberry banana shake mix in it. Next I'll talk about how something called acid prevents that from happening (as opposed to anti-acid, which is what you might need if you drink too many malts...)
A.C.I.D.
On your way home from Microsoft Street it really starts to perplex you how all those last call malts were processed without any mistakes.
There was the couple at the next table who was told that since the ice cream shoppe was out of mint syrup that they couldn't have mint chocolate chip shakes. It would have been so easy for the ice cream bartender to have left out the ingredient and left no one the wiser, but the ice cream server came back and said, sorry, but either the malt as a whole can be made or it's not made at all. (The couple opted for mocha malts instead.)
Then there was the group of noisy conventioneers at the table in the center. You heard them exclaiming at how marvelous it was that their strawberry banana shakes tasted exactly the same each time - the consistency was amazing.
Yourself, you wondered the whole night how it was that such a busy ice cream shoppe never made a mistake and got, say, the conventioneers' strawberry banana shake mix into your chocolate malts. It was as though each malt was made inside some protected bubble where unwelcome ingredients had no chance to interact.
And, lastly, the table in the corner - the one whose ice cream server got sick and had to turn his orders over to another server - they got their malts just as they ordered them - pretty cool considering the change over.
Atomicity, Consistency, Isolation and Durability. These make up the ACID property of data transactions. If all these conditions are met, the changes may commit. If any of these conditions are not met, the transaction cannot occur and any changes are rolled back.
A classic example of atomicity is software installation (a data transaction, really - it places data on your hard drive): if the installation is aborted because of, say, low disk space, the installer doesn't copy just the files that will fit, it refuses to copy anything and quits the installation. It's an all or nothing proposition. If you want a database example, if one of the fields cannot be updated, then the record as a whole will fail to update. If the mint syrup is missing, no mint chocolate chip shake.
Consistency relates to the integrity of the data and is often discussed in terms of field constraints such as "no duplicates". Some of you have first hand experience seeing how well databases like to have duplicate values in primary key fields. You can't change the ingredient measurements on a strawberry banana shake and call this modified recipe "strawberry banana shake", as well - there can be only one strawberry banana shake and it's made to exacting specifications.
Isolation is what keeps coworkers' data entry from being saved into the record you're working on. Each data transaction is done in isolation from all other data transactions. One ice cream bartender's chocolate malt transaction cannot affect another ice cream bartender's strawberry banana shake transaction.
A transaction is durable if it can survive a crisis situation like a power failure - or the ice cream server getting sick and having to turn orders over to a substitute. If the record has been updated, those changes should persist even if something "weird" happens.
This is a simplification (obviously) of enterprise data workings. The Distributed class (and test...) goes a lot more into transactions and Microsoft Transaction Server (MTS). I believe I recommended the Ted Pattison book Programming Distributed Applications with COM and Microsoft Visual Basic 6.0 (Microsoft Press, ISBN 1-57231-961-5) in another post. He explains this stuff far better than I do! :-)
1Those of you all who have heard/read this mini-lecture before will remember it as the Margarita Story. In order to keep my ICRA rating I had to rewrite this a bit. (Actually, it's a little bit funnier now, and you can safely show it to your grandmother :-)
Alrak's Course Resources ©2002-2022 Karla Carter. All rights reserved. This material (including, but not limited to, Mini-lectures and Challenge Labs) may not be reproduced, displayed, modified or distributed without the express prior written permission of the copyright holder. For permission, contact Karla.