Database Systems with SQL - Full Course - Database Systems - Transaction Schedules and Isolation Levels

A transaction schedule is the order of operations, such as read or write, for multiple transactions. In transaction schedules, after finishing the first transaction, the execution of second transaction starts. In a transaction schedule, a conflict happens one operation reads and another writes the same data. So the order of the read and write affects the outcome. When there are two reads, the outcome is not affected. Equivalent schedule - contain the same transactions with every conflicting operations in the same order, and always produce the same result. Conflicting schedule - contain the same transactions with some conflicting operations in different order, and may produce different results. Serial schedule – transactions executed one at a time. They are isolated transactions and there is no concurrency. Serializable schedule – Any schedule that can be made into a serial schedule Isolation levels Relational databases let DBAs specify isolations levels for each transaction. The SQL standard defines four isolation levels : Read Uncommitted – Read Uncommitted is the lowest isolation level. In this level, one transaction may read not yet committed changes made by other transaction, thereby allowing dirty reads. In this level, transactions are not isolated from each other. Read Committed – This isolation level guarantees that any data read is committed at the moment it is read. Thus it does not allows dirty read. The transaction holds a read or write lock on the current row, and thus prevent other transactions from reading, updating or deleting it. Repeatable Read – This is the most restrictive isolation level. The transaction holds read locks on all rows it references and writes locks on all rows it inserts, updates, or deletes. Since other transaction cannot read, update or delete these rows, consequently it avoids non-repeatable read. Serializable – This is the Highest isolation level. A serializable execution is guaranteed to be serializable. Serializable execution is defined to be an execution of operations in which concurrently executing transactions appears to be serially executing. Schedules and recovery There are 3 schedule types that affect the recovery system: 1. nonrecoverable schedule – no transactions can be rolled back. May violate the ACID properties since rollback is required for atomic and durable transactions. 2. cascading schedule - rollback of one transaction results in rollback of other transactions, which may degrade the database performance. Many databases don’t allow this type. 3. strict schedule - rollback of one transaction does not cause the rollback of other transactions. This is supported by most databases, and helps makes the recovery system easier and improves the efficiency of the database. Subscribe to Appficial for more programming videos coming soon. Also, don't forget to click LIKE and comment on the video if it helped you out!