Page 217 - Open Soource Technologies 304.indd

Open Source Technologies

Notes In MySQL, transactions begin with the statement BEGIN WORK and end with either a COMMIT
or a ROLLBACK statement. The SQL commands between the beginning and ending statements
form the bulk of the transaction.

13.1.2 COMMIT and ROLLBACK

When a successful transaction is completed, the COMMIT command should be issued so that
the changes to all involved tables will take effect. If a failure occurs, a ROLLBACK command
should be issued to return every table referenced in the transaction to its previous state.

In MySQL as well as NuSphere’s Enhanced MySQL, you can set the value
of a session variable called AUTOCOMMIT. If AUTOCOMMIT is set to
1 (the default), then each SQL statement (within a transaction or not) is
considered a complete transaction, committed by default when it finishes.
When AUTOCOMMIT is set to 0, by issuing the SET AUTOCOMMIT=0
command, the subsequent series of statements acts like a transaction, and no
activities are committed until an explicit COMMIT statement is issued.

If transactions were not used in application development, a large amount of programming time
would be spent on intricate error checking. For example, suppose your application handles
customer order information, with tables holding general order information as well as line items for
that order. To insert an order into the system, the process would be something like the following:
1. Insert a master record into the master order table.
2. Retrieve the ID from the master order record you just entered.

3. Insert records into the line items table for each item ordered.

If you are not in a transactional environment, you will be left with some straggly data floating
around your tables; if the addition of the record into the master order table succeeds, but steps 2
or 3 fail, you are left with a master order without any line items. The responsibility then falls on
you to use programming logic and check that all relevant records in multiple tables have been
added or go back and delete all the records that have been added and offer error messages to the
user. This is extremely time-consuming, both in man-hours as well as in program-execution time.

In a transactional environment, you’d never get to the point of childless rows, as a transaction
either fails completely or is completely successful.
13.1.3 Row-Level Locking

Transactional table types support row-level locking, which differs from the table-level locking
that is enforced in MyISAM and other nontransactional table types. With tables that support
row-level locking, only the row touched by an INSERT, UPDATE, or DELETE statement is
inaccessible until a COMMIT is issued.

Rows affected by a SELECT query will have shared locks, unless otherwise specified by the
programmer. A shared lock allows for multiple concurrent SELECT queries of the data. However,
if you hold an exclusive lock on a row, you are the only one who can read or modify that row
until the lock is released. Locks are released when transactions end through a COMMIT or
ROLLBACK statement.

212 LOVELY PROFESSIONAL UNIVERSITY