Java Reference
In-Depth Information
to work with a transaction, turn Autocommit off; then issue the commands required by
the transaction. Assuming that everything executes correctly, the transaction will be
committed when the COMMIT command executes, as illustrated above. If any
problems arise during the transaction, the entire transaction is cancelled by the
ROLLBACK command.
Cross-Reference
Transaction management and the ACID test are discussed in
Chapter 1
. The examples in
Chapter 6
illustrate the use of the
COMMIT and ROLLBACK commands.
Using UPDATE on Indexed Tables
When a table is indexed for rapid data retrieval, and particularly when a clustered
index is used for this purpose, updates can be very slow unless you understand and
use the indexes correctly. The reason for this is that the purpose of an index is to
provide rapid and efficient access to a table. In most situations, speed of data
retrieval is considered to be of paramount performance, so tables are indexed to
enhance the efficiency of data retrieval.
A limiting factor in retrieving data rapidly and efficiently is the performance of the
physical storage medium. Performance can be optimized for a specific index by tying
the layout of the rows on the physical storage medium to that index. The index for
which the row layout is optimized is commonly known as the clustered index.
If you fail to take advantage of indexes, and in particular, of the clustered index, when
planning your update strategy, your updates may be very slow. Conversely, if your
updates are slow, you would be well advised to add an index specifically to handle
updates, or to modify your update strategy in light of the existing indexes.
The DELETE Statement
The last DML command is the DELETE command, which is used for deleting entire
records or groups of records. Again, when using the DELETE command, you use a
WHERE clause to identify the records to be deleted.
Using the DELETE command is very straightforward. For example, this is the
command you use to delete records containing the First_Name: "Michael" and the
Last_Name: "Corleone":
DELETE FROM Customers
WHERE First_Name = 'Michael' AND Last_Name = 'Corleone';
Without the WHERE clause, all rows throughout the entire table will be deleted. If you
are using a complicated WHERE clause, it is a good idea to test it in a SELECT
statement before using it in a DELETE command.
