03_Handout_1.pdf

IT2003 Transaction and Concurrency Control Isolation: The data used during the execution of a current transaction cannot be used by another transaction unt...

IT2003 Transaction and Concurrency Control Isolation: The data used during the execution of a current transaction cannot be used by another transaction until the first one A. Transaction is completed. Example: If two (2) people use the same ATM card and make a A transaction is a collection of operations that form a single logical transaction at the same time, the first one to be connected will have unit of work. its first transaction. Therefore, other accesses from that account Example: As a customer, you will make a payment from the cashier would be locked until the existing session is over. to purchase two (2) pairs of Nike shoes. From a customer's Durability – ensures that once transaction changes are done and standpoint, this can be considered as a single operation. In the committed, they cannot be undone or lost. database, however, it consists of several operations, such as writing a new customer’s invoice, reducing the quantity of on-hand Real-life transaction: Fund transfer products in inventory, and updating the sales. If one operation is not A transaction to transfer ₱1000 from the bank account of Aldous to the successful, all other operations will be undone. account of Brendon: A database request is the equivalent of a single SQL statement in 1. read Aldous's account an application program or transaction. 2. Aldous's account = Aldous’s account – ₱1000 o If a transaction has three (3) update statements and one (1) 3. Write changes in Aldous's account insert statement, the transaction uses four (4) database 4. Read Brendon's account requests. 5. Brendon’s account = Brendon's account + ₱1000 Database consistent state – satisfies the constraints specified in the 6. Write changes in Brendon's account schema. o Assume that a specific table contains a gender column where  Atomicity requirement: If the transaction fails after step 3, and it only accepts an entry having values of “Male” and “Female”. before step 6, the system should ensure that its updates are not If a user attempts to enter a value of ‘Person’, then the database reflected in the database, an inconsistency will result. will disallow the entry of such a value.  Consistency requirement: The sum of Aldous' account and Transaction log – A DBMS uses this to keep track of all the transactions Brendon's account is unchanged by the execution of the that update the database. transaction. If between steps 3 and 6, another transaction is allowed o In the case of system failure, this log helps bring the database to access the partially updated database, it will see an inconsistent back to a consistent state. database.  Isolation requirement: Transactions should be run sequentially. Properties of transactions Therefore, no other transaction will happen on both accounts until Atomicity – requires that all operations (SQL requests) of a the current transaction is completed. transaction should be completed.  Durability requirement: Once the user has been notified that the Example: Transaction T1 has four (4) SQL requests that must be transaction has completed (i.e., the transfer of the P1000 has taken successfully completed. Otherwise, the entire transaction is place), the updates to the database by the transaction must persist aborted. despite failures. Consistency – ensures that only valid data following all rules and constraints will be written in the database. When a transaction SQL Transactional Commands results in invalid data, the database reverts to its previous state BEGIN TRANSACTION - This marks the beginning of transaction execution. 03 Handout 1 *Property of STI  [email protected] Page 1 of 8  IT2003 COMMIT - this signals a successful end of the transaction so that any changes (updates) executed by the transaction can be safely BEGIN committed to the database and will not be undone. BEGIN TRANSACTION ROLLBACK TRANSACTION - This signals that the transaction has ended unsuccessfully so that any changes or effects that the --Deduct the amount needed for fund transfer transaction may have applied to the database must be undone. UPDATE BankAccount SAVE TRANSACTION - is a point in a transaction when you can SET Balance = Balance - 1000 roll the transaction back to a certain point without rolling back the WHERE AccountNo = 10 entire transaction. @@TRANCOUNT - returns the number of BEGIN TRANSACTION statements that have occurred on the current --Add the amount to complete the fund transfer connection. UPDATE BankAccount SET Balance = Balance + 1000 Transaction Execution States WHERE AccountNo = 20  Active state - in this state, a transaction stays in this state to SAVE TRANSACTION FundTransfer perform READ and WRITE operations.  Partially committed state - where the final statement in queries has been executed --Aldous current balance is 10,000 and he tries to  Committed state - after all the operation has been completed withdraw an amount of 11,000.  Failed State - if one of the operations cannot be done or proceed UPDATE BankAccount  Terminated - corresponds to the transaction leaving the system SET Balance = Balance - 11000 and if it is either committed or aborted WHERE AccountNo = 10 DECLARE @balance INT SET @balance = (SELECT Balance FROM BankAccount WHERE AccountNo = 10) --Check the balance if sufficient to the amount being withdrawn IF @balance >= 0 BEGIN SAVE TRANSACTION FundWithdraw END Figure 1. Transaction execution states ELSE IF @balance < 0 BEGIN Assume that we have a table named BankAccount with the following columns and values: ROLLBACK TRANSACTION FundTransfer AccountNo Name Balance END 10 Aldous 11,000 COMMIT 20 Brendon 10,000 END 03 Handout 1 *Property of STI  [email protected] Page 2 of 8  IT2003 the schema. Aldous won’t be able to withdraw the amount he The following code is an example of transactional commands requested. based on the real-life transaction: fund transfer. B. Concurrency Control Concurrency Control: When several transactions execute Output: simultaneously in the database, there will be a chance that the consistency and integrity of data may no longer be preserved. The system must control the interaction among the concurrent transactions, and this control is achieved through concurrency control techniques. The main problems in concurrent transactions Lost Update – occurs when two concurrent transactions, T1 and T2, are updating the same data element, and one of the updates is lost (overwritten by the other transaction). Explanation:  Before starting the first query, we declare a command that the Uncommitted data – occurs when two transactions, T1 and T2, are executed concurrently, and the first transaction (T1) is rolled following statements will be classified as a transaction using the “BEGIN TRANSACTION” back after the second transaction (T2) has already accessed the  The first query deducts the amount from Aldous’s account that uncommitted data. will be needed for fund transfer. Inconsistent retrievals - occur when a transaction accesses data  The second query adds the amount to Brendon’s account to before and after one or more other transactions finish working with complete the fund transfer. After completing, we saved the the same data. completed transaction in order to prevent from rolling back the entire transaction in case of having an issue in the next Concurrency Control with Locking Methods transaction. A lock guarantees exclusive use of data item to a current  Since Aldous's current balance is 10,000 after the transaction, transaction. he tries to withdraw an amount of 11,000 to his account. The o Transaction T2 does not have access to a data item that is third query updates the balance of Aldous by deducting the currently being used by transaction T1. amount he requests to be withdrawn to his balance. o The lock is released (unlocked) when the transaction is  The third query will only be executed if the balance of Aldous is completed. sufficient for the amount being withdrawn. To achieve this, we The database-level lock: create an if-else statement and else if statement. o Locks the entire database  In the if-else statement, we set a condition that if the balance of o prevents the use of any tables in the database to transaction Aldous’s account is greater than or equal to 0 after the third T2 while transaction T1 is being executed query, the transaction will be saved and applied these changes o Good for batch processes, but not suitable for multiuser to the database. Afterwards, Aldous will successfully withdraw DBMS as it would slow down the data access if thousands of the amount he requested. transactions had to wait for the current transaction to be  In the else-if statement, we set a condition that if the balance is completed less than to 0 after the third query, the transaction in the third The table-level lock: query must be undone as it violates the constraints specified in o The entire table is locked. 03 Handout 1 *Property of STI  [email protected] Page 3 of 8  IT2003 o Prevents access to any row by transaction T2 while transaction T1 is using the table.  If T1 has not unlocked data item Y, T2 cannot begin. o If a transaction requires access to several tables, each table  If T2 has not unlocked data item X, T1 cannot continue. may be locked.  Consequently, T1 and T2 wait for each other to unlock the o Two (2) transactions can access the same database as long required data item. as they access different tables. o Transactions T1 and T2 cannot access the same table even The three basic techniques to control deadlocks are: when they try to use different rows. o Deadlock prevention – A transaction requesting a new lock is A row-level lock: aborted when there is the possibility that a deadlock can occur. o Less restrictive If the transaction is aborted, all changes made by this o Allows concurrent transactions to access different rows of the transaction are rolled back, and all locks obtained by the same table even when the rows are located on the same page transaction are released. o Improves the availability of data but requires high usage of o Deadlock detection – The DBMS periodically tests the space because a lock exists for each row in a table of the database for deadlocks. If a deadlock is found, the “victim” database transaction is aborted (rolled back and restarted), and another transaction continues. Lock types o Deadlock avoidance – The transaction must obtain all the locks A binary lock: it needs before it can be executed. However, the serial lock o Has only two states: locked (1) and unlocked (0). assignment required in deadlock avoidance increases action o If an object such as a database, table, or row is locked by a response time. transaction, no other transaction can use that object. o As a rule, the transaction must unlock the object after its Transaction Isolation Level termination. Transaction isolation levels are described by the type of “reads” that o These options are automatically managed by the DBMS. a transaction allows or not. The types of read operation are: o User does not require to manually lock or unlock data items.  Dirty Read - a transaction can read data that is not committed yet. A shared/exclusive lock: Example: o An exclusive lock exists when access is reserved specifically 1. The original salary of Mary was ₱15,000. The treasurer for the transaction that locked the object. changed Mary's salary to ₱20,000 but did not commit the o A shared lock exists when a transaction wants to read data from transaction. the database, and no exclusive lock has held that data item. 2. Mary read her wages and found an increase in her wages by o Using the shared/exclusive locking concept, a lock can have ₱5,000. three states: unlocked, shared (read), and exclusive (write). 3. The treasurer found out that they had a mistake in the corresponding transaction. The transaction had been rolled Deadlocks back, and Mary's salary is back to ₱15,000. The ₱20,000 A deadlock occurs when two (2) transactions wait indefinitely for salary is now considered dirty data. each other to unlock data.  Non-repeatable read - a transaction reads a given row at time T1, Example: and then it reads the same row at time T2, yielding different Concurrent transaction: results. The original row may have been updated or deleted. T1 = access data items X and Y Example: T2 = access data items Y and X 03 Handout 1 *Property of STI  [email protected] Page 4 of 8  IT2003 1. In Transaction 1, Mary read her own salary of ₱15,000, and --TRANSACTION 2 the operation was not completed. 2. In Transaction 2, the Treasurer modified Mary's salary to SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ₱20,000 and submitted the transaction. SELECT * FROM Stocks WHERE ID = 1 3. In Transaction 1, when Mary read her salary again, her salary changed to ₱20,000. Output for the Transaction 1:  Phantom Read - a transaction executes a query at time T1, and then it runs the same query at time T2, yielding additional rows that satisfy the query.  Example: 1. Transaction 1, read all employees with a salary of ₱1000 and return 10 rows. 2. At this point, Transaction 2 inserts an employee record into the employee table with a salary of 1000 Output for the Transaction 2: 3. Transaction 1 read all employees with a salary of ₱1000 again and return 11 rows. Four (4) Transaction Isolation levels: 1. A READ UNCOMMITTED o is the least restrictive isolation level o it ignores locks placed by other transactions. Explanation: o Can read modified data values that have not yet been  In Transaction 1, we set the following statements to be a committed by other transactions; these are called "dirty" reads. transaction using the BEGIN TRANSACTION command.  The first query reduced the quantity of Samsung S10 by 1. Example: Assume that we have a table named Stocks and have  The next statement used a WAITFOR DELAY function in order the values of the following: to delay the execution of the previous query (15 seconds). ID Name Quantity  We used the ROLLBACK TRANSACTION command in order 1 Samsung S10 10 to undo and end the existing transaction. Table 1. Stocks  Before writing the query in Transaction 2, we used the READ UNCOMMITTED command in order to return the rows even if -- Transaction 1 it’s not committed yet. BEGIN TRANSACTION  In order to run this concurrent transaction, use two (2) windows UPDATE Stocks set Quantity = Quantity - 1 for writing the queries/codes and execute Transaction 1 first. WHERE ID = 1 While Transaction 1 is waiting for its execution through the --BILLING THE CUSTOMER WAITFOR DELAY function, execute the second window or Transaction 2. WAITFOR DELAY '00:00:15' ROLLBACK TRANSACTION 2. A READ COMMITTED o Default isolation level for SQL Server SELECT * FROM Stocks 03 Handout 1 *Property of STI  [email protected] Page 5 of 8  IT2003 o Prevents dirty reads by specifying that statements cannot read  Since we used the READ COMMITTED command in data values that have been modified but not yet committed by transaction 2, transaction 2 will only return the rows from the other transactions requested query until the transaction 1 is committed. o Other transactions can still modify, insert, or delete data  This is because Transaction 2 was trying to access the same between executions of individual statements within the current data that is being used by transaction 1 and not been committed transaction, resulting in non-repeatable reads, or "phantom" yet. data. Example: Using the Stocks (Table 1). --Transaction 1 READ COMMITTED BEGIN TRANSACTION UPDATE Stocks SET Quantity = Quantity - 2 WHERE ID = 1 Figure 1. “Executing query” loading WAITFOR DELAY '00:00:010' COMMIT 3. A REPEATABLE READ SELECT * FROM Stocks o A more restrictive isolation level than READ COMMITTED o Encompasses READ COMMITTED o Ensures that no other transactions can modify or delete --Transaction 2 data that has been read by the current transaction until the SET TRANSACTION ISOLATION LEVEL READ COMMITTED current transaction commits BEGIN TRANSACTION o Does not prevent other transactions from inserting new rows into the tables which have been using in the existing SELECT * FROM Stocks transaction, resulting in “phantom reads” COMMIT Example: Using the Stocks (table 1). Output of the two (2) transactions: --Transaction 1 REPEATABLE READ SET TRANSACTION ISOLATION LEVEL REPEATABLE READ BEGIN TRANSACTION WAITFOR DELAY '00:00:10' SELECT * FROM Stocks COMMIT Explanation: SELECT * FROM Stocks  If you try to execute these two (2) transactions concurrently, both results will display an “executing query” loading. (See figure 1.) 03 Handout 1 *Property of STI  [email protected] Page 6 of 8  IT2003 --Transaction 2 REPEATABLE READ --Transaction 1 SERIALIZABLE BEGIN TRANSACTION SET TRANSACTION ISOLATION LEVEL SERIALIZABLE UPDATE Stocks BEGIN TRANSACTION SET Quantity = Quantity - 1 WHERE ID = 1 UPDATE Stocks WAITFOR DELAY '00:00:01' SET Quantity = Quantity + 1 WHERE ID = 1 COMMIT SELECT * FROM Stocks WAITFOR DELAY '00:00:010' Output of the Transaction 1: COMMIT --Transaction 2 SERIALIZABLE SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRANSACTION INSERT INTO Stocks VALUES (3, 'Iphone 11', 3) SELECT * FROM Stocks WAITFOR DELAY '00:00:01' COMMIT Explanation: Output for Transaction 1:  In Transaction 1, we want to query the data from Stocks with a TRANSACTION ISOLATION LEVEL REPEATABLE READ.  In Transaction 2, we want to modify the quantity of Samsung S10 in Stocks by reducing its quantity by 1.  Since Transaction 1 is the first transaction that has been executed, this will prevent other transaction (Transaction 2) to modify the data that was being used by the first transaction (Transaction 1).  Note that this isolation level does not prevent you from inserting Output for Transaction 2: a new entry even if the object was being locked. 4. A SERIALIZABLE isolation o Ensures that the data that one transaction has read, will be prevented from being updated or deleted by any other transaction. o Most restrictive level and gives solution to the phantom read. problem. Example: Using the Stocks (table 1) Explanation: 03 Handout 1 *Property of STI  [email protected] Page 7 of 8  IT2003  In Transaction 1, we modify the quantity of Samsung S10 by adding 1.  In Transaction 2, we execute a concurrent transaction that will insert a new entry to table stocks.  Upon using SERIALIZABLE isolation, Transaction 1 does not allow Transaction 2 to insert any entry or rows as it protects the transaction from all three read problems (dirty read, non- repeatable read, and phantom read). REFERENCES Coronel, C. and Morris, S. (2018). Database systems design, implementation, & management (13th ed.). Cengage Learning. Elmasri, R. & Navathe, S. (2016). Fundamentals of Database systems (7th ed.). Pearson Higher Education. Kroenke, D. & Auer, D. Database processing: Fundamentals, design, and implementation (12th ed.). Pearson Higher Education. Silberschatz A., Korth H.F., & Sudarshan, S. (2019). Database system concepts (7th ed.). McGraw-Hill Education. Microsoft. (2017). Transactions. Retrieved from https://docs.microsoft.com/en-us/sql/t-sql/language-elements/transactions- transact-sql?view=sql-server-ver15 Microsoft (2018). SET TRANSACTION ISOLATION LEVEL (Transact-SQL). https://docs.microsoft.com/en-us/sql/t-sql/statements/set-transaction- isolation-level-transact-sql?view=sql-server-ver15 03 Handout 1 *Property of STI  [email protected] Page 8 of 8