DD Quiz 4

Atomicity

'All or nothing' property. ○ A transaction is an indivisible unit that is either performed in its entirety or is not performed at all. It is the responsibility of the recovery subsystem of the DBMS to ensure atomicity.

Ordering of read/writes in serialiability

(a) If two transactions only read a data item, they do not conflict and order is not important. (b) If two transactions either read or write separate data items, they do not conflict and order is not important. (c) If one transaction writes a data item and another reads or writes same data item, order of execution is important.

Deadlock Detection and Recovery

- DBMS allows deadlock to occur but recognizes it and breaks it. - Usually handled by construction of wait-for graph (WFG) showing transaction dependencies: - Create a node for each transaction. - Create edge Ti -> Tj , if Ti waiting to lock item locked by Tj . - Deadlock exists if and only if WFG contains cycle. - WFG is created at regular intervals.

Concurrency Control

- Process of managing simultaneous operations on the database without having them interfere with one another. - Prevents interference when two or more users are accessing database simultaneously and at least one is updating data - Although two transactions may be correct in themselves, interleaving of operations may produce an incorrect result.

Benefits / negatives of NoSQL

-scale out. Distribute data across multiple hosts seamlessly. - Requires less management, automatic repair, simpler data models - Designed for big data - More relaxedd in structure of data - Inexpensive - Low levels of support - Still implementing basic feature set - Lack of expertiese

Recoverable Schedule

A schedule where, for each pair of transactions Ti and Tj, if Tj reads a data item previously written by Ti, then the commit operation of Ti precedes the commit operation of Tj

Cascading Rollback

A single transaction failure leads to a series of transaction rollbacks. To prevent this with 2PL, leave release of all locks until end of transaction.

Timestamp

A unique identifier created by DBMS that indicates relative starting time of a transaction. - Can be generated by using system clock at time transaction started, or by incrementing a logical counter every time a new transaction starts.

Consistency CAP

All replicas contain the same version of data Client always has the same view of the data (no matter what node)

Recovery manager

Allows DBMS to restore database to consistent state follwing a failtue

Deadlock

An impasse that may result when two (or more) transactions are each waiting for locks held by the other to be released. Only one way to break deadlock: abort one or more of the transactions. Deadlock should be transparent to user, so DBMS should restart transaction(s). However, in practice DBMS cannot restart aborted transaction since it is unaware of transaction logic even if it was aware of the transaction history (unless there is no user input in the transaction or the input is not a function of the database state).

Key value

Associate a data value with a specific key

Document oriented

Associate a structured data value with a specific key. The structure is embedded in the object.

ACID

Atomicity Consistency Isolation Durabilitiy

Different Recovery facilities

Backup Mechanism Logging facilities Checkpoint facility Recovery manager

BASE

Basically Available - the system does guarantee availability Soft state - State of system may change over time Eventually consistent - will become consistent over time, given that the system doesn't receive input during that time.

CAP theorem

Cannot satisfy Consistency availabaility and partition tolerance ast the same time

Conflict Serializable Schedule

Conflict serializable schedule orders any conflicting operations in same way as some serial execution. Under constrained write rule (transaction updates data item based on its old value, which is first read), use precedence graph to test for serializability.

Graph database

Consists of ndoes and edges. Typically nodes represent entities and the edges represent relationships

Log file

Contains information about all updates to the database including transaction records and checkpoints records Log file may be duplexed or triplexed. Log file sometimes split into two separate random-access files. Potential bottleneck; critical in determining overall performance.

Hierarchy of Granularity

Could represent granularity of locks in a hierarchical structure. Root node represents entire database, level 1s represent files, etc. When node is locked, all its descendants are also locked. DBMS should check hierarchical path before granting lock. Intention lock could be used to lock all ancestors of a locked node. Intention locks can be read or write. Applied top-down, released bottom-up.

Deadlock Prevention

DBMS looks ahead to see if transaction would cause deadlock and never allows deadlock to occur. Could order transactions using transaction timestamps: Wait-Die Wound-Wait

Main recovery techniques

Deferred update Immediate update Shadow paging

NOSQL sharding

Distributes a single logical database system across a cluster of machines Uses range based partiitioning to distribute documents based on a specific shard key Automatically balances the data associated with each shard Can be turned on and off per collection

Durability

Effects of a committed transaction are permanent and must not be lost because of later failure.

Outcomes of a transaction (Transaction Support)

Either success or failure - Success: Transaction commits and database reaches a new consistent state. - Failure - Transaction aborts, and database must be restored to consistent state before it started. This transaction will be rolled bak or undone - Committed transaction cannot be aborted - Aborted transaction that is rolled back can be estarted later

Checkpoints facilities

Enables updates to database in progress to be made permananet

