CS312 — Final Term Summary (Lectures 23–24)

📘 Lecture 23 — Transaction

📖 Overview: This lecture introduces the fundamental concept of a transaction in database systems, explaining its properties and importance. It covers the four essential ACID properties that any transaction must exhibit to ensure reliable and consistent data processing, with practical examples illustrating each property.

🗂️ Topics Covered

The lecture covers the basics of transactions as logical units of work, followed by detailed explanations of the four ACID properties: Atomicity, Consistency, Isolation, and Durability. Each property is explained with definitions and concrete examples demonstrating their application in database operations.

📝 Lecture Summary

A. Transaction Basics

A transaction is a sequence of operations performed as a single logical unit of work, whether in a manual fashion by a user or automatically by a database program. A transaction is the propagation of one or more changes to the database. Practically, many SQL queries (DDL or DML) can be clubbed into a group and can be executed together as a part of a transaction. A logical unit of work, i.e., Transaction, must exhibit four properties, called the atomicity, consistency, isolation, and durability (ACID) properties, to qualify as a transaction.

🔑 Definition — Transaction: A sequence of operations performed as a single logical unit of work, representing the propagation of one or more changes to the database.

U. Atomicity Property of Transaction

This property states that a transaction must be treated as an atomic unit, that is, either all of its operations are executed or none. There must be no state in a database where a transaction is left partially completed. Atomicity ensures that all operations within the work unit are completed successfully; otherwise, the transaction is aborted at the point of failure, and previous operations are rolled back to their former state.

📌 Example:

• Transaction 1 (T1): Update command to update 100 rows
• Transaction 2 (T2): 20 rows are updated
• Transaction 3 (T3): System failure
• Result: Database should rollback updates of 20 Rows

X. Consistency Property of Transaction

This property states that before and after transaction database should be in consistent state and System constraints should not be violated in any case. No transaction should have any adverse effect on the data residing in the database. If the database was in a consistent state before the execution of a transaction, it must remain consistent after the execution of the transaction as well.

📌 Example:

• Constraint: Only characters are allowed in ENAME column
• Transaction:
  • Start of Transaction
  • Commands
  • End of Transactions
• Results: There are values in ENAME column with _ and - character in it
• Summary: Transaction is not consistent because constraint is violated.

In the above example, during the transaction, a record is entered in the ENAME column with characters _ mentioned in it. This will result in inconsistency as the constraint is violated.

W. Isolation

This property states that all the steps during the execution of transactions are done without interference. This simply means that now two transactions can interfere with each other and if another transaction wants to access same data then it should wait. In a database system where more than one transaction are being executed simultaneously and in parallel, the property of isolation states that all the transactions will be carried out and executed as if it is the only transaction in the system. No transaction will affect the existence of any other transaction.

💡 Why this matters: Isolation is crucial for preventing conflicts when multiple users access the database simultaneously, ensuring each transaction runs independently.

X. Durability

This property states that system should be capable enough to hold transactional data and if transaction fails then there should be backup and recovery process. The database should be durable enough to hold all its latest updates even if the system fails or restarts. After a transaction has completed, its effects are permanently in place in the system. The modifications persist even in the event of a system failure.

💡 Why this matters: Durability guarantees that once a transaction is committed, the data is permanently stored and will not be lost, even if the system crashes immediately afterward.

⭐ Key Takeaways

A transaction is a logical unit of work that must satisfy four ACID properties to ensure reliable database operations. Atomicity guarantees that the transaction is all-or-nothing, with partial failures causing rollback. Consistency ensures the database remains in a valid state before and after the transaction. Isolation prevents transactions from interfering with each other when executed in parallel. Durability ensures that committed transactions persist permanently, with backup and recovery mechanisms available for system failures.

🧠 Quick Revision Questions

1. What is a transaction in database terms, and what are the four ACID properties it must exhibit?
2. Explain Atomicity with an example showing what happens when a system failure occurs mid-transaction.
3. How does Consistency differ from Atomicity? Provide an example of a consistency violation.
4. Why is Isolation important in a multi-user database environment?
5. What does Durability ensure, and how does it handle system failures after a transaction completes?

