COP5775 Midterm Ch 17,19-20 Terms

Any non-serial interleaving of T1 and T2 for concurrent execution leads to

A schedule that is not conflict serializable

Durability

Durability guarantees that once a transaction has been committed, it will remain committed even in the case of a system failure (e.g., power outage or crash). This usually means that completed transactions (or their effects) are recorded in non-volatile memory.

Conflicting operations: Two operations are said to be conflicting if all conditions satisfy:

They belong to different transactions They operate on the same data item At Least one of them is a write operation

False

To optimize a query whose selection clause mentions a view, you'll get the best result if you optimize the view definition and the query separately, and then paste the two of them together.

Atomicity

Transactions are often composed of multiple statements. Atomicity guarantees that each transaction is treated as a single "unit", which either succeeds completely, or fails completely: if any of the statements constituting a transaction fails to complete, the entire transaction fails and the database is left unchanged. An atomic system must guarantee atomicity in each and every situation, including power failures, errors and crashes.[4] A guarantee of atomicity prevents updates to the database occurring only partially, which can cause greater problems than rejecting the whole series outright. As a consequence, the transaction cannot be observed to be in progress by another database client. At one moment in time, it has not yet happened, and at the next it has already occurred in whole (or nothing happened if the transaction was cancelled in progress). An example of an atomic transaction is a monetary transfer from bank account A to account B. It consists of two operations, withdrawing the money from account A and saving it to account B. Performing these operations in an atomic transaction ensures that the database remains in a consistent state, that is, money is neither debited nor credited if either of those two operations fail.[5]

Isolation

Transactions are often executed concurrently (e.g., multiple transactions reading and writing to a table at the same time). Isolation ensures that concurrent execution of transactions leaves the database in the same state that would have been obtained if the transactions were executed sequentially. Isolation is the main goal of concurrency control; depending on the method used, the effects of an incomplete transaction might not even be visible to other transactions.

Which of the following concurrency protocol ensures both conflict serializability and freedom from deadlock?(a)z - phase Locking(b)Time stamp - ordering

(b) only (a) z - phase Locking is a concurrency control method that guarantees serializability. The protocol utilizes locks, applied by a transaction to data, which may block (interpreted as signals to stop) other transactions from accessing the same data.during the transaction's life. 2PL may be lead to deadlocks that result from the mutual blocking of two or more transactions.(b)Time stamp - ordering concurrency protocol ensures both conflict serializability and freedom from deadlock.Only (b) is correct.So, option (C) is correct option.

Query Optimization

-Conducted by a query optimizer in a DBMS Goal: select best available strategy for executing query Based on information available Most RDBMSs use a tree as the internal representation of a query

B-Trees

-Provide multi-level access structure -Tree is always balanced -Space wasted by deletion never becomes excessive -Each node is at least half-full -Each node in a B-tree of order p can have at most p-1 search values

Precedence graph

1. For each transaction Ti participating in schedule S, create a node labeled Ti in the precedence graph. 2. For each case in S where Tj executes a read_item(X) after Ti executes a write_item(X), create an edge (Ti → Tj) in the precedence graph. 3. For each case in S where Tj executes a write_item(X) after Ti executes a read_item(X), create an edge (Ti → Tj) in the precedence graph. 4. For each case in S where Tj executes a write_item(X) after Ti executes a write_item(X), create an edge (Ti → Tj) in the precedence graph. 5. The schedule S is serializable if and only if the precedence graph has no cycles.

Conflict Serializable:

A schedule is called conflict serializable if it can be transformed into a serial schedule by swapping non-conflicting operations.

A transaction completes its execution is said to be

Committed

The process of managing simultaneous operations on the database without having them interfere with one another is

Concurrency control.

Consistency

Consistency ensures that a transaction can only bring the database from one valid state to another, maintaining database invariants: any data written to the database must be valid according to all defined rules, including constraints, cascades, triggers, and any combination thereof. This prevents database corruption by an illegal transaction, but does not guarantee that a transaction is correct. Referential integrity guarantees the primary key - foreign key relationship.

deadlock handling strategies

Deadlock prevention Timeout Deadlock detection and recovery

True

Query optimization is particularly important for queries with very long selection clauses (e.g., involving a lot of view definitions).

In order to undo the work of transaction after last commit which one should be used?

Rollback

A schedule where the operations of each transaction are executed consecutively without any other interference from other transactions is called

Serial schedule.

Query optimization

Step 1: scanner and parser generate initial query representation Step 2: representation is optimized according to heuristic rules Step 3: query execution plan is developed Execute groups of operations based on access paths available and files involved

Query Optimization

The query optimizer attempts to determine the most efficient way to execute a given query by considering the possible query plans. The goal of query optimization is to reduce the system resources required to fulfill a query, and ultimately provide the user with the correct result set faster.

Query optimization

The query processing and optimization module of the DBMS is responsible for choosing an efficient query execution plan for each query based on the existing storage structures. The choice of which indexes to create and maintain is part of physical database design and tuning, which is one of the responsibilities of the DBA staff.

ACID properties of database transactions

atomicity, consistency, isolation, durability

nACID properties

nAtomicity nTransaction performed in its entirety or not at all nConsistency preservation nTakes database from one consistent state to another nIsolation nNot interfered with by other transactions nDurability or permanency nChanges must persist in the database

B+Trees

nData pointers stored only at the leaf nodes nLeaf nodes have an entry for every value of the search field, and a data pointer to the record if search field is a key field nFor a nonkey search field, the pointer points to a block containing pointers to the data file records nInternal nodes Some search field values from the leaf nodes repeated to guide search

Transaction

nDescribes local unit of database processing

nDeadlock prevention protocols

nEvery transaction locks all items it needs in advance nOrdering all items in the database nTransaction that needs several items will lock them in that order nBoth approaches impractical nProtocols based on a timestamp nWait-die Wound-wait nNo waiting algorithm nIf transaction unable to obtain a lock, immediately aborted and restarted later nCautious waiting algorithm nDeadlock-free nDeadlock detection nSystem checks to see if a state of deadlock exists Wait-for graph nVictim selection nDeciding which transaction to abort in case of deadlock nTimeouts nIf system waits longer than a predefined time, it aborts the transaction nStarvation nOccurs if a transaction cannot proceed for an indefinite period of time while other transactions continue normally Solution: first-come-first-served queue

nDeadlock

nOccurs when each transaction T in a set is waiting for some item locked by some other transaction T' nBoth transactions stuck in a waiting queue

Precedence graph

wi rj- i-> j ri wj - i->j wi wj - i->j