Department of Computer Science

Advanced Database system

11/18/2025 2021 Acadamic Year 1

 Chapter 3
Transaction Processing Concepts
What is A Transaction:
 A transaction is an action, or a series of actions, carried out by a
single user or an application program, which reads or updates the
contents of a database.
It is a Logical unit of database processing that includes one or more
access operations (read -retrieval, write -insert or update, delete).
Transaction Processing is an application program may contain
several transactions separated by the Begin and End transaction
boundaries.
 If the database operations in a transaction do not update the
database, it is called “Read-only transaction”

11/18/2025 2021 Acadamic Year

 Transaction User
 According to number of user that concurrently connect to the Transaction
system it is divided into :

o Single User: only one user use the system in each time

o Multi User: many users use the system in the same time

Transaction Concurrency Processing

According to modes of Concurrency processing divided into:

o Concurrency Interleaved processing: Concurrent execution of processes

is interleaved in a single CPU

o Parallel processing: Processes are concurrently executed in multiple CPUs.

11/18/2025 2021 Acadamic Year

 Transaction properties
• To ensure integrity of data, the database system requires to
maintain the ACID properties of the transactions:
a. Atomicity.
b. Consistency preservation.
c. Isolation.
d. Durability or permanency.

19-Dec-12 2023 Acadamic Year

 Either allAtomicity
operations of the transaction are reflected properly in
the database, or none are
 Idea behind ensuring atomicity is following:
The database system keeps track of the old values of any
data on which a transaction performs a write
If the transaction does not complete, the DBMS restores the
old values to make it appear as though the transaction have
never execute.

11/18/2025 2021 Acadamic Year

Consistency
If the database is consistency before an execution of the
transaction, the database remains consistent after the execution
of the transaction.
The consistency requirement here is that the sum of A and B be
unchanged by the execution of the transaction.

11/18/2025 2021 Acadamic Year

 Isolation
 Even though multiple transactions may execute concurrently, the
system guarantees that, Execution of transaction should not be
interfered with by other transactions

for every pair of transactions TransA and TransB, it appears to TransA

that either TransB finished execution before TransA started, or TransB

started execution after TransA finished.

Thus, each transaction is unaware of each other executing

concurrently in the system.

11/18/2025 2021 Acadamic Year

 Durability or permanency
 After a transaction completes successfully, the changes it has made
to the database persist, even if there are system failures.

 These changes must not be lost because of any failure

 ensures that, transaction has been committed, that transaction’s

updates do not get lost, even if there is a system failure

PREPARED BY ENJIGU B. 2023 Acadamic Year

 Example1)
Transfer 50 ETB from account A
to account B Atomicity - shouldn’t take
money from A without
Read(A) giving it to B
A = A - 50 Consistency - money isn’t
Write(A) transaction lost or gained
Read(B) Isolation - other queries
B = B+50 shouldn’t see A or B
Write(B) change until completion
Durability - the money does
not go back to A

11/18/2025 2021 Acadamic Year

 Cont…
Example2) Let Ti be a transaction that transfer 5000 ETB from
Bona’s account (5000) to Beshatu’s account . The transaction can
be defined as follow :

• Ti: read (Bona) (withdraw from Bona)

Bona := Bona – 5000
write (Bona); (update Bona)
____________________________________________
read (Beshatu) (deposit to Beshatu)
Beshatu := Beshatu + 5000
write (Beshatu) (update Beshatu)

11/18/2025 2021 Acadamic Year

 Transaction Failure

o DBMS is responsible for making sure that either All

operations in transaction are completed successfully and the
changes is recorded permanently in the database.
o Even DBMS assure Transactions should be durable it cannot
prevent all sorts of transaction failures in the middle of execution
Example of failure:
o A computer failure (System crash)
o A transaction or system error
o Load error or exception conditions detected by the transaction
o Concurrency control enforcement: by concurrency control method
o Disk failure
o Physical problems and catastrophes: ex. Power failure, fire,
overwrite disk
11/18/2025 2021 Acadamic Year
The Transaction Manager
 The transaction manager enforces the ACID properties
It schedules the operations of transactions
COMMIT and ROLLBACK are used to ensure atomicity
 Locks or timestamps are used to ensure consistency and
isolation for concurrent transactions
 A log is kept to ensure durability in the event of system
failure

11/18/2025 2021 Acadamic Year

 The Transaction Log
 Log is An ordered list of REDO/UNDO actions
 The system maintain log by keeping track of all transactions that
