Database System Concepts

(Unit-5)
Prepared by:
Neeraj Sharma
(Assistant Professor, JLU-SOET)

1
Transaction
 The transaction is a set of logically related operation. It contains a group
of tasks.
 A transaction is an action or series of actions. It is performed by a single
user to perform operations for accessing the contents of the database.
 Example: Suppose an employee of bank transfers Rs 800 from X's
account to Y's account. This small transaction contains several low-level
tasks:
X's Account Y's Account
Open_Account(X) Open_Account(Y)
Old_Balance = X.balance Old_Balance = Y.balance
New_Balance = Old_Balance - 800 New_Balance = Old_Balance + 800
X.balance = New_Balance Y.balance = New_Balance
Close_Account(X) Close_Account(Y)
2
Operations of Transaction
 Following are the main operations of transaction:
 Read(X): Read operation is used to read the value of X from the database
and stores it in a buffer in main memory.
 Write(X): Write operation is used to write the value back to the database
from the buffer.
 Let's take an example to debit transaction from an account which consists
of following operations:
1. R(X);
2. X = X - 500;
3. W(X);

3
Operations of Transaction
 Let's assume the value of X before starting of the transaction is 4000.
 The first operation reads X's value from database and stores it in a buffer.
 The second operation will decrease the value of X by 500. So buffer will contain
3500.
 The third operation will write the buffer's value to the database. So X's final
value will be 3500.
 But it may be possible that because of the failure of hardware, software or
power, etc. that transaction may fail before finished all the operations in the set.
 For example: If in the above transaction, the debit transaction fails after
executing operation 2 then X's value will remain 4000 in the database which is
not acceptable by the bank.
 To solve this problem, we have two important operations:
 Commit: It is used to save the work done permanently.
 Rollback: It is used to undo the work done
4
Operations of Transaction
 begin_transaction − A marker that specifies start of transaction execution.
 read_item or write_item − Database operations that may be interleaved with
main memory operations as a part of transaction.
 end_transaction − A marker that specifies end of transaction.
 commit − A signal to specify that the transaction has been successfully
completed in its entirety and will not be undone.
 rollback − A signal to specify that the transaction has been unsuccessful and so
all temporary changes in the database are undone. A committed transaction
cannot be rolled back

5
Desirable Properties of Transactions
 Any transaction must maintain the ACID properties, viz. Atomicity, Consistency,
Isolation, and Durability.
 Atomicity − This property states that a transaction is an atomic unit of
processing, that is, either it is performed in its entirety or not performed at all.
No partial update should exist.
 Consistency − A transaction should take the database from one consistent state
to another consistent state. It should not adversely affect any data item in the
database.
 Isolation − A transaction should be executed as if it is the only one in the
system. There should not be any interference from the other concurrent
transactions that are simultaneously running.
 Durability − If a committed transaction brings about a change, that change
should be durable in the database and not lost in case of any failure.

6
Concurrency Issues

7
Transaction States

8
Transaction States

9
Schedules of Transactions

10
Introduction to Locking
 Locking is one of the most commonly used concurrency control schemes in
DBMS. This works by associating a variable lock on the data items. This
variable describes the status of the data item with respect to the possible
operations that can be applied on it. The value of this variable is used to control
the concurrent access and the manipulation of the associated data item.
 The concurrency control technique in which the value of the lock variable is
manipulated is called locking. The technique of locking is one way to ensure
Serializability in DBMS.
 In DBMS, locking is the responsibility of a subsystem called lock manager

11
Types of Locking Techniques
 To control concurrency there are various types of locks which can be applied in
DBMS.

12
Binary Locks
 A binary lock has two states or values associated with each data item. These
values are:
 Locked – 1
 Unlocked – 0
 If a data item is locked, then it cannot be accessed by other transactions i.e.,
other transactions are forced to wait until the lock is released by the previous
transaction.
 But, if a data item is in the unlocked state, then, it can be accessed by any
transaction and on access the lock value is set to locked state.
 These locks are applied and removed using Lock () and Unlock () operation
respectively
 In binary locks, at a particular point in time, only one transaction can hold a lock
on the data item. No other transaction will be able to access the same data
concurrently. Hence, Binary locks are very simple to apply but are not used
practically. 13
Shared / Exclusive Locks
 In shared locks, multiple users are allowed to access the same data item with a
read lock which is shared by them. But, in case when a transaction needs to write
a data item, then an exclusive lock is applied on that data item. So here, we
classify the locks as:
 Shared Locks
 Exclusive Locks
 Shared Locks
 Shared locks are applied to a data item when the transaction requests a read
operation on the data item. A shared lock will allow multiple transactions to
only read the data item concurrently.
 As these locks are applied on read operation, they will not compromise on
