lucianob, Atomicity actually has a fairly short definition but atomicity is just one part of the ACID criteria. ACID is atomicity, consistency, isolation, and durability. All these together are required to create a valid unit of work, that is, a properly designed transaction. You can find more information to supplement Brian's excellent reference in the following Oracle support document E1: DB: Transaction, Transaction Processing, Commit and Rollback (1208208.1)
HTH -- Mark D Powell --
Tom Kyte does a very good job explaining this in his books (and also authored the above doc link, I believe).
The concept you are explaining is called "unit of work." Unfortunately, the example you gave has serialization issues for a multiuser system using default oracle locking.
So for example, let's say you have two order entry people dealing with customers on the phone. Your inventory reservation system has to handle this scenario:
You have 500 widgets in inventory.
Person 1 enters 100 lines of an order, including 450 widgets.
Person 2 enters 100 lines of another order, including 150 widgets.
Person 1 commits.
Person 2 commits. Order fails because there weren't enough widgets to reserve.
So what do you do? Tell customer 2 tough after being on the phone 15 minutes? The business process would likely be defined as order lines are transactions, not the order. Allow negative inventory? Some places do... some have configurations for whether each customer can have back orders... Then there's "pseudo-transactions..."
OK, now you say "well, my example was there were two transactions having to do with reservations that are logically a single unit of work." OK, well, lets change my example slightly. Now we have 200 people entering orders for 3 each of those widgets simultaneously, and you have to serialize the update of those two tables. Now you have locking issues (and perhaps deadlock if your coders aren't so good)...
So you are perfectly correct in explaining unit of work, but be careful of business rules, since they have to scale too. It gets worse if the app is "database blind..."
Ahhh - a vendor relations problem! Love those problems where the hardware folk blame the software and software blames the hardware and they both blame the 3rd party supplier.
Can someone point me to a good blog post that explains the concept of Atomicity
You seem to already understand that concept.
In an atomic transaction, a series of database operations either all occur, or nothing occurs.
What seems to be in dispute between you and the vendor, as jgarry so artfully explained, is not whether the process now being used is 'atomic' but whether the process used SHOULD BE atomic.
the item in the inventory table will be stuck in "reserved qty", but there will be no "reservation record" against it, and thus no way to "free it" from being "stuck" in "reserved quantity".
The software vendor blames this on the fact that the email failed to get sent (for whatever reason) and doesn't seem to understand that the "correct" thing to do would be to treat the entire transaction as a whole (it either fully succeeds or totally fails).
The software vendor is CORRECT - the failure to send the email IS to blame.
But what you haven't posted is whether or not the vendor agrees that it is a problem for the inventory table to be 'stuck' like that. If they don't agree that it is a problem then you have a different issue altogether.
Once the vendor agrees that a 'stuck' table IS a problem then you need to convince the vendor that it is THEIR problem and not yours. That has NOTHING to do with ACID, atomicity or anything else.
And, if it is the vendors problem then, IMHO, it is NONE of your business just how the vendor solves the problem as long as the 'solution' works properly and meets the contract requirements you have with the vendor.
Maybe the vendor wants you to help them find solutions and maybe they don't but that is THEIR issue if the problem is theirs.
Maybe they just need/want to add a 'Unstick the inventory table' button that you can push to clean everything up.
In other words the bigger issue is how the process should work. In 'atomicity' terms (see quote above) the vendor needs to properly define that 'series of database operations' that should 'either all occur, or nothing occurs'.
If they want to do those individual steps and commits why do you care as long as you get the correct results?