Database Management (DBAS32100) Lecture Notes PDF

Database Management (DBAS32100) sheridancollege.ca  TRANSACTION PROCESSING AND CONTROL Instructor: Sivasakthi J Email: [email protected] Course: DBAS32100 sheridancollege.ca  TR...

Database Management (DBAS32100) sheridancollege.ca  TRANSACTION PROCESSING AND CONTROL Instructor: Sivasakthi J Email: [email protected] Course: DBAS32100 sheridancollege.ca  TRANSACTIONS A transaction comprises a unit of database work Transactions take the database from one consistent state to the next consistent state. a)In Oracle databases a transaction implicitly begins with the first statement that modifies data. b)Issuing either a COMMIT or ROLLBACK statement explicitly ends a transaction. When you issue a COMMIT, you are assured that all of your changes have been successfully saved and that any data integrity checks and rules have been validated. Oracle’s transactional control architecture ensures that consistent data is provided every time sheridancollege.ca  TRANSACTION CONTROL STATEMENTS The following are the transaction control statements: COMMIT or COMMIT WORK: ends your transaction and makes any changes permanent (durable). ROLLBACK or ROLLBACK WORK: ends your transaction and undoes any uncommitted changes. SAVEPOINT: creates a marked point within a transaction. ROLLBACK TO : rolls back the transaction to the marked point without rolling back any of the work that preceded it. SET TRANSACTION: allows you to set various transaction attributes, such as the transaction’s isolation level and whether it is read-only or read-write. sheridancollege.ca  TRANSACTIONS PROPERTIES Transactions in Oracle exhibit all of the required ACID properties: Atomicity: Either all of a transaction happens or none of it happens. Consistency: A transaction takes the database from one consistent state to the next. Isolation: The effects of a transaction may not be visible to other transactions until the transaction has committed. Durability: Once the transaction is committed, it is permanent. sheridancollege.ca  SAMPLE TRANSACTION Chuck Jones trades in one car and buys another. Let's look at the sequence of steps and the database action. sheridancollege.ca TRANSACTION IS DEFINED BY ACID PROPERTIES ATOMICITY CONSISTENCY ISOLATION DURABILITY sheridancollege.ca  ATOMICITY A business transaction can be a series of changes. For example a series of SQL DML statements A transaction is Atomic if it succeeds as a whole (all the components succeed) or it fails as a whole when one or more of the components fail. Atomic transaction can not partially fail or partially succeed There is a certain class of statements in Oracle that are atomic—but only at the statement level. DDL statements are implemented in a manner such that: 1. They begin by committing any outstanding work, ending any transaction you might already have in place. 2. They perform the DDL operation, such as a CREATE TABLE, ALTER TABLE etc.… 3. They commit the DDL operation if it was successful, or roll back the DDL operation otherwise. Hoffer A. et al 2015 sheridancollege.ca  CONSISTENCY A transaction must bring database to a valid state. In other words a transaction should not introduce wrong data to the database. Relational database management systems have tools that help to minimize the chance of having data inconsistencies. Such as the tabular structure, data types, constrains, and other tools. Relational databases in a third normal form (no redundancies) help to prevent update anomalies. Hoffer A. et al 2015 sheridancollege.ca  RECAP OF 3NF First Normal Form (1NF): Ensures that each column contains atomic (indivisible) values and that each row in a table is uniquely identifiable by a primary key. Example: If a table has a column for "Address" that contains both street name and city, 1NF would require separating these into distinct columns. Second Normal Form (2NF): Extends 1NF by requiring that non-key attributes (attributes not part of the primary key) be fully functionally dependent on the whole primary key. Example: If a table has a composite primary key (e.g., (CustomerID, ProductID)) and an attribute like "Order Date" depends only on CustomerID but not on ProductID, it violates 2NF. Third Normal Form (3NF): Extends 2NF by requiring that non-key attributes are not transitively dependent on the primary key. In simpler terms, every non-key column must depend only on the primary key, not on another non-key column. Example: If a table has columns for "EmployeeID", "DepartmentID", and "DepartmentName", where "DepartmentName" depends on "DepartmentID" but not directly on "EmployeeID", it violates 3NF because "DepartmentName" is transitively dependent on "EmployeeID" through "DepartmentID". sheridancollege.ca  ISOLATION Every transaction has to be dealt with in isolation even if the transactions are concurrent For example there should be a mechanism that allows to resolve the conflicts arising from multiple users updating same data at the same time Hoffer A. et al 2015 sheridancollege.ca  DURABILITY Is the contract between the database and the user that states that a transaction is stored in the database when it is completed no matter what happens (even if the system will fail). Hoffer A. et al 2015 sheridancollege.ca  LOCKS Whenever an INSERT, UPDATE or DELETE statement is issued, the data is not immediately sent to the table but, rather, is temporarily stored in the user's buffer. When the user issues a SELECT statement to view the data, data from the table is combined with the data from the buffer before presenting it to the user and the user thinks that the data has been inserted. Other users, however, do not see the data. Locks prevent users from destroying each other's data when they are simultaneously making changes and so are very important. There are two statements which release the locks: COMMIT and ROLLBACK sheridancollege.ca  LOCKS Locks are acquired when any process accesses a piece of data where there is a chance that another concurrent process will need this piece of data as well at the same time. By locking the piece of data we ensure that we are able to action on that data the way we expect. Blocks occur when two processes need access to same piece of data concurrently so one process locks the data and the other one needs to wait for the other one to complete and release the lock. As soon as the first process is complete, the blocked process resumes operation. The blocking chain is like a queue: once the blocking process is complete, the next processes can continue. Deadlock occurs when one process is blocked and waiting for a second process to complete its work and release locks, while the second process at the same time is blocked and waiting for the first process to release the lock. Locks, Blocks, and Deadlocks (oracle.com) sheridancollege.ca  TRANSACTIONS ATOMICITY A transaction is an atomic block of DMLs that must be saved. You can not save part of the transaction to the database. It will either be saved as a whole or none of the modifications will be saved A transaction is defined as all the DML statements that are placed between two COMMITs or between the First statement in the session and the following COMMIT The select statement in line 26 should return the below result sheridancollege.ca  TRANSACTIONS ATOMICITY ROLLBACK cancels a transaction. It is like Undo button, it undo changes until the previous COMMIT or the beginning of the session whichever comes first Run the select statement from the previous slide and the result should look like this There is no guarantee that the database will retrieve the data in the same order that you inserted it. that is why you need to use ORDER BY in your select statement if you need an order sheridancollege.ca  This part was excluded from TRANSACTIONS ATOMICITY the transaction SAVE POINT is used as a marker and we can rollback to the save point if you are still in the same transaction. Below is the result of the select statement. You can se that the update was not committed while the insert was committed sheridancollege.ca  ACTIVITY 1 ON TRANSACTION ATOMICITY A What is the result of the implementing these transactions. Try to figure it out without running the transactions then run the transactions to check your answer sheridancollege.ca  SOLUTION CREATE TABLE Test_Table (t NUMBER(1)); INSERT INTO Test_Table VALUES (1); SAVEPOINT s2; INSERT INTO Test_Table VALUES (2); COMMIT; INSERT INTO Test_Table VALUES (3); ROLLBACK; SELECT * FROM Test_Table; sheridancollege.ca CREATE TABLE Test_Table (t NUMBER(1)); INSERT INTO Test_Table VALUES (1); SAVEPOINT s2; INSERT INTO Test_Table VALUES (2); COMMIT; INSERT INTO Test_Table VALUES (3); ROLLBACK; SELECT * FROM Test_Table; -- finished in previous slide INSERT INTO Test_Table VALUES (4); SAVEPOINT s2; INSERT INTO Test_Table VALUES (5); SELECT * FROM Test_Table; ROLLBACK TO s2; sheridancollege.ca CREATE TABLE Test_Table (t NUMBER(1)); INSERT INTO Test_Table VALUES (1); SAVEPOINT s2; INSERT INTO Test_Table VALUES (2); COMMIT; INSERT INTO Test_Table VALUES (3); ROLLBACK; SELECT * FROM Test_Table; INSERT INTO Test_Table VALUES (4); SAVEPOINT s2; INSERT INTO Test_Table VALUES (5); SELECT * FROM Test_Table; ROLLBACK TO s2; SELECT * FROM Test_Table; ROLLBACK; SELECT * FROM Test_Table; sheridancollege.ca  ACTIVITY 2 ON TRANSACTION ATOMICITY A For this exercise you will need to create Bank_Account and Transaction_History tables and insert two rows in Bank Account table. The first row is your checking account and the second is your savings account Now you need to make a transaction that transfer $500 from your checking to saving account and add this transaction to transaction history table sheridancollege.ca  INSERT INTO Bank_Account VALUES(1, 'Checking', 1200.20); SOLUTION INSERT INTO Bank_Account VALUES(2, 'Savings', 24000.00); COMMIT; CREATE TABLE Bank_Account (Account_Number NUMBER(6) PRIMARY KEY, UPDATE Bank_Account Account_Type VARCHAR2(12), SET Balance = Balance - 500 WHERE Account_Number = 1; Balance NUMBER(12,4)); UPDATE Bank_Account SET Balance = Balance + 500 CREATE TABLE Transaction_History WHERE Account_Number = 2; (Trans_ID NUMBER PRIMARY KEY, INSERT INTO Transaction_History Trans_Time TIMESTAMP, VALUES (1, From_Accout_Num NUMBER(6), CURRENT_TIMESTAMP, 1, To_Account_Num NUMBER(6), 2, Transfer_Amount NUMBER(12,4), 500.0); CONSTRAINT fk_FromAccount FOREIGN KEY (From_Accout_Num) COMMIT; REFERENCES Bank_Account(Account_Number), CONSTRAINT fk_ToAccount FOREIGN KEY (To_Account_Num) SELECT * FROM Bank_Account; REFERENCES Bank_Account(Account_Number) SELECT * FROM Transaction_History; ); DROP TABLE Bank_Account; DROP TABLE Transaction_History; sheridancollege.ca  TRANSACTIONS RELATED PHENOMENA Dirty Reads Non Repeated Reads Phantom Reads sheridancollege.ca  DIRTY READS Definition: A dirty read occurs when a transaction reads data that another transaction has modified but not yet committed. If the other transaction is rolled back, the first transaction is left with incorrect data. Example in Database: Imagine Transaction A updates a product price from $10 to $15 but hasn't committed the change. Transaction B reads this price as $15, even though it might be rolled back to $10. If Transaction A rolls back, Transaction B ends up with incorrect data. Real-Life Analogy: Suppose two coworkers share a report. Worker A edits the report's numbers but hasn’t saved (committed) the changes. Worker B sees the edited numbers and uses them to make further decisions. Later, Worker A decides the changes were incorrect and undoes them. Now, Worker B has acted on incorrect data, causing confusion. sheridancollege.ca  NON-REPEATABLE READS Definition: A non-repeatable read happens when a transaction reads the same data twice but finds it has changed between the two reads due to another transaction’s update. Example in Database: Transaction A reads an inventory count of 100 items. Meanwhile, Transaction B updates the count to 90 due to a sale and commits. When Transaction A reads the inventory count again, it now sees 90 instead of 100. The data isn’t repeatable as it changed between reads. Real-Life Analogy: Imagine you check the stock of a product online at one point, and it's in stock. But later, when you refresh the page or come back, it's sold out. The stock quantity changed between your views. sheridancollege.ca  PHANTOM READS Definition: Phantom reads occur when a transaction reads a set of rows that satisfy a condition, but another transaction inserts or deletes rows that meet the same condition, causing the result set to change upon a re-read. Example in Database: Transaction A queries all orders with a value greater than $100 and finds 10 orders. Transaction B then inserts a new order worth $150 and commits it. If Transaction A runs the same query again, it now finds 11 orders, including the newly added one – the “phantom” order. Real-Life Analogy: Suppose you’re counting the people in a room. After counting, someone walks in or leaves. If you count again, you’ll get a different number because of the “phantom” people who entered or left. sheridancollege.ca  BANKING SCENARIO Dirty Read:  Transaction A checks the balance of a bank account.  Meanwhile, Transaction B updates the balance of the same account.  Transaction A reads the updated balance before Transaction B commits the update.  If Transaction B rolls back (due to some error or condition), the balance read by Transaction A was incorrect and is now considered a dirty read. Non-Repeatable Read:  Transaction A reads the balance of a bank account.  Transaction B updates the balance of the same account to a new value.  Transaction A reads the balance again and finds that it has changed since the first read.  This inconsistency between the two reads within Transaction A is a non-repeatable read because the data (balance) changed between the two reads. sheridancollege.ca  BANKING SCENARIO Phantom Read:  Transaction A queries the bank's database to find all accounts with a balance greater than $10,000.  Meanwhile, Transaction B inserts a new record into the accounts table with a balance greater than $10,000.  Transaction A repeats the same query to find accounts with a balance greater than $10,000 and discovers a new account that was not there during the first query.  This phenomenon is a phantom read because the result set of Transaction A's query changed due to an insertion by Transaction B. sheridancollege.ca  CONCLUSION  Dirty Read: Transaction A reads uncommitted data modified by Transaction B, leading to potentially incorrect information if Transaction B does not commit its changes.  Non-Repeatable Read: Transaction A gets different results when reading the same data twice due to changes made by Transaction B in between.  Phantom Read: Transaction A sees additional rows in the result set due to inserts made by Transaction B, causing unexpected results in subsequent queries. sheridancollege.ca  CONCLUSION Dirty Reads: it happens when a transaction (Non-Repeated say a select statement) read data that has not been committed yet. The reads are called dirty because it is not known if the data will stay in the database (get committed) or will not stay (rolled back) Non-Repeatable Reads: it happens when a transaction (let's say a select statement) reads a row twice but each time the data in the rows is different. This happens when transaction 1 issues a select statement then transaction 2 updates the values in the table and commits then transaction 1 issues the same select statement again Phantom reads: it is very similar to non repeat reads but it happens on a row level. For example transition 1 selects all the rows from a table then transaction 1 adds more rows to the table and commits then transaction 2 selects all the rows from the table again. You will find that more rows will be returned by the second select statement. These rows are called phantoms sheridancollege.ca  ISOLATION LEVELS When an application process accesses data, the isolation level determines the degree to which that data is locked or isolated from other concurrent processes. The isolation level is in effect during a unit of work. Four levels:  READ UNCOMMITTED  READ COMMITTED  REPEATABLE READ  SERIALIZABLE sheridancollege.ca READ UNCOMMITTED: Allows a transaction to read data that has been modified by other transactions but not yet committed. READ COMMITTED: A transaction can only read data that has been committed by other transactions. REPEATABLE READ: Ensures that if a transaction reads a set of rows, it will see the same set of rows throughout the entire transaction. SERIALIZABLE: Provides the highest level of isolation. Ensures complete isolation between transactions, preventing any interference. sheridancollege.ca  READ UNCOMMITTED Definition: This is the lowest isolation level, where transactions can read data that other transactions have modified but not yet committed, which can lead to dirty reads. Example in Database: If Transaction A is updating an order’s status, Transaction B can read this uncommitted (and possibly incorrect) status, causing confusion. Real-Life Analogy: It’s like reading a draft of a report that someone hasn’t finished. You’re working with potentially incorrect data because the final version isn’t ready. sheridancollege.ca  READ COMMITTED Definition: At this level, a transaction can only read data that has been committed. This prevents dirty reads, but non-repeatable reads and phantom reads are still possible. Example in Database: If Transaction A updates an inventory count and commits, Transaction B will see this updated count only after Transaction A commits. However, if Transaction A updates it again, Transaction B might see a different count upon re-reading. Real-Life Analogy: Imagine you’re allowed to read a report only after it's finalized, which prevents you from seeing any drafts. But if someone edits and saves it repeatedly, you’ll see different versions every time you check. sheridancollege.ca  REPEATABLE READ Definition: This level ensures that if a transaction reads data, it will see the same data for the duration of the transaction, preventing non-repeatable reads. However, phantom reads can still occur. Example in Database: If Transaction A reads a list of products and their prices, Transaction B cannot update these prices until Transaction A completes. However, Transaction B can still insert a new product, so if Transaction A repeats its read query, it might see an additional product. Real-Life Analogy: Imagine you’re reviewing a finalized version of a report; no one can make changes to the sections you’re viewing. But if someone adds a new section, it will appear when you recheck, even if your current view is consistent. sheridancollege.ca  SERIALIZABLE Definition: This is the highest isolation level, ensuring full isolation. Transactions are executed in a way that produces the same results as if they were run sequentially, one after the other, preventing dirty reads, non-repeatable reads, and phantom reads. Example in Database: If Transaction A is checking orders above $100, Transaction B must wait until Transaction A finishes before inserting any new orders above $100, ensuring no phantoms appear. Real-Life Analogy: Think of a library where only one person can check out a book at a time. If you want to borrow a book, you must wait until the previous person has finished and returned it, ensuring strict order. sheridancollege.ca  BANKING SCENARIO Read Uncommitted: Scenario: Imagine a bank account where Transaction A checks the balance ($100). Concurrently, Transaction B deposits $50 into the same account but has not yet completed the transaction. Behavior: Transaction A, operating under Read Uncommitted isolation, reads the updated balance of $150 before Transaction B commits its deposit. If Transaction B rolls back its deposit due to an error, Transaction A's read was based on temporarily incorrect data ($150 instead of the actual $100), potentially leading to incorrect decisions. Read Committed: Scenario: Consider the same bank account scenario. Transaction A checks the balance ($100), and then Transaction B deposits $50. However, Transaction A checks the balance again after Transaction B has committed the deposit. Behavior: Transaction A, under Read Committed isolation, sees the initial balance of $100 for both reads. This isolation level prevents dirty reads but does not prevent Transaction A from seeing different results if it reads the balance multiple times during Transaction B's execution, potentially encountering a non-repeatable read. sheridancollege.ca  BANKING SCENARIO Repeatable Read: Scenario: In the bank account scenario, Transaction A checks the balance ($100) and then checks it again. Meanwhile, Transaction B attempts to deposit $50. Behavior: Transaction A, under Repeatable Read isolation, sees the same balance of $100 consistently throughout its transaction, regardless of any changes made by Transaction B. Even if Transaction B commits its deposit, Transaction A's reads remain consistent at $100, preventing both dirty reads and non-repeatable reads. Serializable: Scenario: Continuing with the bank account example, Transaction A checks the balance ($100) and then checks it again. At the same time, Transaction B attempts to deposit $50. Behavior: Transaction A, under Serializable isolation, sees the balance of $100 consistently throughout its transaction, just like Repeatable Read. However, Serializable goes further by ensuring that Transaction A's view of the database remains as if it were the only transaction running, preventing not only dirty reads and non-repeatable reads but also phantom reads. This means Transaction A would not see any changes made by Transaction B until it completes its own transaction. sheridancollege.ca  TRANSACTIONS ISOLATION LEVELS Isolation Leve Permitted actions Least Read Uncommitted Allows Dirty Reads, Non Repeated Reads, and Phantom Reads isolated Read Committed Allows Non Repeated Reads, and Phantom Reads Repeatable Reads Allows Phantom Reads Serializable Does not allow any of the three reads Most isolated The four isolation levels were defined in SQL standard however not all the database implement them and databases have different mechanisms to implement them sheridancollege.ca  PRACTICE TRANSACTION WITH DIFFERENT ISOLATION LEVEL A Implement the fours scenarios in the Practice Transactions with Isolation Level document. sheridancollege.ca