ACID:
• Atomicity: Either all of a transaction happens or none of it happens.
• Consistency: A transaction takes the database from one consistent state to the next.
• Isolation: The effects of a transaction may not be visible to other transactions until the
transaction has committed.
• Durability: Once the transaction is committed, it is permanent.
• COMMIT: To use this statement’s simplest form, you would just issue COMMIT. You could
bemore verbose and say COMMIT WORK, but the two are equivalent. A COMMIT ends your
transaction and makes any changes permanent (durable). There are extensions to the
COMMIT statement used in distributed transactions. These extensions allow you to label a
COMMIT (label a transaction) with some meaningful comment and force the commit of
an in-doubt distributed transaction.
• ROLLBACK: To use this statement’s simplest form, you would just issue ROLLBACK. Again,
you could be more verbose and say ROLLBACK WORK, but the two are equivalent. A rollback
ends your transaction and undoes any uncommitted changes you have outstanding. It
does this by reading information stored in the rollback/undo segments (going forward
I’ll refer to these exclusively as undo segments, the favored terminology for Oracle 10g)
and restoring the database blocks to the state they were in prior to your transaction
beginning.
• SAVEPOINT: A SAVEPOINT allows you to create a “marked point” within a transaction.
Youmay have multiple SAVEPOINTs within a single transaction.
• ROLLBACK TO <SAVEPOINT>: This statement is used with the SAVEPOINT command. You
may roll back your transaction to that marked point without rolling back any of the
work that preceded it. So, you could issue two UPDATE statements, followed by a
SAVEPOINT and then two DELETE statements. If an error or some sort of exceptional
condition occurs during execution of the DELETE statements, and you catch that excep-
tion and issue the ROLLBACK TO SAVEPOINT command, the transaction will roll back to
the named SAVEPOINT, undoing any work performed by the DELETEs, but leaving the
work performed by the UPDATE statements intact.
• SET TRANSACTION: This statement allows you to set various transaction attributes, such
as the transaction’s isolation level and whether it is read-only or read-write. You can
also use this statement to instruct the transaction to use a specific undo segment when
using manual undo management, but this is not recommended.
• Atomicity: Either all of a transaction happens or none of it happens.
• Consistency: A transaction takes the database from one consistent state to the next.
• Isolation: The effects of a transaction may not be visible to other transactions until the
transaction has committed.
• Durability: Once the transaction is committed, it is permanent.
• COMMIT: To use this statement’s simplest form, you would just issue COMMIT. You could
bemore verbose and say COMMIT WORK, but the two are equivalent. A COMMIT ends your
transaction and makes any changes permanent (durable). There are extensions to the
COMMIT statement used in distributed transactions. These extensions allow you to label a
COMMIT (label a transaction) with some meaningful comment and force the commit of
an in-doubt distributed transaction.
• ROLLBACK: To use this statement’s simplest form, you would just issue ROLLBACK. Again,
you could be more verbose and say ROLLBACK WORK, but the two are equivalent. A rollback
ends your transaction and undoes any uncommitted changes you have outstanding. It
does this by reading information stored in the rollback/undo segments (going forward
I’ll refer to these exclusively as undo segments, the favored terminology for Oracle 10g)
and restoring the database blocks to the state they were in prior to your transaction
beginning.
• SAVEPOINT: A SAVEPOINT allows you to create a “marked point” within a transaction.
Youmay have multiple SAVEPOINTs within a single transaction.
• ROLLBACK TO <SAVEPOINT>: This statement is used with the SAVEPOINT command. You
may roll back your transaction to that marked point without rolling back any of the
work that preceded it. So, you could issue two UPDATE statements, followed by a
SAVEPOINT and then two DELETE statements. If an error or some sort of exceptional
condition occurs during execution of the DELETE statements, and you catch that excep-
tion and issue the ROLLBACK TO SAVEPOINT command, the transaction will roll back to
the named SAVEPOINT, undoing any work performed by the DELETEs, but leaving the
work performed by the UPDATE statements intact.
• SET TRANSACTION: This statement allows you to set various transaction attributes, such
as the transaction’s isolation level and whether it is read-only or read-write. You can
also use this statement to instruct the transaction to use a specific undo segment when
using manual undo management, but this is not recommended.