effect the database
 Keeps information about operations made by transactions
 Log file keep track of the database from start transaction to
complete transaction
 Each log record has a unique Log Sequence Number (LSN).
 The transaction log records the details of all transactions:
Any changes the transaction makes to the database
How to undo these changes
When transactions complete and how
 The log is stored on disk, not in memory
If the system crashes it is preserved
11/18/2025 2021 Acadamic Year
 State of transaction
For recovery purpose, the system needs to keep track of when the
transaction :
Active, the initial state; the transaction stays in this state while it is
executing.
Partially committed, after the final statement has been executed
Failed, after the discovery that normal execution can no longer
proceed.
Aborted, after the transaction has been rolled backed and the
database has been restored to its state prior to the start of
transaction.
Committed, after successful completion

11/18/2025 2021 Acadamic Year

 Cont…
The recovery manager keep track of the followings in Transaction
Process :
 Begin_transaction: mark the beginning of transaction execute
 Read or write: specified operations on the database item that
executes as part of transaction
 End_transaction: specifies that operations have ended and
marks the end of execution
 Commit_Transaction: successful end
 Rollback: unsuccessful end (undone)

11/18/2025 2021 Acadamic Year

 Transaction Concurrent Executions
 Transaction processing permit

Multiple transactions to run concurrently.

Multiple transactions to update data concurrently

Why concurrency is allowed ?

Improved throughput of transactions and system resource

utilization

Reduced waiting time of transactions

11/18/2025 2021 Acadamic Year

 Scheduling Transactions
 Schedule is A series of operation order from one transaction to
another transaction
Serial schedule
 Schedule that does not interleave the actions of different
transactions.
 one transaction is executed completely before starting another
transaction.
Non-serial Schedule
 If interleaving of operations is allowed, then there will be non-serial
schedule.
 It contains many possible orders in which the system can execute the
individual operations of the transactions.

11/18/2025 2021 Acadamic Year

 Serial Schedule

11/18/2025 2021 Acadamic Year

 Non-serial schedule

11/18/2025 2021 Acadamic Year

 Class Activities
 Describe role of DBMS Transaction Processing
 How concurrent Execution managed in Transaction Processing
 Explain the different between Serial and non-Serial schedule

11/18/2025 2021 Acadamic Year

 CHAPTER FOUR
Concurrency Control Techniques
 Concurrency Control in DBMS is a procedure of managing

simultaneous operations without conflicting with each other.

 Concurrency Control is the management procedure that is

required for controlling concurrent execution of the operations

that take place on a database

 In concurrent Processing there are chances of a conflict to occur

which can leave database to an inconsistent state.

11/18/2025 2021 Acadamic Year

 Cont…
Conflict Example:
• You and your brother have a joint bank account, from which you
both can withdraw money. Now let’s say you both go to different
branches of the same bank at the same time and try to withdraw
8000 ETB, your joint account has only 9000 balance. Now if we
don’t have concurrency control in place you both can get 8000
ETB at the same time but once both the transactions finish the
account balance would be -7000 which is not possible and leaves
the database in inconsistent state
◘ To handle these conflicts we need concurrency control in DBMS,
which allows transactions to run simultaneously but handles them
in such a way so that the integrity of data remains intact.

11/18/2025 2021 Acadamic Year

 Concurrency control protocols types
The concurrency control protocols ensure the ACID properties of
the concurrent execution of the database transactions.

Concurrency control protocols can be categorized as :

a) Lock Based Concurrency Control Protocol

b) Time Stamp Concurrency Control Protocol

c) Validation Based Concurrency Control Protocol

11/18/2025 2021 Acadamic Year

 Lock-based Protocols
Locking prevents multiple applications from obtaining copies of the
same resource when the resource is about to be changed
The main techniques used to control concurrent execution of
transactions are based on the concept of locking data items
Locks are used as a means of synchronizing the access by
concurrent transactions to the database items.
Locks are of two kinds
a) Binary Locks − A lock on a data item can be in two states locked
or unlocked.
b) Shared/exclusive − This type of locking mechanism differentiates
the locks based on their uses.
 Allowing more than one transaction to write on the same data item
would lead the database into an inconsistent state.
 Read locks are shared because no data value is being changed
11/18/2025 2021 Acadamic Year
 Binary Locks
