Database development with PL/SQL INSY 8311

Instructor:
▪ Eric Maniraguha | eric.maniraguha@auca.ac.rw | LinkedIn Profile

6h00 pm – 8h50 pm
▪ Monday A -G209
▪ Tuesday B-G209
▪ Wednesday E-G209 April 2025
▪ Thursday F-G308 1
 Database development with PL/SQL

Reference reading

▪ 13 Data Concurrency and Consistency

▪ 11 Data Concurrency and Consistency

Lecture 12 - Transactions and Concurrency 2

 Transactions: Fundamental Concepts

Definition & Example

Follows ACID properties:

A transaction is a sequence of database operations

treated as a single logical unit of work
Atomicity: All operations complete or none complete
Ensures data integrity and consistency

Consistency: Database remains in a valid state before and

Examples: Transferring money between bank after transaction
accounts, placing an order online, updating
employee data.
Isolation: Transactions do not interfere with each other

Durability: Completed transactions are permanently saved

3
 Transaction States

Transaction Active: Partially Committed: Failed: Aborted:

States Committed:

▪Initial state, ▪Final operations ▪Successfully ▪Error occurred ▪Transaction

executing completed completed rolled back
operations

Source Image: https://www.guru99.com/dbms-transaction-management.html

4
 Concurrency Control Problems

Key Issues Lost Update Problem Dirty Read Non-Repeatable Read Phantom Read

Multiple transactions Reading uncommitted Same query produces New rows

modify same data data from another different results in one inserted/deleted
simultaneously transaction transaction during transaction

One transaction's
Can lead to incorrect Data changed by
changes overwrite Affects range queries
results concurrent transaction
another's

5
 Concurrency Control Techniques & Isolation Levels

Concurrency Control Techniques Isolation Levels

Read Uncommitted
▪Lowest isolation
▪Allows dirty reads
▪Maximum concurrency, minimum consistency
Lock-Based Mechanisms
Read Committed
▪Prevents dirty reads
▪Default in many database systems
Shared Locks (Read Locks)
Repeatable Read
▪Multiple transactions can read data simultaneously
▪Prevents write operations ▪Prevents dirty and non-repeatable reads
▪May allow phantom reads
Serializable
Exclusive Locks (Write Locks) ▪Highest isolation level
▪Only one transaction can modify data ▪Completely prevents concurrent anomalies
▪Blocks other read/write operations ▪Lowest performance, highest consistency

6
 Transactions and Concurrency in Databases

▪Optimistic Concurrency •Assumes conflicts are Implementation Considerations

Control likely
▪Assumes conflicts are rare •Locks resources
▪Checks for conflicts at preemptively
transaction commit Choose appropriate isolation level
•Prevents conflicts before
▪Allows more parallel
processing
they occur
Balance between consistency and performance

Transaction Pessimistic
Management Concurrency Use transaction boundaries carefully
Strategies Control
Implement proper error handling

Consider database-specific concurrency features

Code Example (Pseudocode)

BEGIN TRANSACTION;
-- Check account balance
UPDATE accounts SET balance = balance - 100 WHERE id = 123;
UPDATE accounts SET balance = balance + 100 WHERE id = 456;
COMMIT;

7
 Best Practices & Emerging Trends & Key Takeaways

Distributed
transaction
management

Keep Emerging Transactions

ensure data
transactions Trends integrity
short

NoSQL database
concurrency
Would you like me
Log and models to elaborate on
Concurrency
Minimize control prevents
any specific aspect
monitor data
lock of transactions and
inconsistencies
transaction
activities
duration
Multi-version
concurrency?
Key
Best concurrency Takea
Practices control (MVCC)
ways

Use Performance and

Choose techniques
Handle consistency
appropriate require careful
based on specific
potential requirements
isolation balancing
deadlocks
levels 8
 Beyond Basics: Advanced PL/SQL Challenges
for the Curious Mind
Apache Airflow - Wikipedia

YouTube Tutorial

1. SQL Data Warehouse from Scratch | Full Hands-On Data Engineering Project
2. Complete a project with me! - Building Data Model and Database
3. Football Data Analytics | Azure End To End Data Engineering Project
4. Airlines Data Ingestion | End-To-End Data Engineering Project | AWS Services | COMPLETE TUTORIAL ArcGIS Pro- the world's leading GIS ...

5. End-to-End DWBI Project: Real-Life Data Integration with Snowflake, Python, SQL & PowerBI

Articles
▪ Roadmap for Data Engineering 2023
DBT (data build tool ...

▪ How To Master The Data Engineer Roadmap For Career Advancement

▪ Roadmap for Data Engineering 2025
▪ 30+ Data Engineering Projects for Beginners in 2025
Power BI
MongoDB, Inc. Announces Fourth Quarter ...

Project Jupyter - Wikipedia

up Docker with ...

Django Community | Django

Linux - CyberHoot Cyber Library

Programming with Python | Accredited ...

GitHub Logo, symbol, meaning, history ...

Snowflake - inovex GmbH

Note: Clicking on any of these images will direct you to a tutorial that can help you learn more about the technology.
Beyond Basics: Advanced PL/SQL Challenges
for the Curious Mind - Open-Source Apps

10
 Closing Remarks
As we bring this PL/SQL course to a close, I sincerely hope that you now possess a solid foundation in both the theoretical principles and
practical skills of PL/SQL programming. Throughout this journey, you have learned how to:

▪ Declare and manipulate variables effectively.

▪ Apply control structures to manage the flow of logic.
▪ Create and use procedures and functions for modular code.
▪ Handle exceptions to build robust and fault-tolerant programs.
▪ Write efficient, reliable, and maintainable PL/SQL applications.

Key Takeaways
▪ Practice – Keep coding. Build projects, solve real problems, and reinforce your understanding
through hands-on experience.
▪ Explore – Don’t stop here. Go deeper into advanced topics like performance tuning, security,
and PL/SQL optimization.
▪ Collaborate – Learn with others. Join communities, ask questions, share knowledge, and grow
together.

You are now equipped with the tools and mindset to succeed in your database career. The road
ahead will be filled with opportunities and challenges—but remember, each line of code, each
11 debug
session, and each project brings you closer to mastery.
 Biblical Message & Inspirational Quote

Biblical Message

"For I know the plans I have for you," declares the Lord, "plans to prosper you and not to harm you, plans to give you a hope and a future."
— Jeremiah 29:11
This powerful verse reminds us that beyond the technical journey lies a deeper purpose—one filled with hope, growth, and success. Even when challenges arise, know that your path is being
shaped for greatness.

Inspirational Quote
"Success is the sum of small efforts, repeated day in and day out."
— Robert Collier
Your progress in PL/SQL, like any worthwhile endeavor, is built through consistency, persistence, and daily effort. Stay committed. Stay curious.

12
 Congratulations on reaching this milestone. Keep pushing your limits and
never stop learning. I'm excited to see where your journey takes you next—
and I’m confident it will be somewhere amazing.

Go forth and code with purpose.

13