Reviewer -ADBASE.docx

**Transaction** - is a collection of operations that form a single logical unit of work. **Database Request** - is the equivalent of a single SQL statement in an application program or transaction. **Database consistent state** - satisfies the constraints specified in the schema. **Transaction Lo...

**Transaction** - is a collection of operations that form a single logical unit of work. **Database Request** - is the equivalent of a single SQL statement in an application program or transaction. **Database consistent state** - satisfies the constraints specified in the schema. **Transaction Log** - A DBMS uses this to keep track of all the transactions that update the database. **Atomicity** - requires that all operations (SQL requests) of a transaction should be completed. **Consistency** - ensures that only valid data following all rules and constraints will be written in the database. **Isolation** - The data used during the execution of a current transaction cannot be used by another transaction until the first one is completed. **Durability** - ensures that once transaction changes are done and committed, they cannot be undone or lost. **Atomicity requirement** - If the transaction fails after step 3, and before step 6, the system should ensure that its updates are not reflected in the database, an inconsistency will result. **Consistency requirement** - The sum of Aldous\' account and Brendon\'s account is unchanged by the execution of the transaction. If between steps 3 and 6, another transaction is allowed to access the partially updated database, it will see an inconsistent database. **Isolation requirement** - Transactions should be run sequentially. Therefore, no other transaction will happen on both accounts until the current transaction is completed. **Durability requirement** - Once the user has been notified that the transaction has completed (i.e., the transfer of the P1000 has taken place), the updates to the database by the transaction must persist despite failures. **BEGIN TRANSACTION** - This marks the beginning of transaction execution. **COMMIT** - this signals a successful end of the transaction so that any changes (updates) executed by the transaction can be safely committed to the database and will not be undone. **ROLLBACK TRANSACTION** - This signals that the transaction has ended unsuccessfully so that any changes or effects that the transaction may have applied to the database must be undone. **SAVE TRANSACTION** - is a point in a transaction when you can roll the transaction back to a certain point without rolling back the entire transaction. **@\@TRANCOUNT** - returns the number of BEGIN TRANSACTION statements that have occurred on the current connection. **Active State** - in this state, a transaction stays in this state to perform READ and WRITE operations. **Partially committed state** - where the final statement in queries has been executed **Committed state** - after all the operation has been completed **Failed state** - if one of the operations cannot be done or proceed **Terminated** - corresponds to the transaction leaving the system and if it is either committed or aborted **Concurrency Control** - When several transactions execute simultaneously in the database, there will be a chance that the consistency and integrity of data may no longer be preserved. **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). **Uncommitted Data** - occurs when two transactions, T1 and T2, are executed concurrently, and the first transaction (T1) is rolled back after the second transaction (T2) has already accessed the uncommitted data. **Inconsistent retrievals** - occur when a transaction accesses data before and after one or more other transactions finish working with the same data. **Lock** - guarantees exclusive use of data item to a current transaction. **Database-Level Lock** - Locks the entire database **Table-Level Lock** - The entire table is locked. **Row-Level Lock** - Less restrictive **Binary Lock** - Has only two states: locked (1) and unlocked (0). An **exclusive lock** exists when access is reserved specifically for the transaction that locked the object. A **shared lock** exists when a transaction wants to read data from the database, and no exclusive lock has held that data item. **Deadlock** - occurs when two (2) transactions wait indefinitely for each other to unlock data. **Deadlock Prevention** - A transaction requesting a new lock is aborted when there is the possibility that a deadlock can occur. **Deadlock Detection** - The DBMS periodically tests the database for deadlocks. **Deadlock Avoidance** - The transaction must obtain all the locks it needs before it can be executed. Transaction Isolation Level **Dirty Read** - a transaction can read data that is not committed yet. **Non-repeatable Read** - a transaction reads a given row at time T1, and then it reads the same row at time T2, yielding different results. The original row may have been updated or deleted. **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. **READ UNCOMITTED** \- is the least restrictive isolation level \- it ignores locks placed by other transactions. \- Can read modified data values that have not yet been committed by other transactions; these are called \"dirty\" reads. **READ COMMITED** \- Default isolation level for SQL Server \- Prevents dirty reads by specifying that statements cannot read data values that have been modified but not yet committed by other transactions \- Other transactions can still modify, insert, or delete data between executions of individual statements within the current transaction, resulting in non-repeatable reads, or \"phantom\" data. **REPEATABLE READ** \- A more restrictive isolation level than READ COMMITTED \- Encompasses READ COMMITTED \- Ensures that no other transactions can modify or delete data that has been read by the current transaction until the current transaction commits \- Does not prevent other transactions from inserting new rows into the tables which have been using in the existing transaction, resulting in "phantom reads" **SERIALIZABLE ISOLATION** \- Ensures that the data that one transaction has read, will be prevented from being updated or deleted by any other transaction. \- Most restrictive level and gives solution to the phantom read. problem.