A binary lock can have two states or values:
a) locked(1) and
b) unlocked (0)
A distinct lock is associated with each database item X.
 If the value of the lock on X is 1, item X cannot be accessed by a
database operation that requests the item.
 If the value of the lock on X is 0, the item can be accessed when
requested, and the lock value is changed to 1.
The Two operations, lock_item and unlock_item, are used with
binary locking

11/18/2025 2021 Acadamic Year

 Cont…
A transaction requests access to an item X by first issuing a
lock_item(X) operation.
If LOCK(X) = 1, the transaction is forced to wait.
 If LOCK(X) = 0, it is set to 1 and the transaction is allowed to
access item X.
When the transaction is through using the item, it issues an
unlock_item(X) operation, which sets LOCK(X) back to 0 so that X
may be accessed by other transactions.
Hence, a binary lock enforces mutual exclusion on the data item.

11/18/2025 2021 Acadamic Year

 Shared lock vs Exclusive
Exclusive lock
Shared lock
 Read-only lock  the data item can be both reads
 the data item can only read by as well as written by the
the transaction. transaction.
 allows other users to read the  prohibits other users from
locked resource, but they reading the locked resource
cannot update it  This lock is exclusive, and,
 It can be shared between the multiple transactions do not
transactions because when the modify the same data
transaction holds a lock, then it simultaneously.
can't update the data on the
data item.

11/18/2025 2021 Acadamic Year

 Chapter 5
Database Recovery Techniques
 Recovery process restores database to most recent consistent state
before time of failure
Purpose of Database Recovery
To bring the database into the last consistent state, which existed
prior to the failure.
To preserve transaction properties (Atomicity, Consistency,
Isolation and Durability).
Note: Main goal of recovery is Ensure atomicity property of a
transaction

Prepared by [Link] B. 2023 Acadamic Year

 Recovery Concepts
Undo and redo operations required to be idempotent
Executing operations multiple times equivalent to executing
just once
Entire recovery process should be idempotent
Typical recovery strategies
Restore backed-up copy of database
Identify any changes that may cause inconsistency

11/18/2025 2021 Acadamic Year

 Update Techniques
Deferred update techniques
Do not physically update the database until after transaction
commits
Redo-type log entries are needed
Undo-type log entries not necessary
Can only be used for short transactions and transactions that
change few items

11/18/2025 2021 Acadamic Year

 Immediate Update techniques
Immediate update Database may be updated by some operations of
a transaction before it reaches commit point
UNDO-type log entries must be stored
It allows database updates of an uncommitted transaction to be
made as the writes are issued
The update logs must have both old value and new value
Update log record must be written before database item is written

11/18/2025 2021 Acadamic Year

 Cont…
In-place updating techniques
o Writes the buffer to the same original disk location
o Overwrites old values of any changed data items
Shadow Paging Techniques
o Writes an updated buffer at a different disk location, to
maintain multiple versions of data items
o Shadow paging considers disk to be made of n fixed-
size disk pages
o No log required in a single-user environment, but in
multiuser

11/18/2025 2021 Acadamic Year

 The ARIES Algorithm
ARIES stands for “Algorithm for Recovery and Isolation
Exploiting Semantics”
ARIES main principles are listed below
o Write-ahead logging
o Repeating history during redo
o Logging Changes During Undo
Write-ahead logging
Ensure the before-image (BFIM) is recorded
Necessary for UNDO operation if needed
any change to DB element is first recorded in log

11/18/2025 2021 Acadamic Year

 Cont…
Repeating history during redo

Retrace all database system actions prior to crash to reconstruct

database state when crash occurred
On restart following crash, retrace all actions of DBMS before
crash

Logging changes during undo

Prevents ARIES from repeating completed undo operations if

failure occurs during recovery
Changes to DB while undoing a transaction are logged to ensure
such an action is not repeated in the event of repeated restarts
11/18/2025 2021 Acadamic Year
 Cont…

After a crash, the recovery manager is invoked and Proceed in three

phases:
Analysis:
identify dirty pages in buffer pool (i.e., changes not yet written to
disk), and identify active transactions at time of crash.
Scan down from most recent begin checkpoint to last record.
Determines appropriate start point in the log for the REDO operation
Redo:
 repeats all actions, starting from proper point in log, thus restoring the
DB state to its original status
Start at smallest recLSN in dirty page table at end of Analysis.
Redo all changes to any page that might have been dirty at crash
Only necessary REDO operations are applied
ADBMS_Notes 35
 Cont…
