Foundational data architecture patterns - Making Sense of NoSQL - page 46

 Begin/End transaction —Commands to begin and end a batch of transactions
(inserts, updates, or deletes) that either succeed or fail as a group
 Rollback —An operation that returns a database to some previous state
In our SALES_ORDER example, there are two tables that should be updated together.
When new items are added to an order, a new record is inserted into the
ORDER_ITEMS table (which contains the detail about each item) and the total in the
SALES_ORDER table is updated to reflect the new amount owed.
In RDBMS s it's easy to make sure these two operations either both complete suc-
cessfully or they don't occur at all by using the database transaction control statements
shown in figure 3.6.
The first statement, BEGIN TRANSACTION , marks the beginning of the series of
operations to perform. Following the BEGIN TRANSACTION , you'd then call the code
that inserts the new order into the ORDER_ITEMS table followed by the code that
updates the total in the SALES_ORDER table. The last statement, COMMIT TRANSAC-
TION , signals to the system that your transaction is finished and no further processing
is required. The database will prevent (block) any other operations from occurring on
either table while this transaction is in process so that reports that access these tables
will reflect the correct values.
If for some reason the database fails in the middle of a transaction, the system will
automatically roll back all parts of the transaction and return the database to the sta-
tus it was prior to the BEGIN_TRANSACTION . The transaction failure can be reported
to the application, which can attempt a retry operation or request the user to try
again later.
The functions that guarantee transaction reliability can be performed by any appli-
cation. The key is that RDBMS implementations make some parts of this automatic
and easy for the software developer. Without these functions, application developers
must create an undo process for each part of the transactions, which may require a
great deal of effort.
Some NoSQL systems don't support transactions across multiple records. Some
support transaction control but only within atomic units of work such as within a
BEGIN TRANSACTION;
-- code to insert new item into the order here...
-- code to update the order total with new amount here...
COMMIT TRANSACTION;
GO
Figure 3.6 This code shows how the BEGIN TRANSACTION and
COMMIT TRANSACTION lines are added to SQL to ensure that both the
new items are added to a sales order and the total of the sales order is
updated as an atomic transaction. The effect is that the transactions
are done together or not at all. The benefit is that the SQL developer
doesn't have to test to make sure that both changes occurred and then
undo one of the transactions if the other one fails. The database will
always be in a consistent state.