Transactions in Database
A transaction is a group of events that occur between any of the following events:
· Connecting to database
· Disconnecting to database
· Committing changes to the database table
“A series of one or more SQL statements that are logically related or a series of operations performed on table data is termed as a Transaction”.
A transaction begins with the first executable SQL statement after a commit, rollback or connection made to the database engine. All changes made to a table data via a transaction are made or undone at one instance.
How to close the transaction
A transaction can be closed by using either a commit or a rollback statement. By using these statements, table data can be changed or all the changes made to the table data undone.
A COMMIT ends the current transaction and makes permanent any changes made during the transaction. All transactional locks acquired on tables are released.
A ROLLBACK does exactly the opposite of commit. It ends the transaction but undoes any changes made during the transaction. All transactional locks acquired on tables are released.
ROLLBACK [WORK][TO [SAVEPOINT]<SavepointName>]
WORK Is optional and is provided for ANSI compatibility
SAVEPOINT Is optional and is used to rollback a transaction partially (it marks and saves the current point in the processing of a transaction).
SavepontName Is a savepoint created during the current transaction
Rollback must be done with or without the savepoint.