UNIT III TRANSACTIONS

-
3.5 TRANSACTRION CONTROL IN SQL




Transaction Support in SQL

The COMMIT, ROLLBACK, and SAVEPOINT are collectively considered as Transaction Commands

(1) COMMIT: The COMMIT command is used to save permanently any transaction to database.

When we perform, Read or Write operations to the database then those changes can be undone by rollback operations. To make these changes permanent, we should make use of commit

(2) ROLLBACK: The ROLLBACK command is used to undo transactions that have not already saved to database. For example

Consider the database table as

Following command will delete the record from the database, but if we immediately performs ROLLBACK, then this deletion is undone.

For instance -

DELETE FROM Student

WHERE RollNo = 2;

ROLLBACK;

Then the resultant table will be

(3) SAVEPOINT: A SAVEPOINT is a point in a transaction when you can roll the transaction back to a certain point without rolling back the entire transaction. The SAVEPOINT can be created as

SAVEPOINT savepoint_name;

Then we can ROLLBACK to SAVEPOIT as

ROLLBACK TO savepoint_name;

For example - Consider Student table as follows –

Consider Following commands

SQL> SAVEPOINT S1 SQL>DELETE FROM Student

Where RollNo=2;

SQL> SAVEPOINT S2

SQL>DELETE FROM Student

Where RollNo=3;

SQL> SAVEPOINT S3

SQL>DELETE FROM Student

Where RollNo=4

SQL> SAVEPOINT S4

SQL>DELETE FROM Student

Where RollNo=5

SQL> ROLLBACK TO S3;

Then the resultant table will be

Thus the effect of deleting the record having RollNo 2, and RollNo3 is undone.

Comments

Post a Comment

Popular posts from this blog

CS3492 Database Management Systems Syllabus

-
UNIT I RELATIONAL DATABASES  Purpose of Database System – Views of data – Data Models – Database System Architecture – Introduction to relational databases – Relational Model – Keys – Relational Algebra – SQL fundamentals – Advanced SQL features – Embedded SQL– Dynamic SQL UNIT II DATABASE DESIGN Entity-Relationship model – E-R Diagrams – Enhanced-ER Model – ER-to-Relational Mapping – Functional Dependencies – Non-loss Decomposition – First, Second, Third Normal Forms, Dependency Preservation – Boyce/Codd Normal Form – Multi-valued Dependencies and Fourth Normal Form – Join Dependencies and Fifth Normal Form UNIT III TRANSACTIONS Transaction Concepts – ACID Properties – Schedules – Serializability – Transaction support in SQL – Need for Concurrency – Concurrency control – Two Phase Locking - Timestamp – Multiversion – Validation and Snapshot isolation – Multiple Granularity locking – Deadlock Handling – Recovery Concepts – Recovery based on deferred and immed...
Read more

UNIT I RELATIONAL DATABASES

-
Image
 1.7 Keys In the context of a relational database, keys are one of the basic requirements of a relational database model. Keys are fundamental components that ensure data integrity, uniqueness and efficient access. It is widely used to identify the tuples(rows) uniquely in the table. We also use keys to set up relations amongst various columns and tables of a relational database. Keys in DBMS Why do we require Keys in a DBMS? Keys are important in a Database Management System (DBMS) for several reasons: Uniqueness: Keys ensure that each record in a table is unique and can be identified distinctly. Data Integrity: Keys prevent data duplication and maintain the consistency of the data. Efficient Data Retrieval: By defining relationships between tables, keys enable faster querying and better data organization. Without keys, it would be extremely difficult to manage large datasets and queries would become inefficient and prone to errors. Types of Database Keys 1. Super Key The set of o...
Read more

UNIT I WEBSITE BASICS, HTML 5, CSS 3, WEB 2.0

-
1.11 Colors   ,Shadows,Text, Transformations,Transitions,Animations,Bootstrap Framework The following explains how to use  colors, shadows, text utilities, transformations, transitions, animations , and the  Bootstrap framework  in web technologies, with examples primarily using CSS and Bootstrap.  CSS Concepts Cascading Style Sheets (CSS) provides core functionalities for styling web elements.   Concept   Description Example (CSS) Colors Used for foreground (text) and background colors. color: blue; background-color: #fff; Shadows Adds visual depth to text or box elements. box-shadow: 2px 2px 5px rgba(0,0,0,0.3); Text Controls typography, alignment, capitalization, etc.. text-align: center; text-transform: uppercase; font-family: sans-serif; Transformations Modifies the position, rotation, scale, or skew of an element in 2D or 3D space. transform: rotate(45deg);  or  transform: scale(1.1); Transitions Creates smooth, animated changes to CSS ...
Read more