UNDO
 undo actions of transactions that didn’t commit --> DB reflects
only committed transactions.
Starting at end of log, in reverse order, undo changes of all
transactions at time of crash.
Every log record has associated log sequence number (LSN)

11/18/2025 2021 Acadamic Year

 Chapter 6
Database System Security
Multi-user database systems include security to control how the
database is accessed and used .

Those security Mechanisms are :

 Prevent unauthorized database access

 Prevent unauthorized access to schema objects

 Control disk usage and Audit user actions

Database System Security can be considered as :

System security

Data security ADBMS_Notes 37

 Cont…
System Security
o The access and use of the database at the system level
Example:
 username and password
 disk space allocated to users, and the system operations that
users can perform
Data security
The access and use of the database objects and
the actions that those users can have on the objects
Example : retrieving a data value from table

ADBMS_Notes 38
 AAA security model
Authentication: verifying the identity of someone who wants
to access data, resources, or applications.
Authorization: Access limits for authenticated users
user must be able to access only the information and
resources that he/she has allowed
Accounting: auditing

ADBMS_Notes 39
 User Accounts
The default administrative user accounts are automatically
created when you install Oracle/SQL /MySQL Server
Database are:
SYS: granted by DBA role, owns Data Dictionary
SYSTEM: granted the DBA role and perform all
administrative functions

ADBMS_Notes 40
 Data Type Differences
SQL Server Oracle
INTEGER NUMBER(10)
SMALLINT NUMBER(6)
TINYINT NUMBER(3)
REAL FLOAT
FLOAT FLOAT
BIT NUMBER(1)
VARCHAR(n) VARCHAR2(n)
TEXT CLOB
IMAGE BLOB
BINARY(n) RAW(n) or BLOB

ADBMS_Notes 41
 Cont…

SQL Server Oracle

VARBINARY RAW(n) or BLOB
DATETIME DATE
SMALL-DATETIME DATE
MONEY NUMBER(19,4)
NCHAR(n) CHAR(n*2)
NVARCHAR(n) VARCHAR(n*2)
SMALLMONEY NUMBER(10,4)
TIMESTAMP NUMBER
SYSNAME VARCHAR2(30),
VARCHAR2(128)

ADBMS_Notes 42
 Privileges

• Privileges are the right to execute particular SQL statements. The

database administrator (DBA) is a high-level user with the ability
to grant users access to the database and its objects
Example:
 The ability to connect to the database
 The ability to create a user
 The ability CREATE SESSION
 The ability CREATE TABLE
 The ability CREATE SEQUENCE
 The ability CREATE VIEW
 The ability CREATE PROCEDURE

ADBMS_Notes 43
 Cont…
a) System privileges: Gaining access to the database
b) Object privileges: Manipulating the content of the database
objects
System privileges can be given to a user by another user who has
administrator privileges
Example:
– Creating new users
– Removing users
– Removing tables
– Backing up tables
Special Administrative privileges: required for an administrator
to perform basic database operations are granted through two
special system privileges
• SYSDBA privilege: can do anything
• SYSOPER privilege: sub-admin access, can perform Backup,
recover, startup, shutdown
ADBMS_Notes 44
 Cont…
To grant privileges on an object, the object must be in your own
schema, or granted privileges WITH GRANT OPTION .
• An object owner can grant any object privilege on the object to
any other user or role of the database.
• The owner of an object automatically acquires all object
privileges on that object.
GRANT privilege TO user [WITH ADMIN OPTION] ;

WITH ADMIN OPTION: it means give grantee right to grant

the same privileges to other users

ADBMS_Notes 45
 Operations Authorized System Privilege

Grantee can create other Oracle users (a privilege CREATE USER

required
for a DBA role).

Grantee can drop another user. DROP USER

Grantee can drop a table in any schema. DROP ANY TABLE

Grantee can back up any table in any schema with BACKUP ANY TABLE
the export utility
Grantee can create tables in any schema. CREATE ANY TABLE

Grantee can query tables, views, or snapshots in any SELECT ANY TABLE
schema

ADBMS_Notes 46
 Creating Users

• The syntax for creating a user is:

CREATE USER username

IDENTIFIED BY password

Example:

CREATE USER Scott

IDENTIFIED BY tiger

ADBMS_Notes 47
 GRANT-Statement
GRANT privileges ON object TO users [WITH GRANT
OPTION]

GRANT SELECT
INSERT
DELETE
UPDATE
( column-name )
,
,
ALL PRIVILEGES

