Redirecting to

  Prev   Next
Transaction: Set of instructions perform logical unit of work. Instructions of transactions either run completely or not run at all. These set of instructions are one atomic unit of work. The main operations of a transaction are Read and Write. Read(A): read operation reads the value of A from the database and stored it in a buffer in main memory. Write(A): write operation writes the value back from buffer to the database. We can do some operations on transactions: Commit: it is used to save the work done permanently in database. Rollback: it is used to undo the work done. Transaction theory: concurrency-control-introduction Transaction management video: 1. Transaction1 2. Transaction2 There are four important properties of transactions/ ACID properties: 1. Atomicity 2. Consistency. 3. Isolation. 4. Durability. 1. Atomicity: Atomicity requires each transaction is all or nothing i.e. instructions of transaction either run completely or not run at all (rollback). 2. Consistency: It ensures that database remains in consistent state before and after the transaction i.e. it ensures bringing the database from one consistent state to another consistent state. Some integrity constraints are maintained so that database is consistent before and after the transaction. 3. Isolation: Execution of one transaction is isolated from that of another transactions i.e. transactions executed isolate. Although multiple transactions may execute concurrently, each transaction must be unaware of other concurrently executing transactions. Intermediate result of transaction must be hidden from other concurrently executed transactions. 4. Durability: Once transaction completes successfully, the change made by the transaction should be permanent even if there are system failure. ACID properties: Theory: acid-properties transaction-property Video: ACID properties Transaction states: Video Transaction states transaction_state 1. Active: It is the first state of every transaction in which transaction is being executed. 2. Partially committed: when transaction executes its final operation but not committed i.e. value is still not saved in the database. 3. Committed: if transaction executes all its operation completely then it is said to be committed and all the effects are now permanently stored on the database. 4. Failed: A transaction considers failed when any one of the checks fails or if transaction is aborted while it is in active state. 5. Aborted: after the transaction has been rolled back and database restored to its state prior to the start of the transaction. After aborting the transaction, the database recovery module will select one the two operations: a. Restart the transaction. b. Kill the transaction. Concurrency control: Concurrency control is the process for managing simultaneous operations without conflicting with each another. Concurrency control is more important for proper functioning of system where two or more transactions require to access to the same data. Need of concurrency control: Since several transactions require to access the same data simultaneously then it may lead to problem of data integrity and inconsistency. Advantage of concurrency control : Advantage of concurrency control Problems of concurrency control: 1. Dirty read. 2. Lost updates. 3. Unrepeatable read. 4. Phantom read. 1. Dirty read: This problem occurs when second transaction selects a data which is updated by another transaction before committing. Video on dirty read: dirty read Theory: dirty-read 2. Lost updates: This problem occurs when multiple transactions select the same row and update the row based on the value selected. Video on lost update: lost update 3. Unrepeatable read: This problem is also known as Inconsistent Retrievals Problem. This problem occurs when a transaction accesses data before and after another transaction finish working with such data. Video on unrepeatable read: unrepeatable read 4. Phantom read: Data getting changed in current transaction by other transaction. Video on Phantom read: Phantom read Schedule: Schedule describes the execution of the transactions running in the system i.e. order of execution of operations from various transactions. Types of schedule: 1. Serial schedule. 2. Non serial schedule. 1. Serial schedule: Transaction can begin only after the completion of previous transaction i.e. one after another. 2. Non serial schedule: operations of concurrent transactions are interleaved. Video on schedules: schedules serial_nonserial_schedule schedules_hierarchy Types of schedulers: concurrency-control-types-of-schedules Conflict serializability: If two or more transactions in a non-serial schedule execute concurrently then it may lead to some conflicting operations. Conditions for conflicting operations: 1. The operations belong to different transactions. 2. At least one of the operations is write operation. 3. If operations operate on same (shared) data. If all the above conditions hold then operations are conflicting. Video on conflict serializability: conflict serializability Practice question on conflict serializability: 1. conflict serializability1 2. conflict serializability2 View serializable schedule: If non serial schedule is view equivalent to some serial schedule. serializable_chart Video on view serializability: view serializability Practice problem on view serializability: Practice problem on view serializability Theory: how-to-test-two-schedule-are-view-equal-or-not Recoverable schedules: Recoverable schedules are those schedules which can be handle in between failure. This ensures that transaction still has a chance to recover if the uncommitted transaction fails later. Video on recoverable schedules: recoverable schedules Cascading schedules: The phenomenon in which single transaction failure which leads to a series of transaction rollbacks. Cascadeless schedules: These schedules avoid cascading rollbacks. Video on Cascadeless schedules and cascading schedules: Cascadeless schedules and cascading schedules Strict schedules: A schedule is said to be strict if every value written by a transaction T is not read or changed by other transaction until first transaction T either commits or aborts. Video on strict schedules: strict schedules Concurrency control technique: Video: Concurrency control technique Time stamp ordering protocol: This protocol uses a timestamp to serialize the execution of concurrent transactions. This protocol ensures that every conflicting read and write operations are executed in timestamp order. Timestamp: it is a unique identifier used by DBMS to identify a transaction. Timestamp may be system time or logical count. Timestamp is usually assigned to transactions in the order in which they are entered in the system. Theory: timestamp-ordering-protocols timestamp-deadlock-prevention-schemes Video: Introduction: Time stamp ordering protocol Protocol in detail: Time stamp ordering protocol in detail Properties of time stamping protocol: Properties of time stamping protocol Advantage of timestamp protocol: Eliminates the possibility of deadlock. Disadvantage of timestamp protocol: Starvation may be possible. Thomas's write rule: Theory: concurrency-control-protocol-thomas-write-rule Video: Thomas's write rule Lock based protocol: Video: Lock based protocol Properties of lock-based protocol: Properties of lock-based protocol 2 phase locking protocol (2PL): 2 phase locking protocol Conservative state 2PL: Conservative state 2PL
Id Name