4 Replies Latest reply: Feb 14, 2014 4:23 PM by rp0428 RSS

    Atomic transaction / Atomicity explanation for a layman and software vendor

    lucianob


      Can someone point me to a good blog post that explains the concept of Atomicity as described here: Atomicity (database systems) - Wikipedia, the free encyclopedia

      That article is a good start, but I was hoping for something that went into more details, and I couldn't find such an article/blog myself. I just found more short definitions, or very short responses in other forums. Was hoping for a nicely detailed (and somewhat lengthier) posting on this topic.

       

      Background:

      This isn't related to Oracle itself, but the software vendor in this case just happens to use Oracle. The issue is that the software contains several places where a single "user transaction" contains several table updates, and the software does a "commit" on each update instead of treating the whole transaction as a single unit. For example: An inventory system that creates a "reservation record" on a certain item in inventory. The software updates the inventory table's record to  have a quantity of "reserved", does a "commit", sends an email, then creates the record in the "reservation record table" and issues a commit. If the process craps out on sending the email... 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). I'd like something that I can show them that would do a good job of explaining this... And that a transaction like this should be treated as a whole, regardless of any error that occurred during the process.

        • 2. Re: Atomic transaction / Atomicity explanation for a layman and software vendor
          Mark D Powell

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

          • 3. Re: Atomic transaction / Atomicity explanation for a layman and software vendor
            jgarry

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

            • 4. Re: Atomic transaction / Atomicity explanation for a layman and software vendor
              rp0428

              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?