the consistency of the database.

14
Exclusive Locks
 Exclusive locks on the other hand are applied on the transactions which request a
write operation on the data item.
 The transaction which is modifying the data item requests an exclusive lock on
the data item and hence any other transaction which needs access to the data
item has to wait until the lock applied by the previous transaction has been
released by it.
 But when exclusive locks are applied there are situations when a transaction
enters into a wait state indefinitely. Such a state where a transaction cannot come
out of the wait state is known as a deadlock.

15
Two Phase Locking
 The Two Phase Locking Techniques guarantee Serializability in DBMS. A
transaction is said to follow Two Phase Locking Protocol if all locking
operations in the transaction precede the first unlock operation.
 In this, locks are applied in two phases:
 Growing Phase
 This phase is also known as the first phase or the expanding phase. It is in this phase that the
transaction acquires all the locks needed by it but it cannot release any locks here.
 Shrinking Phase
 This phase is also known as the second phase or the contracting phase. Here a transaction is
not allowed to acquire any new locks but it can release the existing locks it holds. The Two
Phase Locking Protocol helps solve problems of lost update, inconsistent analysis or dirty read
too.

16
Database Recovery
 Database Systems like any other computer system, are subject to failures but the
data stored in them must be available as and when required. When a database
fails it must possess the facilities for fast recovery. It must also have atomicity
i.e. either transactions are completed successfully and committed (the effect is
recorded permanently in the database) or the transaction should have no effect
on the database.

17
Types of Recovery Techniques in DBMS
 Database recovery techniques are used in database management systems
(DBMS) to restore a database to a consistent state after a failure or error has
occurred. The main goal of recovery techniques is to ensure data integrity and
consistency and prevent data loss.
 There are mainly two types of recovery techniques used in DBMS
 Rollback/Undo Recovery Technique
 Commit/Redo Recovery Technique

18
Rollback/Undo Recovery Technique
 The rollback/undo recovery technique is based on the principle of backing out or
undoing the effects of a transaction that has not been completed successfully due
to a system failure or error. This technique is accomplished by undoing the
changes made by the transaction using the log records stored in the transaction
log. The transaction log contains a record of all the transactions that have been
performed on the database. The system uses the log records to undo the changes
made by the failed transaction and restore the database to its previous state.

19
Commit/Redo Recovery Technique
 The commit/redo recovery technique is based on the principle of reapplying the
changes made by a transaction that has been completed successfully to the
database. This technique is accomplished by using the log records stored in the
transaction log to redo the changes made by the transaction that was in progress
at the time of the failure or error. The system uses the log records to reapply the
changes made by the transaction and restore the database to its most recent
consistent state.
 In addition to these two techniques, there is also a third technique called
checkpoint recovery.
 Checkpoint Recovery is a technique used to reduce the recovery time by
periodically saving the state of the database in a checkpoint file. In the event of a
failure, the system can use the checkpoint file to restore the database to the most
recent consistent state before the failure occurred, rather than going through the
entire log to recover the database.

20
Database Security
 Authentication
 User authentication is to make sure that the person accessing the database is
who he claims to be. Authentication can be done at the operating system
level or even the database level itself. Many authentication systems such as
retina scanners or bio-metrics are used to make sure unauthorized people
cannot access the database.
 Authorization
 Authorization is a privilege provided by the Database Administer. Users of
the database can only view the contents they are authorized to view. The rest
of the database is out of bounds to them.

21
Database Security
 The different permissions for authorizations available are:
 Primary Permission - This is granted to users publicly and directly.
 Secondary Permission - This is granted to groups and automatically awarded
to a user if he is a member of the group.
 Public Permission - This is publicly granted to all the users.
 Context sensitive permission - This is related to sensitive content and only
granted to a select users.

22
Database Security
 The categories of authorization that can be given to users are:
 System Administrator - This is the highest administrative authorization for a
user. Users with this authorization can also execute some database
administrator commands such as restore or upgrade a database.
 System Control - This is the highest control authorization for a user. This
allows maintenance operations on the database but not direct access to data.
 System Maintenance - This is the lower level of system control authority. It
also allows users to maintain the database but within a database manager
instance.
 System Monitor - Using this authority, the user can monitor the database and
take snapshots of it.

23
Database Security
 Database Integrity
 Data integrity in the database is the correctness, consistency and
completeness of data. Data integrity is enforced using the following three
integrity constraints:
 Entity Integrity - This is related to the concept of primary keys. All tables
should have their own primary keys which should uniquely identify a row and
not be NULL.
 Referential Integrity - This is related to the concept of foreign keys. A foreign
key is a key of a relation that is referred in another relation.
 Domain Integrity - This means that there should be a defined domain for all the
columns in a database.

24
Thank you!!

25