dbm exam 3 - concurrency

What is the difference between data administration and database administration?

One is a function applying to an entire organization, the other is specific to a particular database.

database administration tasks

• manage database structure

• control concurrent processing

• manage processing rights and responsibilities

• develop database security

• provide for database recovery

• manage the DBMS

• maintain the data repository

• participate in database and application development

• facilitate changes to database structure

index

secondary structure in a physical database design that can speed up access to data for certain types of queries

Does the DBA need to understand the physical database design?

Yes, it informs their understanding of DBMS performance and they may be able to influence replication levels of data.

Disk mirroring

A physical database design strategy that maintains two copies of data.

RAID

A physical database design strategy that keeps redundant arrays of independent disks.

query optimizer

evaluates ways to execute a query, picks the fastest one using indices

What does concurrency control protect against?

One user’s work inappropriately influencing another user’s work (race condition).

transaction

a logical unit of work

atomic

either all of it happens, or none of it does

What is COMMIT?

TCL keyword that makes all statements of the transaction permanent.

What is ROLLBACK?

TCL keyword that undoes the entire transaction according to the log.

implicit lock

locks placed by the DBMS

explicit lock

locks placed by the application program

What is the most common type of locking in database systems today?

implicit locking

_ granularity is easy to manage, but frequently causes conflicts.

large

_ granularity is hard to manage, but conflicts are less common.

small

exclusive lock

prohibits other users from reading the locked resource

shared lock

allows other to read, but not update

serializable transactions

2 transactions that run concurrently and generate the same results as if they had run separately

_ is used to achieve serializability.

two-phase locking

growing phase

transactions are allowed to obtain locks as necessary

shrinking phase

once the first lock is released, no other lock can be obtained

strict two-phase locking

locks are obtained throughout the transaction, no lock is released until COMMIT or ROLLBACK is issued

Why might someone use strict two-phase locking?

It’s easier to implement.

What are some ways to prevent deadlock?

• allow users to issue all lock requests at one time

• require all application programs to lock resources in the same order

What does a DBMS do when deadlock occurs?

It aborts one of the transactions and rolls back partially completed work.

optimistic locking

assumes no transaction conflict will occur

What kind of locking is a DBMS that processes a transaction and checks whether a conflict occurs afterwards practicing?

optimistic

What kind of locking is a DBMS that issues locks during a transaction practicing?

pessismistic

What does ACID stand for?

Atomic, Consistent, Isolated, Durable.

durable

database committed changes are permanent

statement-level consistency

each statement independently processes rows consistently

transaction-level consistency

all rows impacted by the transaction are protected from changes during the entire transactions

transaction isolation levels

1. read uncommitted

2. read committed

3. repeatable read

4. serializable

dirty read

The transaction reads a row that has been changed, but the change has not been committed.

If the change is rolled back, the transaction has incorrect data.

nonrepeatable read

The transaction rereads data that has been changed and finds updates or deletions due to committed transactions.

phantom read

The transaction rereads data and finds new rows inserted by a committed transaction since the prior read.

What consistency problems are possible at a read uncommitted isolation level?

dirty read, nonrepeatable read, phantom read

What consistency problems are possible at a read committed isolation level?

nonrepeatable read, phantom read

What consistency problems are possible at a repeatable read isolation level?

phantom read

What consistency problems are possible at a serializable isolation level?

none

cursor

pointer into a set of rows

What is the above code doing?

Creating an SQL cursor.

SQL GRANT and REVOKE are keywords granting permissions in what language?

SQL Data Control Language

SQL Injection Attack

occurs when a user inputs data that can modify an SQL statement

What is the above code doing?

Outputs every single row as a result of a SQL injection attack.

Why is recovery via reprocessing unfeasible?

The recovered system may never catch up if the computer is heavily scheduled.

rollback

undo erroneous changes, reprocess only valid transactions

rollforward

restore database using saved backup and apply valid transactions since the last save.

What is this an example of?

rollback recovery

What is this an example of?

rollforward recovery

What is a checkpoint?

A point of synchronization between the database and the transaction log.

The DBMS refuses new requests, finishes processing requests, and writers to disk.

A _ marks a point within a transaction for rollback and is user-initiated.

savepoint

A _ ensures data consistency in case of failure, writes to disk, tries to finish ongoing transactions, and is automatic.

checkpoint

data repository

collection of metadata about users in a database

Who maintains the data repository?

DBA

What does it mean for a data repository to be active?

It’s part of a system’s development process.

What does it mean for a data repository to be passive?

Documentation is only made when someone has time.