Transaction Control Language
Transaction Control Language CSC 271
Comsats Institute of Information Technology
Popular in Database Management
verified elite notetaker
Popular in ComputerScienence
This 28 page Study Guide was uploaded by Ismail Yousuf on Saturday January 9, 2016. The Study Guide belongs to CSC 271 at Comsats Institute of Information Technology taught by Amjad Usman in Winter 2016. Since its upload, it has received 55 views. For similar materials see Database Management in ComputerScienence at Comsats Institute of Information Technology.
Reviews for Transaction Control Language
Report this Material
What is Karma?
Karma is the currency of StudySoup.
You can buy or earn more Karma at anytime and redeem it for class notes, study guides, flashcards, and more!
Date Created: 01/09/16
CSC271 Database Systems Lecture 24 Data Control Language and Transaction Control Language Instructor : Amjad Usman firstname.lastname@example.org Tuesday, December 15, 2015 Data Control Language (DCL) Create users Create roles Use GRANT and REVOKE statements Create and use database link 3 User Access 4 Privileges 5 System Privileges 6 Creating users 7 User System Privileges 8 Granting System Privileges 9 What is a Role? 10 Creating and Granting Privileges to a Role 11 Changing Password 12 Object Privileges 13 Granting Object Privileges 14 Revoking Privileges 15 Data Dictionary for Privileges 16 Database Transactions A database transaction consists of one of the following: DML statements which constitute one consistent change to the data One DDL statement One DCL statement 17 Database Transactions Begin when the first DML SQL statement is executed End with one of the following events: A COMMIT or ROLLBACK statement is issued A DDL or DCL statement executes (automatic commit) The system crashes 18 Advantages of COMMIT and ROLLBACK Statements With COMMIT and ROLLBACK statements,you can: Ensure data consistency Preview data changes before making changes permanent Group logically related operations 19 Controlling Transactions Time COMMIT Transaction DELETE SAVEPOINT A INSERT UPDATE SAVEPOINT B INSERT ROLLBACK ROLLBACK ROLLBACK to SAVEPOINT B to SAVEPOINT A 20 Rolling Back Changes to a Marker Create a marker in a current transaction by using the SAVEPOINT statement. Roll back to that marker by using the ROLLBACK TO SAVEPOINT statement. UPDATE... SAVEPOINT update_done; Savepoint created. INSERT... ROLLBACK TO update_done; Rollback complete. 21 Implicit Transaction Processing An automatic commit occurs under the following circumstances: DDL statement is issued DCL statement is issued Normal exit from SQL*Plus,without explicitly issuing COMMIT or ROLLBACK statements An automatic rollback occurs under an abnormal termination of SQL*Plus or a system failure. 22 State of the Data Before COMMIT or ROLLBACK The previous state of the data can be recovered. The current user can review the results of the DML operations by using the SELECT statement. Other users cannot view the results of the DML statements by the current user. The affected rows are locked;other users cannot change the data within the affected rows. 23 State of the Data after COMMIT Data changes are made permanent in the database. The previous state of the data is permanently lost. All users can view the results. Locks on the affected rows are released;those rows are available for other users to manipulate. All save points are erased. 24 Committing Data Make the changes. DELETE FROM employees WHERE employee_id = 99999; 1 row deleted. INSERT INTO departments VALUES (290, 'Corporate Tax', NULL, 1700); 1 row inserted. Commit the changes. COMMIT; Commit complete. 25 State of the Data After ROLLBACK Discard all pending changes by using the ROLLBACK statement: Data changes are undone. Previous state of the data is restored. Locks on the affected rows are released. DELETE FROM copy_emp; 22 rows deleted. ROLLBACK; Rollback complete. 26 State of the Data After ROLLBACK: Example DELETE FROM test; 25,000 rows deleted. ROLLBACK; Rollback complete. DELETE FROM test WHERE id = 100; 1 row deleted. SELECT * FROM test WHERE id = 100; No rows selected. COMMIT; Commit complete . 27 THANK YOU SO MUCH
Are you sure you want to buy this material for
You're already Subscribed!
Looks like you've already subscribed to StudySoup, you won't need to purchase another subscription to get this material. To access this material simply click 'View Full Document'