Transaction Control Language (TCL)

Database transactions:

In a database, transaction means successful execution of a SQL statements with valid changes on the database. A database transaction consists of one of the following :

• • DML statements that constitute one consistent change to the data.
• • One DDL statement.
• • One DCL statement.

Transactions give you more flexibility and control when changing data, and they ensure data consistency in the event of user process failure or system failure.

Start and end of a transaction:

A transaction begins when a first DML statement is executed and ends when one of the following occurs :

• • A COMMIT or ROLLBACK statement is issued.
• • A DDL statement (such as create, update ) is issued.
• • A DCL statement is issued.
• • Machine fails or the system crashes.
• • User exists from the iSQL * plus .
• • When one transaction ends , the next executable statements start the next transaction.
  A DDL or a DCL statement is automatically committed and therefore implicitly ends a transaction.

Explicit transaction control statement:

Advantages of explicit transaction control statement:

• • Ensures data consistency.
• • Can see the data changes before making the changes permanent.
• • Group related operations.

You can control the logic of transaction by using the transaction control statement.

The statements are :

• • COMMIT : ends the current transaction by making all the pending data changes permanent.
• • SAVEPOINT : marks the position as save point within the current transaction.
• • ROLLBACK : ends the current transaction by discarding all pending data changes.
• • ROLLBACK TO : Rolls back the current transaction to the specified SAVEPOINT NAME save point, there by discarding all pending data changes after that point.
  Only ROLL BACK will roll back to the beginning of the transaction

Example:

Update emp
    Set emp_name = 'chandini'
    Where emp_id =50 ;
    1 row updated.
    SAVEPOINT upd ;
    Savepoint created.
    Insert into emp
    1 row inserted.
    ROLLBACK to upd ;
    Rollback completed.

In the above example emp table is updated and a save point is marked after that then a record is inserted to the emp table, up till here all the changes have been done but they have not been made permanent. At the end the rollback statement is issued which ends the transaction at the place where it has been marked and discards all the changes after that.

Best practices :

• • Do not use select * this is time consuming and reduces performance. Instead, list out each field that is required.
• • It is potentially dangerous to use select * in the embedded SQL i.e. SQL embedded in an application program because the meaning of the asterisk(*) might change. Example. If a column is added to or dropped from some table.
• • While evaluating NULL in a where clause of a query, use is NULL as opposed to = NULL.
• • If one is sure that the UNION operation cannot produce duplicate rows, use the UNION ALL as opposed to UNION because the query will execute much more quickly.
• • If the group by clause has been used in a select statement, then use only the grouping columns(columns on which grouping has been done) or aggregate functions in the column list of the select statement.
• • Rows that have a null value in the relevant column are ignored by all the aggregate function except count(*).
• • Index is most appropriate when queries against a table are more frequent than insert and update operations.
• • Exists is beneficial when the most selective filter is in the parent query. This allows the selective predicates in the parent query to be applied before filtering the rows against the exists criteria.
• • IN is most beneficial when the most selective filter appears in the sub-query and there are indexes on the join columns.

Tips to write a good query :

Tip 1 :

Select account_no, trans_date, amount
    From transaction
    Where amount + 3000 < 5000 ;
    Replace the above query with the following
    Select account_no, trans_date, amount
    From transaction
    Where amount < 2000;

Reason : Avoid unnecessary computational overhead in queries.

Tip 2 :

Select quantity, avg(actual_price)
    From item
    Group by quantity
    Having quantity > 40 ;
    Replace the above query with the following :
    Select quantity, avg(actual_price)
    From item
    Where quantity > 40
    Group by quantity ;

Reason :- The where clause filters the rows from the table according to the search condition. Then the group by clause is applied only on the filtered rows. It saves time. If as opposed to this, if the rows are grouped first then the row groups are filtered using the having clause, it leads to an increased overhead in terms of time required for execution of the query.

Tip-3 :

Problem statement : to retrieve the average salary for 'presidents' and 'managers'.

Select job, avg(sal)
    From emp group by job
    Having job='president' or job='manager' ;
    Replace the above query with the following :
    Select job, avg(sal)
    From emp
    Where job='president' or job='manager'
    Group by job ;

Reason:- the where clause filters the rows from the table according to the search condition. Then the group by clause is applied only on the filtered rows. It saves time. If as opposed to this, if the rows are grouped first then the row groups are filtered using the having clause, it leads to an increased overhead in terms of time required for execution of the query.