Database Reference
In-Depth Information
/* *** EXAMPLE CODE - DO NOT RUN *** */
BEGIN TRANSACTION;
SELECT
FROM
WHERE
PRODUCT.Name, PRODUCT.Quantity
PRODUCT
PRODUCT.Name = Pencil ;
Set NewQuantity = PRODUCT.Quantity - 5;
{process transaction - take exception action if NewQuantity < 0, etc.}
PRODUCT
PRODUCT.Quantity = NewQuantity
PRODUCT.Name = Pencil ;
UPDATE
SET
WHERE
{continue processing transaction} . . .
IF {transaction has completed normally} THEN
COMMIT TRANSACTION;
ELSE
ROLLBACK TRANSACTION;
Figure 9-10
Marking transaction
Boundaries
END IF;
Continue processing other actions not part of this transaction . . .
The SQL BEGIN TRANSACTION statement explicitly marks the start of a new trans-
action, while the SQL COMMIT TRANSACTION statement makes any database changes
made by the transaction permanent and marks the end of the transaction. If there is a need
to undo the changes made during the transaction due to an error in the process, the SQL
ROLLBACK TRANSACTION statement is used to undo all transaction changes and return
the database to the state it was in before the transaction was attempted. Thus, the SQL
ROLLBACK TRANSACTION statement also marks the end of the transaction, but with a
very different outcome.
These boundaries are the essential information that the DBMS needs to enforce the dif-
ferent locking strategies. If the developer now declares via a system parameter that he or she
wants optimistic locking, the DBMS will implicitly set locks for that locking style. If, however,
the developer declares pessimistic locking, the DBMS will set the locks differently.
By The WAy
As usual, each DBMS product implements these SQL statements in a slightly
different way. SQL Server does not require the SQL keyword TRANSACTION,
allows the abbreviation TRANS, and also allows the use of the SQL WORK keyword with
COMMIT and ROLLBACK. Oracle Database uses SET TRANSACTION with COMMIT and
ROLLBACK. MySQL does not use the SQL keyword TRANSACTION, while it allows (but
does not require) use of the SQL WORK keyword in its place.
Also note that the SQL BEGIN TRANSACTION statement is
not
the same as the
SQL BEGIN statement used in SQL/PSM control-of-flow statements (as discussed
in Chapters 7, 10A, 10B, and 10C). Thus, you may have to use a different syntax
for marking transactions within a trigger or stored procedure. For example, MySQL
marks the beginning of transactions in a BEGIN . . . END block with the SQL START
TRANSACTION statement. As usual, be sure to consult the documentation for the
DBMS product you are using.
Search WWH ::
Custom Search