When to undo

If transaction had not committed at failure time, recovery manager has to undo (rollback) any effects of that transaction for atomicity. Partial undo - only one transaction has to be undone. Global undo - all transactions have to be undone.

Locking - Basic Rules

If transaction has shared lock on item, can read but not update item. If transaction has exclusive lock on item, can both read and update item. Reads cannot conflict, so more than one transaction can hold shared locks simultaneously on same item. Exclusive lock gives transaction exclusive access to that item. Some systems allow transaction to upgrade read lock to an exclusive lock, or downgrade exclusive lock to a shared lock. To guarantee serializability, need an additional protocol concerning the positioning of lock and unlock operations in every transaction.

Logging facilities

Keep track of current state of transactions and database changes

NoSQL Supported Model Types

Key-value Document oriented Graph database Columnar database

How to avoid Lost update problem

Loss of T2's update avoided by preventing T1 from reading balx until after update.

Need for Concurrency Control

Lost update problem. Uncommitted dependency problem. Inconsistent analysis problem.

Shadow paging

Maintain two page tables during life of a transaction: current page and shadow page table. When transaction starts, two pages are the same. Shadow page table is never changed thereafter and is used to restore database in event of failure. During transaction, current page table records all updates to database. When transaction completes, current page table becomes shadow page table.

Backup mechanism

Makes periodic backup copies of database

Partition tolereance

Multiple entry points System reamins operational on system split Sytem works well across physical network partitions

Consistency

Must transform database from one consistent state to another.

If the database has been damaged

Need to restore last backup copy of database and reapply updates of committed transactions using log file.

If database is only inconsistent:

Need to undo changes that caused inconsistency. May also need to redo some transactions to ensure updates reach secondary storage. Do not need backup, but can restore database using before- and after-images in the log file

Serializability

Objective of a concurrency control protocol is to schedule transactions in such a way as to avoid any interference. Could run transactions serially, but this limits degree of concurrency or parallelism in system. Serializability identifies those executions of transactions guaranteed to ensure consistency. • No guarantee that results of all serial executions of a given set of transactions will be identical.

Uncommitted Dependency Problem

Occurs when one transaction can see intermediate results of another transaction before it has committed. Referred to as a dirty read T4 updates balx to £200 but it aborts, so balx should be back at original value of £100. T3 has read new value of balx (£200) and uses value as basis of £10 reduction, giving a new balance of £190, instead of £90.

Inconsistent Analysis Problem

Occurs when transaction reads several values but second transaction updates some of them during execution of first. T6 is totaling balances of account x (£100), account y (£50), and account z (£25). Meantime, T5 has transferred £10 from balx to balz, so T6 now has wrong result (£10 too high).

Isolation

Partial effects of incomplete transactions should not be visible to other transactions. ○ Transactions execute independently of one another.

Checkpoint

Point of synchronization between database and log file. All buffers are force-written to secondary storage. Checkpoint record is created containing identifiers of all active transactions When failure occurs, redo all transactions that committed since the checkpoint and undo all transactions active at time of crash

How to avoid dirty read

Problem avoided by preventing T3 from reading balx until after T4 commits or aborts.

How to avoid inconsistent analysis problem

Problem avoided by preventing T6 from reading balx and balz until after T5 completed updates.

Database Recovery

Process of restoring database to a correct state in the event of a failure.

Nonserial schedule

Schedule where operations from set of concurrent transactions are interleaved. Objective of serializability is to find nonserial schedules that allow transactions to execute concurrently without interfering with one another. In other words, want to find nonserial schedules that are equivalent to some serial schedule. Such a schedule is called serializable

Serial Schedule

Schedule where operations of each transaction are executed consecutively without any interleaved operations from other transactions.

Schedule

Sequence of reads/writes by set of concurrent transactions

Recoverability

Serializability identifies schedules that maintain database consistency, assuming no transaction fails. Could also examine recoverability of transactions within schedule. If transaction fails, atomicity requires effects of transaction to be undone. Durability states that once transaction commits, its changes cannot be undone (without running another, compensating, transaction)

Granularity of Data Items

Size of data items chosen as unit of protection by concurrency control protocol. Ranging from coarse to fine: - The entire database. - A file. - A page (or area or database spaced). - A record. - A field value of a record.

Columnar database

Stores data by columns as opposed to rows. Columns are grouped into families. Typically a family corresponds to a real world object.

Lost Update Problem

Successfully completed update is overridden by another user. T1 withdrawing £10 from an account with balx, initially £100. T2 depositing £100 into same account. Serially,final balance would be £190.

Types of Failures

System crashes, resulting in loss of main memory. Media failures, resulting in loss of parts of secondary storage. Application software errors. Natural physical disasters. Carelessness or unintentional destruction of data or facilities. Sabotage.

