47 Database Transactions

• ·         Ends the current transaction by discarding all pending data changes- ROLLBACK
• ·         Enables the user to discard changes made to the database- ROLLBACK
• ·         Creates a marker in a transaction, which divides the transaction into smaller pieces- SAVEPOINT
• ·         guarantees a consistent view of the data by all users at all times- Read consistency [Read consistency - is an automatic implementation, guarantees that readers of the data see consistent data that is not currently undergoing change.]
• ·         Mechanisms that prevent destructive interaction between transactions accessing the same resource that can be granted to the user-Lock [Locking: Implicit locking occurs in oracle in all statements except SELECT to avoid destructive interaction b/w transactions accessing same resource. EXPLICIT locking is also possible in oracle.]
• ·         a collection of DML statements that form a logical unit of work- Transaction

1.  Define the COMMIT, ROLLBACK, and SAVEPOINT statements as they relate to data transactions.

• ·         COMMIT, ROLLBACK and SAVEPOINT are known as TCL (Transaction Control Language).
• ·         COMMIT - make all pending changes permanent.
• ·         ROLLBACK - discard the pending changes made. Automatic rollback will occur in case of system failure to protect data integrity.
• ·         SAVEPOINT - marker to divide transaction into smaller pieces, ROLLBACK to SAVEPOINT is possible. SAVEPOINT is not a schema object and can't be referenced in data dictionary, lost after commit or rollback (transaction complete).
• ·         TRANSACTION-

·         Starts with:

o   DML (INSERT UPDATE DELETE MERGE)

·         And ends with:

o   COMMIT,

o   Exit normally (implicit commit),

o   ROLLBACK,

o   DDL [CREATE DROP ALTER RENAME TRUNCATE]

o   DCL [GRANT OR REVOKE].

2.  What data will be committed after the following statements are issued?

INSERT INTO R values (5, 6);

SAVEPOINT my_savepoint_1;

INSERT INTO R values (7, 8);

SAVEPOINT my_savepoint_2;

INSERT INTO R values (9, 10);

ROLLBACK TO my_savepoint_1;

INSERT INTO R values (11, 12);

COMMIT;

Ideally result should be same as:

INSERT INTO R values (5, 6);

INSERT INTO R values (11, 12);

I tried  in SQL script in APEX:

I get:

ORA-01086: savepoint 'MY_SAVEPOINT_1' never established in this session or is invalid

Now I empty the table

TRUNCATE TABLE r;

And edit the script a little bit (observe BEGIN END;): 

Conclusion: If begin + end is not used I get all the rows, which seems not the purpose of statements above. To achieve the purpose with APEX and SQL scripts I will have to use BEGIN+END

3.  Construct a SQL statement for the DJs on Demand D_SONGS table that deletes the song “All These Years,” inserts a new Country song called ‘Happy Birthday Sunshine’ by “The Sunsets” with a duration of 4 min and an ID = 60. Make sure that all data can be recovered before any changes to the table are made.

type_code is not nullable in d_songs. Purpose of this problem seems to be: show that, since INSERT fails and I am making a transaction, delete will also rollback.

Point  e)  or f) below is the solution to this problem, rest is to understand the issue.

a)      If I use simply below mentioned SQL script, delete is committed, even if insert fails:

DELETE FROM d_songs

WHERE title = 'All These Years';

INSERT INTO d_songs (id, title, duration, artist)

VALUES (60, 'Happy Birthday Sunshine', '4 min',  'The Sunsets');

COMMIT;

SELECT *  FROM d_songs

WHERE title = 'All These Years';

b)      Now I recreated the whole schema using schema creation scripts provided.

c)      The most straight forward way to verify rollback is below mentioned executed as a script:

DELETE FROM d_songs

WHERE title = 'All These Years';

INSERT INTO d_songs (id, title, duration, artist)

VALUES (60, 'Happy Birthday Sunshine', '4 min',  'The Sunsets');

ROLLBACK;

When above mentioned script executed:

How to: when INSERT fails delete should also rollback.

d)      Now I recreated the whole schema using schema creation scripts provided.

e)      Alternative1: Now make both the delete and insert one unit under a block: Save below mentioned code as script in apex:

BEGIN

DELETE FROM d_songs

WHERE title = 'All These Years';

INSERT INTO d_songs (id, title, duration, artist)

VALUES (60, 'Happy Birthday Sunshine', '4 min',  'The Sunsets');

COMMIT;

END;

Even if I skip COMMIT here, I should get same results.

SELECT *  FROM d_songs

WHERE title = 'All These Years';

f)        Alternative2: demo the use of savepoint with begin and end:

BEGIN

SAVEPOINT rollbactobeginning;

DELETE FROM d_songs

WHERE title = 'All These Years';

INSERT INTO d_songs (id, title, duration, artist)

VALUES (60, 'Happy Birthday Sunshine', '4 min',  'The Sunsets');

EXCEPTION

  WHEN OTHERS THEN

    ROLLBACK TO rollbactobeginning;

END;

SELECT *  FROM d_songs

WHERE title = 'All These Years';

SELECT * FROM d_songs WHERE id = 60;

g)       Tweak alternative 2 to understand benefit of SAVEPOINT:

BEGIN

DELETE FROM d_songs

WHERE title = 'All These Years';

SAVEPOINT rollbactobeginning;

INSERT INTO d_songs (id, title, duration, artist)

VALUES (60, 'Happy Birthday Sunshine', '4 min',  'The Sunsets');

EXCEPTION

  WHEN OTHERS THEN

    ROLLBACK TO rollbactobeginning;

END;

So, the row with given title is deleted.

4.  Write an SQL statement that will issue an automatic commit.

DELETE FROM d_songs

WHERE title = 'All These Years';

In apex, above statement will auto-commit in SQL commands window.

I tried  ROLLBACK in SQL commands window:

Rollback statement not applicable. All statements are automatically committed.

5.  Give two examples of businesses other than banks that rely on transaction control processes. Describe why each business needs transaction processing control.

·         Say, I am booking a plane ticket. If this is not a transaction, I could get my money deducted from my debit card, even if the assignment of seat to my unique id failed.

·         Say, a passenger airplane has to take off, and it follows a sequence (which includes right from fuel to weather check kind of activities) before it is actually triggered to run for flying, even if one step fails in the sequence, taking off must be cancelled, if it were not a transaction, I won't ever like to be present in a passenger airplane to die.