Advanced SQL Transactions PDF

Section1 1) Define Transaction 2) Transaction management, ACID 3) SQL commands 4) Automatic commit and rollback 1) Transaction is a set of operations performed on a database and all served as a single logical unit of work. Transactions are essential for handling user...

Section1 1) Define Transaction 2) Transaction management, ACID 3) SQL commands 4) Automatic commit and rollback 1) Transaction is a set of operations performed on a database and all served as a single logical unit of work. Transactions are essential for handling user requests to access and modify database contents, ensuring the database remains consistent and reliable despite various operations and potential interruptions. Transaction in Database Management Systems (DBMS) can be defined as a set of logically related operations. Operations of Transaction: A user can make different types of requests to access and modify the contents of a database. So, we have different types of operations relating to a transaction. They are discussed as follows: Read(X) //select Write(X) //insert, delete, update Commit Rollback for example, account X wants to transfer 500 to account Y. 1. The first operation reads X's value from database and stores it in a buffer. So buffer will contain 3500 2. The second operation will decrease the value of X by 500. 3. The third operation will increase Y’value+500 4. Confirm the transaction 2) Transaction Management, ACID Transaction management focuses on managing a sequence of operations that are treated as a single unit of work. The primary goals of transaction management include ensuring data integrity, consistency, and reliability. ACID represents a set of properties aimed at ensuring reliable processing of database transactions. These properties help maintain data integrity and consistency in a database environment. A(Atomicity): ensures that a transaction is treated as a single, indivisible unit of work. This means that either all the operations within a transaction are completed successfully, or none of them are applied to the database. If any part of the transaction fails, the entire transaction is rolled back to its previous state, leaving the database unchanged. For example: Account A transfer 200 to account B Scenario1: 1) Read(A) 2) A-200 3) B+20 4) Commit Scenario2: 1) Read(A) 2) A-200 3) System Failure 4) automatic Rollback  Consistency(C): ensures that a transaction takes the database from one valid state to another valid state, maintaining all predefined rules, constraints, and integrity conditions throughout the transaction process. After the successful execution of a transaction, the database must still satisfy all set data integrity constraints. For example: Bank system Account (A)= 1000 Account(B)=500 A transfer 200 to B Rule1: any account > zero Rule2: The total balance across all accounts remains constant Scenario 1: 1)ensure A>0 A+B=1500 2) Read(A) 3)A=1000-200=800 4)B=500+200=700 After this transaction, the database is in a state of consistency. Scenario 2: 1)ensure A>0 A+B=1500 2) Read(A) 3)A=1000-200=800 4) system failure After this transaction A=800 B=500 A+B=1300 Not consistency Isolation(I): ensures that concurrent transactions do not interfere with each other. Each transaction should execute independently of others, meaning that the intermediate state of a transaction is not visible to other transactions until it is completed (committed). This prevents issues such as dirty reads, non-repeatable reads, and phantom reads. For example: User1 draw 100$, user2 draw 50$ at the same time User1:200-100 =100 at the same time user2: read (200)-50=150 no isolation Durability(D): guarantees that once a transaction has been committed, it will remain so, even in the event of a system failure, such as a crash, power loss, or hardware malfunction. 3)SQL command: 4)Automatic commit and rollback Automatic commit - DDL and DCL commands - Exit command Automatic rollback - System crash - System failure