Managing Multiuser Databases - Database Processing: Fundamentals, Design, and Implementation - page 406

Implicit and explicit COMMIT TRANSACTION
Some DBMS products allow and implement an implicit COMMIT TRANSACTION when-
ever a SQL DML statement is run. For example, suppose we run a transaction using the SQL
UPDATE command:

/* *** EXAMPLE CODE - DO NOT RUN *** */
/* *** SQL-UPDATE-CH09-01 *** */
UPDATE CUSTOMER

SET AreaCode = '425'

WHERE ZipCode = '98050';

SQL Server 2012 and MySQL 5.6 will, by default, automatically commit the changes to the
database after the transaction is complete. You do not have to use a COMMIT statement to
make the database changes permanent. This is an implicit COMMIT setting.
On the other hand, Oracle Database 11 g Release 2 does not provide a mechanism for im-
plicit COMMITs, and an explicit COMMIT statement must be run to make the changes to the
database permanent (Oracle Database uses COMMIT instead of COMMIT TRANSACTION).
Thus, we would have to run the SQL UPDATE as:

/* *** EXAMPLE CODE - DO NOT RUN *** */
/* *** SQL-UPDATE-CH09-02 *** */
UPDATE CUSTOMER

SET AreaCode = '425'

WHERE ZipCode = '98050';

COMMIT;

Note that this statement applies only to the Oracle Database DBMS itself. Some Oracle
Database utilities do implement the ability to automatically issue COMMIT statements, and
thus it can appear to the user that there is an implicit COMMIT. We will discuss this in detail
when we work with Oracle Database 11 g Release 2 in Chapter 10B.

Consistent Transactions
Sometimes, you will see the acronym ACID applied to transactions. An ACID transaction is
one that is a tomic, c onsistent, i solated, and d urable. Atomic and durable are easy to define.
As you just learned, an atomic transaction is one in which either all of the database actions
occur or none of them does. A durable transaction is one in which all committed changes are
permanent. Once a durable change is committed, the DBMS takes responsibility for ensuring
that the change will survive system failures.
The terms consistent and isolated are not as definitive as the terms atomic and durable .
Consider a transaction with just one SQL UPDATE statement:

/* *** EXAMPLE CODE - DO NOT RUN *** */
/* *** SQL-UPDATE-CH09-03 *** */
BEGIN TRANSACTION;
UPDATE CUSTOMER
SET AreaCode = '425'
WHERE ZIPCode = '98050';
COMMIT TRANSACTION;

Suppose that there are 500,000 rows in the CUSTOMER table and that 500 of them have
ZIPCode equal to '98050'. It will take some time for the DBMS to find those 500 rows. During that
time, other transactions may attempt to update the AreaCode or ZIPCode fields of CUSTOMER.