📘 Lecture 24 — Locks & Granularity

📖 Overview: This lecture addresses the problems arising from concurrent transaction execution in multi-user database systems, such as lost updates and reading uncommitted data. It introduces locking as the primary mechanism to ensure transaction isolation and explores lock granularity, types, and the critical issue of deadlocks, including their prevention and detection methods.

🗂️ Topics Covered

The lecture begins by explaining the two main problems of concurrent transactions: the Lost Update Problem and the Uncommitted Data problem, using flight booking examples. It then provides the rationale for locks and covers lock basics, including read locks and write locks. The granularity of locks is detailed from database-level to column-level. The two levels of locks, Exclusive and Shared, are defined with a compatibility graph. Finally, the concepts of Deadlock, its prevention, and its detection are explained with a classic example.

📝 Lecture Summary

A. Concurrent Transaction

Concurrent execution of database transactions in a multi-user system means that any number of users can use the same database at the same time. Data is shared and accessed by multiple users simultaneously, and this simultaneous access and processing of data may lead to dirty data. The uncontrolled execution of concurrent transactions in a multi-user environment can lead to various problems. The two main problems and examples of how they can occur are:

Y. Lost Update Problem This problem occurs when multiple transactions are accessing the same data and have their operations interrupted in such a way that one transaction will access the data before the other has applied any updates. In such a scenario, the second transaction will not have access to the updated data. The isolation property of the transaction is violated as the transactions have interfered with each other.

🔑 Definition — Lost Update Problem: A concurrency problem where one transaction's update is overwritten by another transaction before it can be committed, resulting in data loss. 📐 Example: Two transactions book seats in a flight. The operations are interleaved so that Transaction 2 accesses the data (seats=15) before Transaction 1 reduces the seats by 1 (to 14). Transaction 2 then reduces the seats by 2, but its operation is overwritten by Transaction 1's final write. The final seat value is 14, but Transaction 2 holds a value of 13, which is wrong.

Z. Uncommitted Data This problem occurs when one transaction updates a data item but has not yet committed the data permanently to the database. Because of failure, the transaction is rolled back, and the data item is returned to its previous value. A second transaction accesses the updated data item before it is returned to its original value. The second transaction will read the shared data between failure and rollback, violating the isolation property.

🔑 Definition — Uncommitted Data Problem: A concurrency problem where a transaction reads data that has been updated by another transaction that subsequently fails and is rolled back. 📐 Example: Transaction 1 updates seats from 15 to 12 but does not commit. Transaction 2 then reads this uncommitted value (12) and updates it to 15. Transaction 1 then rollbacks, returning the seat value to its original 15. Transaction 2 has used incorrect (dirty) data. The final seat value is 15, but Transaction 2 holds a value of 15, which is correct but based on a wrong process.

A. Rationale for Lock

The issues in the concurrent transaction are due to the isolation property of the transaction. The issues in concurrency may lead to dirty or unreliable data. This pinpoints a need for a mechanism to ensure the isolation property of all the transactions. Locks come in as a solution.

B. Lock Basics

A lock, as a read lock or write lock, is used when multiple users need to access a database concurrently. Locks avoid multiple users accessing the shared data at the same time. If one transaction is in progress, other transactions must wait to access the same shared data. This prevents data from being corrupted or invalidated when multiple users try to read while others write to the database. A read lock can be used to prevent other users from reading a record (or page) which is being updated.

C. Granularity of Locks

The granularity of locks in a database refers to how much of the data is locked at one time. In theory, a database server can lock as much as the entire database or as little as one column of data. Such extremes affect the concurrency (number of users that can access the data).

D. Level of Locks

There are two levels of locks in a database:

