DATABASE MANAGEMENT SYSTEM
SEMESTER-IV
LAB SHEET - 8
TRANSACTIONS
❖ Databases are all about sharing data, so it is common for multiple users to be
accessing and even changing the same data at the same time. The simultaneous
execution of operations is called concurrency. Sometimes concurrency can get us into
trouble if our changes require multiple SQL statements. In general, if two or more
users access the same data and one or more of the statements changes the data, we
have a conflict. This is a classic problem in database systems; it is called the isolation
or serializability problem. If the users perform multiple steps, conflicts can cause
incorrect results to occur. To deal with this problem, databases allow the grouping of
a sequence of SQL statements into an indivisible unit of work called a transaction. A
transaction ends with either a commit or a rollback:
o commit—A commit permanently stores all of the changes performed by the
transaction. o rollback—A rollback removes all of the updates performed by the
transaction, no matter how many rows have been changed. A rollback can be
executed either by the DBMS to prevent incorrect actions or explicitly by the
user.
❖ The DBMS provides the following guarantees for a transaction, called the ACID
properties: Atomicity, consistency, Isolation, and durability. These properties will be
covered in the course at detail.
❖ SQL starts a transaction automatically when a new statement is executed if there is
no currently active transaction. This means that a new transaction begins automatically
with the first statement after the end of the previous transaction or the beginning of the
session. ❖ A user may explicitly start a transaction using the START TRANSACTION
statement.
SET TRANSACTION NAME <string>;
❖ Commit:
SET TRANSACTION NAME ‘t1’;
UPDATE vrs SET inventory = inventory + 10;
SELECT * FROM vrs;
COMMIT;
SELECT * FROM vrs;
❖ Rollback:
SET TRANSACTION NAME ‘t2’;
UPDATE vrs SET inventory = inventory -20;
SELECT * FROM vrs;
ROLLBACK;
SELECT * FROM vrs;
❖ Here note that without using start transaction, if you execute the update query alone,
the values are automatically committed. There is no way to revert back.
SAVEPOINTS
❖ SQL allows you to create named placeholders, called savepoints, in the sequence of
statements in a transaction. You can rollback to a savepoint instead of to the
beginning of the transaction. Only the changes made after the savepoint are undone.
To set a savepoint, use the SAVEPOINT command:
SAVEPOINT <savepoint name>
❖ If we create a savepoint, we can rollback to that savepoint with the
following: ROLLBACK TO SAVEPOINT <savepoint name>
❖Executing ROLLBACK without designating a savepoint or executing a COMMIT
deletes all savepoints back to the start of the transaction. A rollback to a particular
savepoint deletes all intervening savepoints.
UPDATE vrs SET inventory = inventory + 25;
SELECT * FROM vrs;
SAVEPOINT spoint1;
UPDATE vrs SET inventory = inventory - 15;
SELECT * FROM vrs;
SAVEPOINT spoint2;
UPDATE vrs SET inventory = inventory + 30;
SELECT * FROM vrs;
SAVEPOINT spoint3;
ROLLBACK TO SAVEPOINT spoint1;
SELECT * FROM vrs;
EXERCISES ON EMPLOYEES DATABASE:
❖Write queries for each of the following based on employee database created in
the previous labs. The scheme for employee database is shown below.
1. Begin the transaction using the START TRANSACTION statement.Then, select
maximum income among the employee.Use the COMMIT statement to complete the
transaction.
2. Delete those records of employees whose first_name starts from ‘Y’ from the employee
table and then COMMIT the changes in the database.
3. Delete those records of employees whose last_name starts from ‘B’ from the employee
table and then ROLLBACK the changes in the database by keeping Savepoints. .
4. Using a transaction, delete the Administration department and all of its sub departments.
Deliverables:
A report should be prepared with AIM, Experiments, results and conclusion.Paste the
snaps of each of the output and enter some details for respective output.