Three techniques for handling deadlock

Timeouts. Deadlock prevention. Deadlock detection and recovery

Two-Phase Locking (2PL)

Transaction follows 2PL protocol if all locking operations precede first unlock operation in the transaction. Two phases for transaction: Growing phase - acquires all locks but cannot release any locks. Shrinking phase - releases locks but cannot acquire any new locks. If every transaction in a schedule follows 2PL, schedule is serializable. However, problems can occur with interpretation of when locks can be released.

Transaction records contain:

Transaction identifier. Type of log record, (transaction start, insert, update, delete, abort, commit). Identifier of data item affected by database action (insert, delete, and update operations). Before-image of data item. After-image of data item. Log management information.

Timeouts

Transaction that requests lock will only wait for a system-defined period of time. If lock has not been granted within this period, lock request times out. In this case, DBMS assumes transaction may be deadlocked, even though it may not be, and it aborts and automatically restarts the transaction.

Locking

Transaction uses locks to deny access to other transactions and so prevent incorrect updates. • Most widely used approach to ensure serializability. • Generally, a transaction must claim a shared (read) or exclusive (write) lock on a data item before read or write. • Lock prevents another transaction from modifying item or even reading it, in the case of a write lock.

Timestamping

Transactions ordered globally so that older transactions, transactions with smaller timestamps, get priority in the event of conflict. Conflict is resolved by rolling back and restarting transaction. No locks so no deadlock. Read/write proceeds only if last update on that data item was carried out by an older transaction. Otherwise, transaction requesting read/write is restarted and given a new timestamp.

Transactions and Recovery

Transactions represent basic unit of recovery. Recovery manager responsible for atomicity and durability. If failure occurs between commit and database buffers being flushed to secondary storage then, to ensure durability, recovery manager has to redo (rollforward) transaction's updates.

Concurrency Control Techniques

Two basic concurrency control techniques: Locking, Timestamping. Both are conservative approaches: delay transactions in case they conflict with other transactions. Optimistic methods assume conflict is rare and only check for conflicts at commit

Need for Recovery Control

Two types of storage: volatile (main memory) and nonvolatile. Volatile storage does not survive system crashes. Stable storage represents information that has been replicated in several nonvolatile storage media with independent failure modes.

Deferred Update

Updates are not written to the database until after a transaction has reached its commit point If transaction fails before commit, it will not have modified database and so no undoing of changes required May be necessary to redo updates of committed transactions as their effect may not have reached database

Recovery from DeadlockDetection issues

choice of deadlock victim; - abort the transactions that incur the minimum costs how far to roll a transaction back; avoiding starvation - Starvation occurs when the same transaction is always cho- sen as the victim, and the transaction can never complete.

Tradeoff of granularity of data items

coarser,the lower the degree of concurrency; finer, more locking information that is needed to be stored. Best item size depends on the types of transactions

Precedence Graph

node for each transaction; a directed edge Ti → Tj , if Tj reads the value of an item written by TI a directed edge Ti → Tj , if Tj writes a value into an item after it has been read by Ti a directed edge Ti → Tj , if Tj writes a value into an item after it has been written by Ti If precedence graph contains cycle schedule is not conflict serializable.

Wound-Wait

only a younger transaction can wait for an older one. If older transaction requests lock held by younger one, younger one is aborted (wounded).

Wait-Die

only an older transaction can wait for younger one, otherwise transaction is aborted (dies) and restarted with same timestamp.

Basic Timestamp algorithm write

ts(T) < write_timestamp(x) x already updated by younger (later) transaction. Transaction must be aborted and restarted with a new timestamp. ts(T) < read_timestamp(x) - x already read by younger transaction. - Roll back transaction and restart it using a later timestamp. - Otherwise proceed and set write_stamp = ts(T)

Basic Timestamp algorithm Read

ts(T) < write_timestamp(x) x already written by younger transaction. Abort and Restart the transaction. Otherwise, operation is accepted and executed. Update the read_timestamp = max((ts(T), read_timestamp(x))

Immediate Update

updates are applied to the database as they occur without waiting to reach its commit point Need to redo updates of committed transactions following a failure. May need to undo effects of transactions that had not committed at time of failure. Essential that log records are written before write to database. Write-ahead log protocol. If no "transaction commit" record in log, then that transaction was active at failure and must be undone. Undo operations are performed in reverse order in which they were written to log

Availabilitity

ystem remains operational of failing nodes All clients can always read and write

Transaction

• An action, or series of actions, carried out by a single user or application program, that reads or updates the contents of the database. - Logical unit of work on database - Application program is series of transactions with non-database processing inbetween - Transforms database from one consistent state to another, although consistency may be violated during transaction