1. Exclusive Lock: When a statement modifies data, its transaction holds an exclusive lock on the data that prevents other transactions from accessing the data. This lock remains in place until the transaction holding the lock issues a commit or rollback. Table-level locking lowers concurrency in a multi-user system.
2. Shared Lock: When a statement reads data without making any modifications, its transaction obtains a shared lock on the data. Another transaction that tries to read the same data is permitted to read, but a transaction that tries to update the data will be prevented from doing so until the shared lock is released.

Using exclusive locks, the locked data can be read or processed by only one user. A request for another exclusive lock or for a shared lock is rejected, as shown in the ‘Compatibility Graph’. In shared locks, several users can read the same data at the same time, but as soon as a user edits the data, a second user can no longer access this data. Requests for further shared locks are accepted, even if they are issued by different users, but exclusive locks are rejected.

E. Deadlock

In a database, a deadlock is a situation in which two or more transactions are waiting for one another to give up locks. For example, Transaction A might hold a lock on some rows in the ACCOUNTS table and needs to update some rows in the ORDERS table to finish. Transaction B holds locks on those very rows in the ORDERS table but needs to update the rows in the ACCOUNTS table held by Transaction A. Transaction A cannot complete its transaction because of the lock on Orders. Transaction B cannot complete its transaction because of the lock on Accounts. All activity comes to a halt and remains at a standstill forever unless the DBMS detects the deadlock and aborts one of the transactions.

🔑 Definition — Deadlock: A state in a multi-user system where two or more transactions are each waiting for the other to release a lock, preventing any of them from proceeding.

F. Deadlock Prevention

Databases use methods to avoid deadlocks to increase transaction throughput and reduce system overhead. To avoid deadlocks, each lock request is inspected to identify the risk of a potential deadlock. If this inspection reveals the deadlock potential, the lock is not granted. Well, very few modern DBMS’s can actually prevent or avoid deadlocks because there’s a lot of overhead required in order to do so. This is because the DBMS’s that do try to prevent deadlocks have to try to predict what a database user will do next. Thus, every lock request must be accepted after assessing the deadlock potential of the lock request. 💡 Why this matters: Deadlock prevention is proactive but complex and resource-intensive, making it less common than detection in practice.

G. Deadlock Detection

Instead of deadlock prevention, the more popular approach to dealing with database deadlocks is deadlock detection. There are two common approaches to deadlock detection:

1. Set the lock wait (time for lock to be released) time period to a certain preset limit (like 5 seconds). So, if a session waits more than 5 seconds for a lock to free up, then that session will be terminated.
2. Inspect all the locks currently in place to see if there are any two sessions that have locked each other out and are in a state of deadlock.

In either of the above-mentioned approaches, one of the requests will have to be terminated to stop the deadlock, and any transaction changes which came before the request will have to be rolled back so that the other request can make progress and finish. 💡 Why this matters: Deadlock detection is reactive but more practical, as it allows the system to resolve conflicts after they occur, without the overhead of trying to predict future actions.

⭐ Key Takeaways

The uncontrolled execution of concurrent transactions leads to the Lost Update Problem and the Uncommitted Data problem, both violating transaction isolation. Locks are the fundamental mechanism to enforce isolation, with granularity (database, table, row, column) balancing concurrency and control. There are two lock types: Shared Locks (multiple readers allowed) and Exclusive Locks (single writer), as defined by their compatibility. A key challenge is the deadlock where transactions wait for each other's locks, which is more commonly resolved through deadlock detection (using timeouts or lock inspection) than through overhead-heavy prevention.

🧠 Quick Revision Questions

1. What are the two main problems caused by uncontrolled concurrent transactions, and which property of a transaction do they violate?
2. Explain the difference between a Shared Lock and an Exclusive Lock using the compatibility graph concept.
3. In a scenario with frequent updates to a large table, which lock granularity (e.g., table or row) would be more suitable for high concurrency, and why?
4. Describe the "deadlock" situation using the classic example of two transactions updating the Accounts and Orders tables.
5. What are the two common approaches for deadlock detection, and what is the final action taken to resolve a detected deadlock?