Database Reference
In-Depth Information
previous transaction completes. A transaction is completed when a COM-
MIT or ROLLBACK command is executed, permanently storing changes
or undoing them, respectively. All DDL commands execute a COMMIT
command inherently. A disconnection from a session also executes an auto-
mated COMMIT command. This is an example transaction.
BEGIN
INSERT INTO Artist(artist_id, name)
VALUES(100,'A new artist');
UPDATE Artist SET name='Delete this row'
WHERE artist_id=100;
DELETE FROM Artist WHERE artist_id=100;
COMMIT;
END;
/
The COMMIT, ROLLBACK, SAVEPOINT, and SET TRANSAC-
TION commands are used to control transactions. COMMIT stores
changes, ROLLBACK removes changes, and SAVEPOINT allows partial
rollback of some of the commands in a transaction. The SET TRANSAC-
TION command allows specific settings to be global for an entire transac-
tion. Transactional control is discussed more in Chapter 15.
DML means Data Manipulation Language and DDL means Data Defi-
nition Language. The only important thing to remember about DML and
DDL commands with respect to transactional control is that DDL com-
mands are always automatically committed (cannot be undone). This
means that DDL commands automatically end the current transaction, and
nothing from that transaction may be undone once the transaction has
been completed. DML commands can be committed or rolled back.
Comparing COMMIT and ROLLBACK commands is significant
because committing is always faster than rolling back. Why? Well, it should
be because one would hope that most applications execute far more com-
mits than rollbacks. In short, COMMIT is built to be faster than ROLL-
BACK because it is a much more likely event.
What happens when a change is made to data in a table?
Redo log entries are written, storing log entries for data that is
about to be changed.
