TRANSACTION COMMAND

COMMIT Command :

Used to commit all changes made since the beginning of the transaction. It does the

following.

• Makes all the changes made to database during transaction permanent

• Unlocks the rows that are locked during the transaction.

• Ends the transaction.

• Erases all savepoints in the current transaction(covered later).

Changes made to database such as inserting rows, deleting rows, and updating rows are not made permanent until they are committed.

Implicit Commit :Oracle Implicitly issues commit in the following cases.

• Before a DDL command.
• After a DDL command.
• At normal disconnect from the database. For example, when you exit SQL*PLUS using EXIT command.

Note: It is very important to remember not to mix DML commands with DDL command as the later commands are going to issue COMMIT, which might commit incomplete changes also.

ROLLBACK Command :

Used in transaction processing to undo changes made since the beginning of the transaction.

• Undoes the changes made to database in the current transaction
• Releases locks held on rows duration transaction.
• Ends the transaction.
• Erases all savepoints in the current transaction (covered later).

ROLLBACK SEGMENT:

In order to rollback changes that were made, Oracle has to store the data that was existing prior to the change so that previous data can be restored when user rolls back the changes.ROLLBACK SEGMENT is the area where Oracle stores data that will be used to roll back the changes. Every transaction is associated with a ROLLBACK SEGMENT.Generally a few ROLLBACK SEGMENTS are created at the time of creating database. Database Administrator can create some more rollback segments depending upon the requirement usingCREATE ROLLBACK SEGMENT command. Oracle assigns one rollback segment to each transaction. It is also possible to associate a transaction with a particular rollback segmentusing SET ROLLBACK SEGMENT command. It is not relevant to discuss more about the way ROLLBACK SEGMENT functions in a book like this. Please see Oracle Concepts for more information about ROLLBACK SEGEMENT.

SAVEPOINT Command : 

Savepoint is used to mark a location in the transaction so that we can rollback up to that mark and not to the very beginning of the transaction.

The following is the syntax of SAVEPOINT.
SAVEPOINT savepoint_name;

A single transaction may also have multiple savepoints. The following example illustrates

how to use save pointsSQL> update . . .SQL> savepoint s1;Savepoint created.SQL> insert .... ;SQL> insert .... ;SQL> savepoint s2;Savepoint created.SQL> delete ...;SQL> rollback to s1;Rollback complete.SQL> update…SQL> commit;

In the above example, ROLLBACK TO S1; will undo changes made from savepoint S1 tothe point of rollback. That means it will undo INSERT, INSERT, and even DELETE given aftersecond savepoint. It doesn’t undo UPDATE given before first savepoint. The COMMIT givenafter last UPDATE is going to commit first UDPATE and last UPDATE. Because all the remainingare already rolled back by ROLLBACK TO S1 command.If we have given ROLLBACK TO S2; then it would have undone only DELETE given after thesecond savepoint and remaining statements (update, insert, insert, and update) would have been committed.

Locking:It is important from database management system’s point of view to ensure that two user arenot modifying the same data at the time in a destructive manner.Let us consider the following example to understand what will happen if two users are trying to update the same data at the same time.Assume we have PRODUCTS table with details of products. Each product is having quantity on hand (QOH). Transactions such as sales and purchases are going to modify QOH column of the table.The following are the steps that might take place when two transactions – one sale and one purchase – are taking place.

1. Assume QOH of product 10 is 20 units.
2. At 10:00 USER1 has sold 5 units and updated QOH as follows but has not committed the
change. After UPDATE command QOH will be 15.
update products set qoh = qoh – 5 where prodid = 10;
3. At 10:11 USER2 has purchased 10 units and updated QOH as follows and committed. After
UPDATE command QOH will be 25 as 10 it added to 15.
update products set qoh = qoh + 10 where prodid = 10;
4. If at 10:12 USER1 has rolled back the UPDATE then data that was there before the
UPDATE should be restored i.e.20. But the actual data should be 30 as we added 10 units to it at 10:11 and committed.

As you can see in the above example, if two users are trying to update the same row at the same time the data may be corrupted. As shown in the above example at the end of the process QOH should be actually 30 but it is not only 20.
It is possible to ensure that two transactions are interfering with each other by locking the rows that are being modified so that only one transaction at a time can make the change.
Oracle ensures that only one transaction can modify a row at a time by locking the row once the row is updated. The lock will remain until the transaction is completed.
Oracle also ensures that other users will not see the changes that are not committed. That means if transaction T1 has updated a row then until the transaction is committed no other transaction in the system will see the changes made by T1. Instead other transactions will see
only the data that was existing before the change was made.

The following scenario will illustrate the process in detail.
1. Assume QOH of product 10 is 20.
2. Transaction T1 has issued UPDATE command to update QOH of product 10. Oracle locks the row that is updated and does not allow other transactions to update the row. However,
it is possible to read the data from the row.
update products set qoh = qoh + 5 where prodid = 10;

3. If T1 has issued SELECT to retrieve data then it will get 25 in QOH of product 10.
4. If T2 has issued SELECT command, it will see only 20 in QOH of product 10. This is because no uncommitted changes will be available to other transactions.
5. If T2 is trying to update product 10 then Oracle will cause transaction T2 to wait until transaction T1 (that holds lock on this row) is completed. Oracle will wail for lock to be released indefinitely.
6. If transaction T1 is committed then change is made permanent and lock will be released.Now it is possible for other transactions to see the updated data and even update the row if required.Now it is possible for other transactions to see the updated data and even update the row.

The following are the important points to be remembered about Oracle’s locking
mechanism.

• Locking in Oracle is automatic and transparent. That means we never need to ask Oracle to lock row that is being modified. Locking is transparent means user is not aware of the locking process. It happens automatically and in the background.
• Oracle locks the row that is being updated. That means locking is row-level. Other levels that are in user are - page-level and table-level.
• Oracle releases locks held by a transaction when transaction is completed either
• successfully – using COMMIT – or unsuccessfully – using ROLLBACK.
• If a transaction is trying to update a row and if row is already locked then Oracle will wait for the row that is locked to be unlocked indefinitely. It is because of the fact that rows are locked for a small duration in a typical production system. So Oracle prefers to wait to cause any error.
• It is possible to lock table manually using LOCK TABLE command.

Locking the rows that are being updated is an important part of Oracle. It ensures that no two transactions can update the same row at the same time. Locking mechanism is followed by all database management systems. But some smaller database management systems follow page-level locking where not the exact row that is being modified is locked instead the entire page in which the row exists is locked.