user-name ·
ON base relation TO
view relation PUBLIC WITH
GRANT
OPTION

ADBMS_Notes 48
 REVOKE Statement

REVOKE SELECT
GRANT INSERT
OPTION DELETE
FOR UPDATE
( column- )
name
,
,
ALL PRIVILEGES

ON table-name FROM user-name RESTRICT

view-name PUBLIC CASCADE
,

ADBMS_Notes 49
 GRANT and REVOKE of Privileges

 GRANT INSERT, SELECT ON Employees TO Chala

 Chala can query Employees or insert tuples into it

 GRANT DELETE ON Employees TO Beka WITH GRANT

OPTION
 Beka can delete tuples, and also authorize others to do so.

 GRANT UPDATE Salary ON Employees TO Dawit

 Dawit can update (only) the salary field of Employees tuples.

ADBMS_Notes 50
 Granting Object Privileges
• Grant query privileges on the EMPLOYEES table.
GRANT select
ON employees
TO Norah, Sarah;
• Grant privileges to update specific columns to users and roles.
GRANT update (department name, location_id)
ON departments
TO Scott, manager;

ADBMS_Notes 51
 Cont…
• Syntax to Revoke Object Privileges :

REVOKE privilege ,ALL ON object FROM user/role/PUBLIC;

Example

REVOKE select, insert

ON departments
FROM Scott;

ADBMS_Notes 52
 What Is a Role?

A role is a named group of related privileges that can be granted

to the user.
Pre-defined roles:
– DBA: it has all system privileges
– RESOURCE: Enables a user to create certain types of objects
in his own schema
– CONNECT: Enables a user to connect to the database. Grant
this role to any user or application that needs database access.

ADBMS_Notes 53
 Creating and Assigning a Role

First, the DBA must create the role. Then the DBA can assign
privileges to the role and users to the role.
Syntax
CREATE ROLE rolename;
Creating and Granting Privileges to a Role
Create a role
CREATE ROLE manager;
Grant system privileges to a role
GRANT create table, create view
TO manager;
Grant a role to users
GRANT manager TO Maha, Nora;
ADBMS_Notes 54
 Changing Your Password

The DBA creates your user account and initializes your

password.
You can change your password by using the
ALTER USER statement.
Syntax
ALTER USER user IDENTIFIED BY newpassword;
EXAMPLE:
ALTER USER Scott
IDENTIFIED BY lion;

ADBMS_Notes 55
WITH GRANT OPTION and PUBLIC Keywords

• Give a user authority to pass along privileges.

GRANT select, insert
ON departments
TO Scott
WITH GRANT OPTION;
• Allow all users on the system to query data from Alice’s
DEPARTMENTS table.
GRANT select
ON Alice. Departments
TO PUBLIC;
ADBMS_Notes 56
DBMS LANGUAGES STATEMENT

ADBMS_Notes 57
 DDL (Data Definition Language)
DDL statements are used to alter/modify a database or table
structure and schema.
CREATE – create a new Table, database, schema
ALTER – alter existing table, column description
DROP – delete existing objects from database
Creating a New Table with an Encrypted Column
CREATE TABLE employee ( first_name VARCHAR2(128),
last_name VARCHAR2(128), empID NUMBER, salary
NUMBER(6) ENCRYPT );
Encrypting Unencrypted Columns
ALTER TABLE employee MODIFY (first_name ENCRYPT);
 Disabling Encryption on a Column
ALTER TABLE employee MODIFY (first_name DECRYPT);

ADBMS_Notes 58
 DML (Data Manipulation Language)
DML operations performed on data such as selecting a few records
from a table, inserting new records, deleting unnecessary records,
and updating/modifying existing records.

Example :

SELECT – select records from a table

INSERT – insert new records
UPDATE – update/Modify existing records
DELETE – delete existing records

ADBMS_Notes 59
 DCL (Data Control Language)
 DCL statements control the level of access that users have on
database objects.
 GRANT – allows users to read/write on certain database
objects
REVOKE – keeps users from read/write permission on
database objects
TCL (Transaction Control Language)
 TCL statements allow you to control and manage transactions
to maintain the integrity of data within SQL statements.
 BEGIN Transaction – opens a transaction
COMMIT Transaction – commits a transaction
ROLLBACK Transaction – ROLLBACK a transaction in
case of any error
ADBMS_Notes 60
Emerging Technology