transaction control language in sql

Transaction Control Language (TCL)-: TCL is the part of structure query language.It managed transaction in database.Understanding TCL we Divided TCL in some like

tcl in sql
transaction control language

A. Database Transaction:-The server ensures data consistency based on transactions. Transactions give we more flexibility and control when changing data, and they ensure data consistency in the event of user process failure or system failure. Transactions consist of DML statements that make up one consistent change to the data. For example, a transfer of funds between two accounts should include the debit to one account and the credit to another account in the same amount. Both actions should either fail or succeed together. The credit should not be committed without the debit.

B.—-Transaction Control:-
1. The server implements consistency to guarantee that the data seen by a statement or transaction does not change until that statement or transaction completes.
2. The server always uses statement level consistency which ensures that the data visible to a statement does not change during the life of that statement.
3. Transaction can consists of multiple statements.
4. When used, transaction level consistency will ensure that the data visible to all statements in a transaction does not change for the life of transaction.

C.—-Transaction Type:-
Type                                                    Description
Data Manipulation Language          Consists of any number of DML                                                                              statements that server treats as a sing                                                                      1 entity or a logical unit of work
Data Definition Language                Consists of only one DDL statement
Data Control Language                     Consists of only one DCL statement

A transaction begins when the first executable SQL statement is executed.
A transaction terminates when one of the following occurs.
1. A COMMIT or ROLLBACK statement is issued.
2. A DDL statement, such as CREATE, is issued.
3. A DCL statement is issued.
4. The user exits SQL *Plus.
5. A machine fails or the system crashes.
After one transaction ends, the next executable SQL statement automatically starts the next transaction. A DDL statement or a DCL statement is automatically committed and therefore implicitly ends a transaction.
D.—Controlling Transactions-: Controlling transaction are two type

  1. Explicit transaction control statements
  2. Implicit transaction process

Explicit Transaction Control Statements:- We can control the logic of transactions by using the COMMIT, SAVE POINT, and ROLLBACK statements.
Statements Description
COMMIT Ends the current transaction by making all pending data changes
SAVE POINT name Marks a save point within the current transaction.
ROLLBACK [ TO SAVE POINT name]ROLLBACK ends the current
transaction by discarding all pending data changes; ROLLBACK TO
SAVE POINT rolls back the current transaction to the specified
save point, thereby discarding the save point and any subsequent
changes. If we omit this clause, the ROLLBACK statement rolls back
the entire transaction.
Save points are not schema objects and cannot be referenced in the data dictionary.

Implicit Transaction Processing:- An automatic commit occurs under the following circumstances:
1. DDL statement is issued.
2. DCL statement is issued.
3. Normal exit from SAL *Plus, without explicitly issuing COMMIT or ROLLBACK.

An automatic roll back occurs under the following circumstances :
1. An abnormal termination of SQL*Plus.
2. A system failure.
A third command is available in SQL*Plus. The AUTO COMMIT command can be toggles ON or OFF. If set to ON, each individual DML statement is committed as soon as it is executed. We cannot roll back the changes. If set to OFF, COMMIT can be issued or when we exit from SQL*Plus.
When a transaction is interrupted by a system failure, the entire transaction is automatically rolled back. This prevents the error from causing unwanted changes to the data and returns the tables to their state at the time of the last commit. In this way, the server protects the integrity of the tables.

E.–Committing Changes:- Every data change made during the transaction is temporary until the transaction is committed state of the data, before COMMIT or ROLLBACK is issued:
1. Data manipulation operations primarily affect the database buffer, therefore, the previous state of the data can be recovered.
2. The current user can review the results of the data manipulation operations by querying the tables.
3. Other users cannot view the results of the data manipulation operations made by the current user. The server institutes read consistency to ensure that each user sees data as it existed at the last commit.
4. The affected rows are locked; other users cannot change the data in the affected rows.
Data changes may actually by written to the database files before COMMIT, but they are still only temporary. If a number of users are making changes simultaneously to the same table, then each user sees only his or her changes until other users commit their changes. Other users see data as it is committed in the database (in other words, before changes). By default, the server has row level locking. It is possible to alter the default locking mechanism. Make all pending changes permanent by using the COMMIT statement. State of the data after a COMMIT is issued:
1.Data changes are written to the database.
2. The previous state of the data is permanently lost.
3. All users can view the results of the transaction.
4. The locks on the affected rows are released; the rows are now available for other users to perform new data changes.
5. All save points are erased.

UPDATE emp SET deptno = 10 WHERE empnoi = 7782;
1 row updated
Commit Complete
The example updates the EMP table and sets the department number for employee 7782(clark) to 10. It then markes the change permanent by issuing the COMMIT statement.

F—Rolling Back changes:- Discard all pending changes by using the ROLLBACK statement. Following a ROLLBACK:
1. Data changes are undone.
2. The previous state of the data is restored.
3. The locks on the affected rows are released.

DELETE FROM employee;
14 rows deleted
Rollback complete
While attempting to remove a record from the EMPLOYEE table, we can accidentally empty the table. We can correct the mistake and reissue the proper statement.

G–Rolling Back Changes to a Save point:-We can create a marker in the current transaction by using the SAVE POINT statement. The transaction therefore can be divided into smaller sections. We can then discard pending changes up to that marker by using the ROLLBACK TO SAVE POINT statement. If we create a second save point with the same name as an earlier save point, the earlier save point is deleted.
Statement-Level Rollback
Part of a transaction can be discarded by an implicit rollback, if a statement execution error is detected, If a single DML statement fails during execution of a transaction, its effect is undone by a statements in the transaction will not be discarded. They can be committed or rolled back explicitly by the user.

H–Read Consistency:-
Database users make two types of access to the database:
1. Read operations (SELECT statement).
2. Write operations (INSERT, UPDATE, DELETE statements)’

We need read consistency so that the following occur:

1. The database reader and writer are ensured a consistent view of the data.
2. Readers do not view data that is in the process of being changed.
3. Writers are ensured that the changes to the database are done in a consistent way.
4. Changes made by one writer do not disrupt or conflict with changes another writer is making.
the purpose of read consistency is to ensure that each user sees data as it existed at the last commit, before a DML operation started. Read consistency is an automatic implementation. It keeps a partial copy of the database in rollback segments. When an insert, update, or delete operation is made to the database, segment. All readers, except the one who issued the change, still see the database as it existed before the changes started; they view the rollback segments’ “snapshot” of the data.
Before changes are committed to the database, only the user who is modifying the data sees the database with the alterations; everyone else sees the snapshot in the rollback segment. This guarantees that readers of the data read consistent data that is not currently undergoing change.


Leave a Comment